This site will look better if you upgrade to a browser that supports web standards.

McGimpsey & Associates 

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.

This page last updated Saturday, 3 January 2004

© Copyright 2001 - 2004 McGimpsey and Associates. Except where noted, all code on this site may be distributed under the Gnu GPL. Acknowledgement is appreciated.

Quick Links

Automatically kill hyperlinks using an event handler.