How to prevent the automatic creation of hyperlinks (233073)

MORE INFORMATION

To prevent Excel from automatically creating a hyperlink, use either of the following methods.

Method 1: Type an apostrophe at the beginning of the cell entry

You can type an apostrophe (') to prevent a hyperlink from being created when you make a cell entry. For example, Excel will automatically create a hyperlink when you type the following text in a cell: However, Excel will not automatically create a hyperlink when you type the following text:

Method 2: Use an application-level event handler

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. The following steps illustrate how to use an application-level event handler to prevent Excel from automatically creating a hyperlink.

Note This procedure does not prevent the use of the HYPERLINK worksheet function to create hyperlinks.

1. Save and close all open workbooks and then create a new workbook.
2. Start the Visual Basic Editor (press ALT+F11).
3. Press CTRL+R to switch to the Project Explorer window.
4. Double-click the ThisWorkbook icon.
5. Type the following code in the module sheet:

   Dim WithEvents app As Application
   
   Private Sub app_SheetChange(ByVal Sh As Object, ByVal Target As Range)
      On Error Resume Next
      Target.Hyperlinks.Delete
   End Sub
   
   Private Sub Workbook_Open()
      Set app = Application
   End Sub
   
   					

6. Press ALT+F11 to switch to Excel.
7. Save the file.
8. Press ALT+F11 to switch to the Visual Basic Editor.
9. Place the insertion point anywhere in the Workbook_Open code.
10. Press F5 to run the Workbook_Open macro.

Now, when you enter any of the prefixes listed in the "Summary" section, Excel will not automatically create a hyperlink. However, the effects of this macro will only last during your current session of Excel. If you want this procedure to run every time that you start Excel, place the file in one of your Excel startup folders. The default startup folders for Excel include the following folders:

• C:\Windows\Application Data\Microsoft\Excel\Xlstart
• C:\Winnt\Profiles\User name\Application Data\Microsoft\Excel\Xlstart
• C:\Windows\Profiles\User name\Application Data\Microsoft\Excel\Xlstart
• C:\Program Files\Microsoft Office\Office\Xlstart
• The folder location that is specified in the Alternate startup file location box on the General tab of the Options dialog box.

Note You may not have all of these folders available.