Resetting Hyperlink to Worksheet

[David]

I have a program that renames the names in Column A and its associated
worksheet.
All the names in Column A are hyperlinked to its associated worksheet. The
names in Column A and the worksheet name are the same.
When I make the name changes, the hyperlink is broken.
I just need to set the hyperlink in the cell that was changed in Column A to
it's associated worksheet again.

Here is the code I use to make the changes. Thanks!

RowNum = Application.InputBox(prompt:="What is the Row Number of the
Location You Want to Change?", Type:=2)
FranNum = Application.InputBox(prompt:="What is the Franchise Number You
Want to Use?", Type:=2)
LabelName = Application.InputBox(prompt:="What is the Franchise Name You
Want To Use?", Type:=2)

If RowNum = "0" Then
'do nothing
Else

Cells((RowNum), 1).Select
OldLabelName = Cells((RowNum), 1).Value

Cells((RowNum), 2).Select

ActiveCell.FormulaR1C1 = (FranNum)
Cells((RowNum), 1).Select
ActiveCell.FormulaR1C1 = (LabelName)
Cells((RowNum), 1).Select
Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
Sheets(OldLabelName).Select
Sheets(OldLabelName).Name = (LabelName)

NEED CODE HERE TO RESET THE HYPERLINK TO THE SHEET ONLY - NOT TO A CELL ON
THE SHEET.

David

 

[Gary''s Student]

Perhaps using a separate column for the active "clickable" links. If you are
updating a link in column A and the result does not "click", then in another
column enter:

=HYPERLINK(A1)

This should always give a "clickable" result.

 

[David]

The worksheet is fixed and used by multiple people in the office.
The way the program works, people need to change the number in Column B, the
Location Name in Column A, and have the Location Name in Column A be a
hyperlink to a worksheet that will be named the same as the Location Name in
Column A.
Once the worksheet name is changed, the hyperlink is broken. I need to reset
the hyperlink to the new worksheet name.
Thanks!

David

 

[Gary''s Student]

Hyperlinks to a different sheet in the same workbook can be implemented by
either:
Insert > Hyperlink
or the HYPERLINK () function.


The advantage to using the function is that if you have many hyperlinks of
the form:
=HYPERLINK("#Sheet3!A1","table of contents")
where they all refer to Sheet3 then the better form is:
=HYPERLINK("#" & A1 & "!Z100","table of contents")
Where you put Sheet3 in cell A1. All these hyperlinks will refer to cell A1
for the sheetname.


You only have to change one cell to "un-break" all the links.