Home

MacOffice

Combatting automatic creation of hyperlinks in XLv.X and XL2001

Note: These techniques are applicable to WinXL97/00 as well. WinXL02/03 have an Options setting under Autocorrect that the user can set to prevent the automatic creation of hyperlinks.

The parsers in XLv.X and XL2001 automatically create hyperlinks when a text string that can be interpreted as an email address or URL are entered. There are at least five ways of dealing with this if you don't want these hyperlinks in your sheet.

  1. Prefix the entry with an apostrophe to indicate that the entry should be considered text:

    'jemcgimpsey@mvps.org

  2. Allow the parser to create the hyperlink, then Undo it, using the Edit/Undo menu, or the CTRL-z or CMD-z keyboard shortcuts. This is easily done if you have only a few entries.
  3. Manually trigger a macro to delete hyperlinks from the Selection (attach to a keyboard shortcut, a toolbar button, or run from the Tools/Macro/Macros dialog):
    
         Public Sub DeleteSelectionHyperlinks()
            On Error Resume Next
            Selection.Hyperlinks.Delete
            On Error GoTo 0
         End Sub
         
    or the ActiveSheet:
    
         Public Sub DeleteActiveSheetHyperlinks()
            On Error Resume Next
            ActiveSheet.Hyperlinks.Delete
            On Error GoTo 0
         End Sub
         
  4. Use a Workbook or Worksheet event handler to trap the creation of a hyperlink. This will work on an individual workbook or worksheet. Put this in the workbook code module:
            
         Private Sub Workbook_SheetChange(ByVal sh As Object, _
                ByVal Target As Excel.Range)
            On Error Resume Next
            Target.Hyperlinks.Delete
            On Error GoTo 0
         End Sub
            
    or put this code in the worksheet code module if you only want it to apply to one sheet:
            
         Private Sub Worksheet_Change(ByVal Target As Excel.Range)
            On Error Resume Next
            Target.Hyperlinks.Delete
            On Error GoTo 0
         End Sub
  5. Use a class-module event handler that traps the creation of a hyperlink for all workbooks. The Microsoft Knowledge Base has a code sample that will accomplish this...sort of. It's based on the Workbook_SheetSelectionChange event, and uses the Application.MoveAfterReturnDirection property to determine in which cell the entry was made, then removes the hyperlink from that cell. This is a problem for several reasons:
    • It checks the wrong cell if the user uses the mouse or Tab key instead of Return or Enter to complete the cell entry.
    • It assumes that both the Enter key and the Return key are mapped to the Application.MoveAfterReturnDirection (see how to Set the Enter key to move independently of the Return key).
    • If Application.MoveAfterReturnDirection is set to xlDown and you select a cell below a cell with a hyperlink, that hyperlink is removed (likewise if .MoveAfterReturnDirection is set to xlUp, xlToLeft or xlToRight and you select the corresponding adjacent cell).
    Instead, I recommend using the Worksheet_Change() event which returns the cell actually changed. See Using an Event Handler to Disable Automatic Hyperlinks.

Valid XHTML 1.1 Valid CSS Made on a Macintosh

Quick Links

Automatically kill hyperlinks using an event handler.