Hyperlinks - Turning off (Removing)

[RWN]

xl2k

Excel insists on treating a share name (\\directoryname) in a cell as a hyperlink. I use
them extensively on templates to point my VBA routines to the directories to be used.
Each time I enter it in a cell I have to turn around and Remove the link.

I've looked at Tools->Options, thinking that there must be a switch I could throw, but to
no avail.

Not a big deal as I could skip the double backslash and add it in the macro, but I'd
rather have the whole name in the cell to prevent confusion for the users.

 

[Dave Peterson]

If you prefix the value with an apostrophe, does that stop it?

'\\myshare\myfolder\myfile.xls

You can remove the hyperlinks from a selected area by running a small macro:

option Explicit
sub removeLinks()
selection.hyperlinks.delete
end sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[RWN]

I'm not new at macro's although-at times-it seems that everything I know is wrong!

Yes, forcing the entry to text (apostrophe) does work, but *only* if I enter the '
initially.

If I enter the UNC without the ' it treats it as a link if I then edit the field and add
the ' it still stays as a link.

If I enter it with the ' - all is well (as noted).
If I then delete the ' it becomes a link and if I try re-entering the value with the ', it
stays as a link.

Tried formatting the cell as text - same thing, entry becomes a link.

In other words, once a link, always a link.

This is not a major problem, more of an annoyance-if I forget the ' and go back to the
cell-with the mouse-to remove the link it immediately tries to connect to the link.
I was hoping there would be some setting to disable the links at the user's option.

Oh well, thanks for the response.

 

[Dave Peterson]

Select the range of cells that no longer need to have hyperlinks.

Hit alt-f11 (to get to the VBE)
hit ctrl-g (to see the immediate window)
type this (or copy|paste) into that window:

selection.hyperlinks.delete
(hit enter)

Close the VBE and back to excel.

The macro might be something nice to add to a workbook (like Personal.xl*) so
that it's always available, but it's not necessary.

 

[RWN]

Thanks Dave.

Already came to that conclusion - for me it's a "MSWish" item, but I'm probably the only
one so I'll add it to my utilities workbook.

 

[Nick Hodge]

Rob

FWIW XL2003 allows you to switch it off under Tools>Auto correct>Auto format
as you type, but doesn't help you with XL2K

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

Thanks Dave.

Already came to that conclusion - for me it's a "MSWish" item, but I'm
probably the only
one so I'll add it to my utilities workbook.

 

[RWN]

Thank you Nick.
At least I know it's not me!


--
Regards;
Rob
------------------------------------------------------------------------

Rob

FWIW XL2003 allows you to switch it off under Tools>Auto correct>Auto format
as you type, but doesn't help you with XL2K

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

 

[David McRitchie]

Yes but it's all or nothing, you can't format certain columns
to contain macros or not to contain macros, so my choice
even in Excel 2003 would be to just let the little beggars be
created and then delete them from the selected columns where they're
not wanted using a macro such as earlier in thread.