Excel 2000 hyperlinks

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Wyza

Thread Starter
Joined
Jan 12, 2003
Messages
22
When I enter an email address into a cell, Excel automatically changes the font and turns it into a hyperlink. How can I stop this from happening and just have the email address displayed as the default text and withOUT a hyperlink?

Thanks for any help you can offer.
 
Joined
Apr 25, 2004
Messages
544
On my Excel, I am in version 2003, I can just right click in the cell and choose remove hyperlink...do you not have this option?
 
Joined
Apr 25, 2004
Messages
544
AH, ha, just did some research...

Removing or avoiding automatic hyperlinks
You may have discovered that Excel 2000 supports automatic cell hyperlinks. Whenever you type something that resembles a URL or an e-mail address into a cell, this feature automatically converts the text into a clickable hyperlink. But what if you don't want to create a hyperlink?

If you use Excel 2000, you're out of luck. There is no way to turn this potentially annoying feature off. But you can, however, override it. If Excel creates a hyperlink from your cell entry, click the Undo button (or press Ctrl-Z) to restore the cell's contents to normal text. Or, you can precede the cell entry with an apostrophe.

Note: If you're using Excel 2002, you can turn automatic hyperlinks on or off in the AutoCorrect dialog box

Surprisingly, Excel doesn't provide a direct way to remove all hyperlinks on a worksheet. In fact, the only way to accomplish this is one cell at a time: Activate the cell that contains the hyperlink, right-click, and then select Hyperlink, Remove Hyperlink. Unfortunately, this command is available only when you have selected a single cell. To quickly deactivate all hyperlinks, you need to use a macro.

To create the macro, press Alt-F11 to activate the Visual Basic Editor, select Insert, Module to insert a new VBA module into your project, and then enter the following code:

Sub ZapHyperlinks()
Cells.Hyperlinks.Delete
End Sub
When you execute the ZapHyperlinks macro, all hyperlinks on the active sheet are deleted and replaced with normal text.
 

cwwozniak

Chuck
Moderator
Joined
Nov 28, 2005
Messages
69,735
There is a way to remove multiple hypelinks without macros that I have used several times when I needed two version of a the same workbook with and without hyperlinks. Found this hint on a couple of help sites:

Looking for a quick, easy way to remove hyperlinks without a macro? Believe it or not, you can accomplish this by using the Paste Special features of Excel. Follow these steps:

1. In a blank cell, enter the number 1.
2. Select the cell and press Ctrl+C. The cell contents (1) are now copied to the Clipboard.
3. Hold down the Ctrl key as you click each hyperlink you want to remove.
4. Choose Paste Special from the Edit menu. Excel displays the Paste Special dialog box.
5. Select the Multiply radio button.
6. Click OK. All the hyperlinks are removed, but the text of the hyperlinks remain.
7. Delete the cell you created in step 1.
 
Joined
Jul 25, 2004
Messages
5,458
When typing, preceed your text with a single apostrophe and it will not be automatically made a hyperlink.
 
Joined
Jan 4, 2006
Messages
2
and if you have a long list of email addresses already populated in excel, you can use an adjacent column to refer to the cell eg. =A1 in cell B1 will effectively take away the hyperlinks then you can copy & paste as values in a new column
 
Joined
Jul 25, 2004
Messages
5,458
monguze said:
and if you have a long list of email addresses already populated in excel, you can use an adjacent column to refer to the cell eg. =A1 in cell B1 will effectively take away the hyperlinks then you can copy & paste as values in a new column
Situations like that you're better off to select all the data, right click and select Remove Hyperlinks.
 

cwwozniak

Chuck
Moderator
Joined
Nov 28, 2005
Messages
69,735
firefytr said:
Situations like that you're better off to select all the data, right click and select Remove Hyperlinks.
"Remove Hyperlinks" does not show up as a choice when right clicking on any kimd of group of cells, at least in Excel 2003 running on Windows XP. "Remove Hyperlink" only shows up as a choice if a single cell with hyperlink is selected. What version of Excel are you using?
 
Joined
Jul 25, 2004
Messages
5,458
Doh! I completely forgot about that. I customized my right click menu to show for multiple entries. You are correct. (You can see why I customized it. ;) )

This is what I use in Excel 2000...

Code:
Option Explicit

Const RHL As String = "Remove Hyperlinks"

Sub AddRemoveHyperlink_CellMenu()
    Dim Menu As CommandBar
    Dim MenuItem As CommandBarButton
    Dim Pos As Long
    On Error Resume Next
    Set Menu = Application.CommandBars("Cell")
    Delete_RemoveHyperlink
    Pos = Menu.Controls.Count + 1
    Set MenuItem = Menu.Controls.Add(msoControlButton, before:=Pos, temporary:=False)
    MenuItem.Caption = RHL
    MenuItem.FaceId = 456
    MenuItem.HyperlinkType = msoCommandBarButtonHyperlinkNone
    MenuItem.OnAction = "Module1.RemoveHyperlinks"
    Set MenuItem = Nothing
    Set Menu = Nothing
End Sub

Sub Delete_RemoveHyperlink()
    On Error Resume Next
    Application.CommandBars("Cell").Controls(RHL).Delete
End Sub

Sub RemoveHyperlinks()
    On Error Resume Next
    Selection.Hyperlinks.Delete
End Sub
I also have some routines for making text Upper case, Lower case and Proper case, all put onto my right click menu, as I use them quite a bit.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Members online

Top