Software

Ask something
Discussion Locked

Automatic hyperlink insertion in Excel

0 helpful
10 replies

by trixiabonsol

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
I'm using an Office 2000 version of Excel. When typing an entry into a cell that has the form 'xxxxxxx@xxx' Excel automatically formats the cell as a hyperlink and inserts the "address" as a mailto in the hyperlink. How do I prevent auto-hyperlink insertion in excel?

10 replies

by jireland607

What you need to do is go to:

Tools - AutoCorrect Options

On the window that appears select the middle tab labelled 'Autoformat as you type'

Uncheck the option 'Internet and network paths with hyperlinks'

This should solve your problem

James

by trixiabonsol

This method will not work for EXCEL in Office 2000 - the AutoCorrect option mentioned does not exist.

by sgt_shultz

snipped from support.microsoft.com mskb article 233073. some feature, eh?
---
XL2000: How to Prevent the Automatic Creation of Hyperlinks
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

by sgt_shultz

Method 2: Use an Application-Level Event Handler

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 in the module sheet:

by sgt_shultz

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 in the module sheet:Dim objDelHyperlink As New Class1

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

by sgt_shultz

well, i see something has been horribly messed up in the pasting here. i will let you research the article if you want the code...

by trixiabonsol

This answers the question! Thank You! I'm using the apostrophe method now - works fine if slightly kludgey.

This discussion is closed to new replies.