ExcelBanter - Hyperlinks - Turning off (Removing)

ExcelBanter (https://www.excelbanter.com/)

- Setting up and Configuration of Excel (https://www.excelbanter.com/setting-up-configuration-excel/)

- - Hyperlinks - Turning off (Removing) (https://www.excelbanter.com/setting-up-configuration-excel/14122-hyperlinks-turning-off-removing.html)

RWN

Hyperlinks - Turning off (Removing)

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.

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

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 wrote:

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.

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

--

Dave Peterson

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.
--
Regards;
Rob
------------------------------------------------------------------------
"Dave Peterson" wrote in message
...
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 wrote:

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.

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

--

Dave Peterson

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 wrote:

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.
--
Regards;
Rob
------------------------------------------------------------------------
"Dave Peterson" wrote in message
...
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 wrote:

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.

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

--

Dave Peterson

--

Dave Peterson

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.

--
Regards;
Rob
------------------------------------------------------------------------
"Dave Peterson" wrote in message
...
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 wrote:

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.
--
Regards;
Rob
------------------------------------------------------------------------
"Dave Peterson" wrote in message
...
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 wrote:

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.

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

--

Dave Peterson

--

Dave Peterson

Nick Hodge

RWN

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

--
Regards;
Rob
------------------------------------------------------------------------
"Nick Hodge" wrote in message
...
Rob

FWIW XL2003 allows you to switch it off under ToolsAuto correctAuto format
as you type, but doesn't help you with XL2K

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
HIS

"RWN" wrote in message
...
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.

--
Regards;
Rob
------------------------------------------------------------------------
"Dave Peterson" wrote in message
...
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 wrote:

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.
--
Regards;
Rob
------------------------------------------------------------------------
"Dave Peterson" wrote in message
...
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 wrote:

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.

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

--

Dave Peterson

--

Dave Peterson

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.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm

All times are GMT +1. The time now is 10:27 AM.