• klseesea
• Dec 23,2008
• In: Software & Tools

Prevent automatic hyperlinks creation in Excel 2000 / 2002

Note: These techniques are applicable to Excel 2000 as well. Windows Excel 2002/ 2003 have an Options setting under Autocorrect that the user can set to prevent the automatic creation of hyperlinks.

Tools > AutoCorrect [Auto Format As you type ] > Uncheck - Internet and Network path as hyperlinks.

The parsers in Excel 2000 and Excel 2002  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:’officemvp@microsoft.com
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

1. or the ActiveSheet:

        Public Sub DeleteActiveSheetHyperlinks()
           On Error Resume Next
           ActiveSheet.Hyperlinks.Delete
           On Error GoTo 0
        End Sub

2. 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

3. 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.

Related Article

• March 25, 2009 -- Greyed out buttons in "Display Properties Desktop" settings (0)
• February 17, 2009 -- Robocopy options for robust file copy tasks (0)
• January 21, 2009 -- There is not enough memory or disk space to complete the operation" error message when you open a file in MS Word (0)
• January 20, 2009 -- Sysprep get an unexpected error has occurred while changing your computer’s network identification (0)
• January 13, 2009 -- BDD 2007 faqs on Windows XP deployment (0)
• January 7, 2009 -- NTFS versus Fat32 (0)
• January 6, 2009 -- Versions of the .NET Framework (0)
• January 5, 2009 -- How to determine which versions of the .NET Framework (0)
• December 24, 2008 -- Screen Saver Tab Missing from Display Properties (0)
• December 17, 2008 -- Windows Automatic Updates options are grayed out? (0)