Microsoft KB Archive/323238

= How to prevent the automatic creation of hyperlinks in Excel for Mac =

Article ID: 323238

Article Last Modified on 2/8/2007

-

APPLIES TO


 * Microsoft Excel 2004 for Mac
 * Microsoft Excel X for Mac
 * Microsoft Excel 2001 for Mac

-



This article was previously published under Q323238





For a Microsoft Excel 2002 version of this article, see 291209.



For a Microsoft Excel 2000 version of this article, see 233073.



SUMMARY
When you type an entry in your worksheet that begins with any of the following prefixes, and which could be a valid hyperlink in style, Microsoft Excel for Mac automatically creates a hyperlink:


 * http://
 * www.
 * mailto:
 * ftp://
 * file://
 * news:

Excel for Mac also creates a hyperlink when you type an e-mail address in the following format:

@ .com

Excel for Mac does not provide a built-in method to override this behavior. However, this article contains sample methods that you can use to prevent Excel for Mac from automatically creating hyperlinks.



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:

http://support.microsoft.com

However, Excel for Mac does not automatically create a hyperlink when you type the following text:

'http://support.microsoft.com

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.

 Save and close any open workbooks, and then create a new workbook. On the Tools menu, point to Macro, and then click Visual Basic Editor. On the Insert menu, click Class Module.  Type the following code in the module sheet: Public WithEvents NoHyperlink As Application

Private Sub NoHyperlink_SheetChange(ByVal Sh As Object, ByVal Target As Range) Target.Hyperlinks.Delete End Sub

Private Sub NoHyperlink_SheetSelectionChange(ByVal Sh As Object, _       ByVal Target As Range)

On Error Resume Next

&apos; If the "Move selection after Enter" option is enabled. If Application.MoveAfterReturn Then

&apos; Which direction is specified? Select Case Application.MoveAfterReturnDirection

&apos; 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

&apos; 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

&apos; 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

&apos; 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

&apos; The "Move selection after Enter" option is enabled. Else Target.Hyperlinks.Delete End If

End Sub  Click the project explorer window, and then double-click the ThisWorkbook icon.</li>  Type the following code in the module sheet: Dim objDelHyperlink As New Class1

Private Sub Workbook_Open Set objDelHyperlink.NoHyperlink = Application End Sub

Private Sub Workbook_Open

Set objDelHyperlink.NoHyperlink = Application

End Sub </li> 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..</li> Save the file.</li> On the Tools menu, point to Macro, and then click Visual Basic Editor.</li> Place the insertion point anywhere in the Workbook_Open code.</li> On the Standard toolbar, click Run Sub/UserForm to run the Workbook_Open macro. You can also press F5 to run the macro.</li></ol>

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. <ol> Save and close any open workbooks, and then create a new workbook.</li> On the Tools menu, point to Macro, and then click Visual Basic Editor.</li> On the Insert menu, click Class Module.</li>  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 &quot;Move selection after Enter&quot; option is enabled. If Application.MoveAfterReturn Then

' Which direction is specified? Select Case Application.MoveAfterReturnDirection

' 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

' 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

' 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

' 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

' The &quot;Move selection after Enter&quot; option is enabled. Else Target.Hyperlinks.Delete End If

End Sub </li> Click the project explorer window, and then double-click the ThisWorkbook icon.</li>  Type the following code in the module sheet: Dim objDelHyperlink As New Class1

Private Sub Workbook_Open

Set objDelHyperlink.NoHyperlink = Application

End Sub </li> 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..</li> Save the file.</li> On the Tools menu, point to Macro, and then click Visual Basic Editor.</li> Place the insertion point anywhere in the Workbook_Open code.</li> <li>On the Standard toolbar, click Run Sub/UserForm to run the Workbook_Open macro. You can also press F5 to run the macro.</li></ol>

Now, when you type any of the prefixes that are listed in the &quot;Summary&quot; 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 more information, click the following article number to view the article in the Microsoft Knowledge Base:

288117 How to create a Personal Macro Workbook in Excel for Mac

Additional query words: MacXLX Mac XLX XL2001 disable excel link url web site XL2004

Keywords: kbhowto KB323238

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.