Object hyperlink produces "Reference is not valid" error after worksheet name change

[relative_virtue]

Hi all,

I have quite a number of subs in my workbook that are triggered through
Worksheet_FollowHyperlink, which is called through the use of an object
(i.e. Ctrl+K) hyperlink. For example, I have a "Sort" function that
sorts my worksheet when a certain cell is clicked on.

The problem is, these object hyperlinks need to refer to a cell on the
sheet they are on. I am currently still designing this workbook and
I've needed to change the names of the various worksheets several
times. But each time I do so, clicking on one of the links brings up
the following message:

"Reference is not valid."

before carrying on and running the code anyway. The only way I seem to
be able to right it is by selecting the hyperlink, hitting Ctrl+K again
and updating the sheet name in the Edit Hyperlink dialog box. This has
the effect of resetting the formatting on the link, which means about
15-20mins work every time my boss tells me she wants a different name
for the worksheet!

Any ideas about how I get around this problem?

Tristan Jakob-Hoff

 

[relative_virtue]

Thanks Jim,

Not a bad idea, but I'm a bit sketchy on this bit:

'Create the hyperlink

Is it possible to create object hyperlinks programatically?

Best regards,

Tristan Jakob-Hoff

 

[relative_virtue]

Thanks Jim,

Not a bad idea, but I'm a bit sketchy on this bit:

'Create the hyperlink

Is it possible to create object hyperlinks programatically?

Best regards,

Tristan Jakob-Hoff