sheet names and hyper-links

[Nicholas Cook]

Two questions:

1. Is it possible to have Excel sheet/tab names linked to
names entered on a worksheet?

2. Hyper-links to a worksheet are broken when the
worksheet name is changed. Is there a way to hyper-link
to the fixed worksheet name in VBA instead of to the
varible name? The only solution I've found is to insert
a defined name on each worksheet and hyper-linked to the
defined name instead of the worksheet name-it's a pain.

Feel free to email solutions to
nichol...@gmaccm.co.jp.

[Bill Manville]

Nicholas Cook wrote:
> 1. Is it possible to have Excel sheet/tab names linked to
> names entered on a worksheet?
>

Not without using a macro to set them. You can't put a formula in a
sheet tab.

> 2. Hyper-links to a worksheet are broken when the
> worksheet name is changed. Is there a way to hyper-link
> to the fixed worksheet name in VBA instead of to the
> varible name?
>

You could try this, if you are happy including VBA in the workbook.

=HYPERLINK("#'" & XLName("Sheet1")&"'!$A$1",XLName("Sheet1")&"!$A$1")

with

Function XLName(stVBASheet As String) As String
Dim WS As Worksheet
Application.Volatile
For Each WS In Application.Caller.Parent.Parent.Worksheets
If WS.CodeName = stVBASheet Then
XLName = WS.Name
Exit Function
End If
Next
End Function

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup