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. 

  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. 

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



Related Article
    

  

  

  

  
If you like this blog please take a second and subscribe to my rss feed

  
Tags: , 

  
Comments: No comments, be the first to comment

  
All the fields that are marked with REQ must be filled

  

     
  



Leave a reply

    

        Name
    (Req)     

    
    

    E-mail
    (Req)    

    
    

    URI

    
    

    Message