How to prevent the automatic creation of hyperlinks in Excel for Mac (323238)

MORE INFORMATION

To prevent Excel for Mac from automatically creating a hyperlink, use any 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 for Mac automatically creates a hyperlink when you type the following text in a cell: However, Excel for Mac does not automatically create a hyperlink when you type the following text:

Method 2: Use a program-level event handler to prevent hyperlinks

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 program-level event handler to prevent Excel for Mac from automatically creating a hyperlink.

Notes

• The only way to use this macro to remove an existing hyperlink is to select the cell, click in the text in the Formula Bar to activate the cell, and then press RETURN. This method is best to use when you want to prevent new hyperlinks but do not want to accidentally delete existing hyperlinks.
• This procedure does not prevent the use of the HYPERLINK worksheet function to create hyperlinks.

1. Save and close any open workbooks, and then create a new workbook.
2. On the Tools menu, point to Macro, and then click Visual Basic Editor.
3. On the Insert menu, click Class Module.
4. Type the following code in the module sheet:

   <?xm-insertion_mark_start author="jay" time="20050117T215112-0800"?>
   Public WithEvents NoHyperlink As Application
   
   Private Sub NoHyperlink_SheetChange(ByVal Sh As Object, ByVal Target As Range) 
   Target.Hyperlinks.Delete 
   End Sub<?xm-insertion_mark_end?>
   <?xm-deletion_mark author="jay" time="20050117T215043-0800" data="Public WithEvents NoHyperlink As Application
   
   Private Sub NoHyperlink_SheetSelectionChange(ByVal Sh As Object, _ 
          ByVal Target As Range)
   
       On Error Resume Next
   
       &apos; If the &quot;Move selection after Enter&quot; option is enabled.
       If Application.MoveAfterReturn Then
   
           &apos; Which direction is specified?
           Select Case Application.MoveAfterReturnDirection
   
               &apos; If the &quot;Down&quot; direction is specified.
               Case xlDown
   
                   If Target.Row = 65536 Then
                       Target.Hyperlinks.Delete
                   Else
                       Target.Offset(-1, 0).Hyperlinks.Delete
                   End If
   
               &apos; If the &quot;Up&quot; direction is specified.
               Case xlUp
   
                   If Target.Row = 1 Then
                       Target.Hyperlinks.Delete
                   Else
                       Target.Offset(1, 0).Hyperlinks.Delete
                   End If
   
               &apos; If the &quot;Right&quot; direction is specified.
               Case xlToRight
   
                   If Target.Column = 256 Then
                       Target.Hyperlinks.Delete
                   Else
                       Target.Offset(0, -1).Hyperlinks.Delete
                   End If
   
               &apos; If the &quot;Left&quot; direction is specified.
               Case xlToLeft
   
                   If Target.Column = 1 Then
                       Target.Hyperlinks.Delete
                   Else
                       Target.Offset(0, 1).Hyperlinks.Delete
                   End If
   
           End Select
   
       &apos; The &quot;Move selection after Enter&quot; option is enabled.
       Else
           Target.Hyperlinks.Delete
       End If
   
   End Sub"?>
   					

5. Click the project explorer window, and then double-click the ThisWorkbook icon.
6. Type the following code in the module sheet:

   <?xm-insertion_mark_start author="jay" time="20050117T215644-0800"?>
   Dim objDelHyperlink As New Class1
   
   Private Sub Workbook_Open()
   Set objDelHyperlink.NoHyperlink = Application
   End Sub<?xm-insertion_mark_end?>
   <?xm-deletion_mark author="jay" time="20050117T215619-0800" data="Dim objDelHyperlink As New Class1
   
   Private Sub Workbook_Open()
   
       Set objDelHyperlink.NoHyperlink = Application
   
   End Sub"?>
   					

7. On the Excel menu in Microsoft Excel X for Mac and later versions of Excel for Mac, or on the File menu in Microsoft Excel 2001 for Mac, click Close and Return to Microsoft Excel..
8. Save the file.
9. On the Tools menu, point to Macro, and then click Visual Basic Editor.
10. Place the insertion point anywhere in the Workbook_Open code.
11. On the Standard toolbar, click Run Sub/UserForm to run the Workbook_Open macro. You can also press F5 to run the macro.

Method 3: Use a program-level event handler to remove existing hyperlinks and to prevent Excel for Mac from automatically creating hyperlinks

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 program-level event handler to prevent Excel for Mac from automatically creating a hyperlink.

Note With this code, you can also easily remove any undesired hyperlinks by using the arrow keys to move in any direction though cells that contain hyperlinks. This procedure does not prevent the use of the HYPERLINK worksheet function to create hyperlinks.

1. Save and close any open workbooks, and then create a new workbook.
2. On the Tools menu, point to Macro, and then click Visual Basic Editor.
3. On the Insert menu, click Class Module.
4. Type the following code in the module sheet:

   Public WithEvents NoHyperlink As Application
   
   Private Sub NoHyperlink_SheetSelectionChange(ByVal Sh As Object, _ 
   ByVal Target As Range)
   
   On Error Resume Next
   
   ' If the "Move selection after Enter" option is enabled.
   If Application.MoveAfterReturn Then
   
   ' Which direction is specified?
   Select Case Application.MoveAfterReturnDirection
   
   ' If the "Down" direction is specified.
   Case xlDown
   
   If Target.Row = 65536 Then
   Target.Hyperlinks.Delete
   Else
   Target.Offset(-1, 0).Hyperlinks.Delete
   End If
   
   ' If the "Up" direction is specified.
   Case xlUp
   
   If Target.Row = 1 Then
   Target.Hyperlinks.Delete
   Else
   Target.Offset(1, 0).Hyperlinks.Delete
   End If
   
   ' If the "Right" direction is specified.
   Case xlToRight
   
   If Target.Column = 256 Then
   Target.Hyperlinks.Delete
   Else
   Target.Offset(0, -1).Hyperlinks.Delete
   End If
   
   ' If the "Left" direction is specified.
   Case xlToLeft
   
   If Target.Column = 1 Then
   Target.Hyperlinks.Delete
   Else
   Target.Offset(0, 1).Hyperlinks.Delete
   End If
   
   End Select
   
   ' The "Move selection after Enter" option is enabled.
   Else
   Target.Hyperlinks.Delete
   End If
   
   End Sub

5. Click the project explorer window, and then double-click the ThisWorkbook icon.
6. Type the following code in the module sheet:

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

7. On the Excel menu in Microsoft Excel X for Mac and later versions of Excel for Mac, or the File menu in Microsoft Excel 2001 for Mac, click Close and Return to Microsoft Excel..
8. Save the file.
9. On the Tools menu, point to Macro, and then click Visual Basic Editor.
10. Place the insertion point anywhere in the Workbook_Open code.
11. On the Standard toolbar, click Run Sub/UserForm to run the Workbook_Open macro. You can also press F5 to run the macro.

Now, when you type any of the prefixes that are listed in the "Summary" section, Excel for Mac does not automatically convert them to hyperlinks. However, the effects of this macro apply only during your current session of Excel for Mac. If you want this procedure to run every time that you start Excel for Mac, place the file in one of your Excel for Mac startup folders. For additional information, click the following article number to view the article in the Microsoft Knowledge Base: