XL2000: How to Prevent the Automatic Creation of Hyperlinks

ID: Q233073

The information in this article applies to:

Microsoft Excel 2000

SUMMARY

When you type an entry in your worksheet that begins with any of the following prefixes, Microsoft Excel creates a hyperlink:

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

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

<user name>@<company name>.com

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

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:

http://support.microsoft.com

However, Excel will not automatically create a hyperlink when you type the following text:

'http://support.microsoft.com

Method 2: Use an Application-Level Event Handler

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals 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 needs. If you have limited programming experience, you may want to contact a Microsoft Certified Solution Provider or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Solution Providers, please see the following page on the World Wide Web:

http://www.microsoft.com/mcsp/

For more information about the support options available from Microsoft, please see the following page on the World Wide Web:

http://www.microsoft.com/support/supportnet/overview/overview.asp

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.

Save and close any open workbooks and then create a new workbook.

Start the Visual Basic Editor (press ALT+F11).

On the Insert menu, click Class Module.

Type the following code into 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

Press CTRL+R to switch to the Project Explorer window.

Double-click the ThisWorkbook icon.

Type the following code into the module sheet:


Dim objDelHyperlink As New Class1

Private Sub Workbook_Open()
    Set objDelHyperlink.NoHyperlink = Application
End Sub

Press ALT+F11 to switch to Excel.

Save the file.

Press ALT+F11 to switch to the Visual Basic Editor.

Place the insertion point anywhere within the Workbook_Open code.

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 convert it to 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:

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

REFERENCES

For more information about creating hyperlinks, click Microsoft Excel Help on the Help menu, type "Create a hyperlink" in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

Additional query words: XL2000 disable

Keywords : kbdta
Version : WINDOWS:2000
Platform : WINDOWS
Issue type : kbhowto