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

McGimpsey & Associates 

Home

MacOffice

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

Using an Event Handler to disable automatic hyperlinks.

On another page, I demonstrated four ways to prevent or undo the automatic hyperlinks that XLv.X and XL2001 (along with WinXL97 and 00) insert. Those methods worked on a per entry, worksheet or workbook level. The following method can be used to prevent automatic hyperlinks from being created in ALL workbooks. The method is similar to that published in the Microsoft Knowledge Base, but overcomes its several shortcomings.

The method uses a class module and application events to delete automatic hyperlinks as soon as they are created. It does not prevent you from entering hyperlinks manually using the menu or keyboard shortcuts. This is how to implement it:

1. In order to work for all workbooks, you need to store this macro in a workbook that is always open. For most people the Personal Macro Workbook (Personal.xls for Windows users) works well, but any workbook stored in the Microsoft Office N:Office:Startup:Excel folder (XLStart folder for Windows users) will do. If you choose to use a different workbook, you can hide it using the Window/Hide menu item in XL before saving. Another alternative is to put this in an add-in.
2. If you don't want to use the Personal Macro Workbook, create a new workbook.
3. Switch to the VBE (Tools/Macro/Visual Basic Editor or Opt-F11).
4. On the View menu, click Project Explorer. Select the Project with your filename.
5. From the Insert Menu, choose Class Module.
6. Paste this code into the module sheet:

       
       Public WithEvents NoHyperlink As Application
   
       Private Sub NoHyperlink_SheetChange(ByVal Sh As Object, _
               ByVal Target As Range)
       On Error Resume Next
       Target.Hyperlinks.Delete
       On Error GoTo 0
       End Sub
       

7. In the Project Explorer Window, find your project's ThisWorkbook icon.
8. Double-click the ThisWorkbook icon.
9. Type or paste the following code in the ThisWorkbook module:

   					
       Dim objDelHyperlink As New Class1
   
       Private Sub Workbook_Open()
           Set objDelHyperlink.NoHyperlink = Application
       End Sub
   
   

10. On the File menu, click Close and Return to Excel .
11. Save the file in XL's Startup Folder (for Macs, by default it's Microsoft Office N:Office:Startup:Excel:, where N is 2001 or X).
12. Switch back to the VBE (choose Tools/Macro/Visual Basic Editor, or type Opt-F11).
13. Place the insertion point anywhere in the Workbook_Open() code.
14. On the Standard toolbar, click Run Sub/UserForm to run the Workbook_Open() macro.

Every time you make an entry from now on, XL will check to see if that entry contains a hyperlink. If it does, it will delete the hyperlink, but not the content. Placing the file in XL's Startup directory ensures that the file will be loaded each time XL is started.

For more on Application Events, see Chip Pearson's explanation.

This page last updated Monday, 26 April 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

Remove hyperlinks manually