Hyperlinks fail due sheet rename

[Desmond]

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.

 

[erik.van.geit]

Hello, Desmond
Welcome to the Board !!!!!
try to cure the problem with something like this

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