Hyperlinks fail due sheet rename

Desmond

New Member
Joined
Nov 8, 2005
Messages
10
I have a spreadsheet with numerous hyperlinks to cells within the same sheet.
The links were created by Right Clicking on a cell, selecting Hyperlink..., and inserting the required information.
It has worked fine until today when I changed the name of the sheet, and now my Hyperlinks no longer work, producing the following error "Reference is not valid.
Renaming the sheet back to the original name fixes the problem, but I would really like to be able to rename it occasionally.
Is there a simple solution to this problem?
TIA, Des.
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Hello, Desmond
Welcome to the Board !!!!!
try to cure the problem with something like this
Code:
Sub update_hyperlinks()
'Erik Van Geit
'051109
Dim h As Hyperlink
Dim OldShName As String
Dim NewShName As String

OldShName = "sheet1"
NewShName = "newname"
For Each h In ActiveSheet.Cells.Hyperlinks
    If Left(h.SubAddress, Len(OldShName)) = OldShName Then _
    h.SubAddress = Application.Substitute(h.SubAddress, OldShName, NewShName)
Next h
End Sub
kind regards,
Erik
 
Upvote 0

Forum statistics

Threads
1,212,055
Messages
6,105,639
Members
447,974
Latest member
misspancake

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top