Can anybody tell me how to achieve the above. For instance, on sheet1 if
cell A1 contents are 'case 4576' i want to be able to click on cell A1/sheet
1 which will take me directly to a sheet named 'case 4576' - or to any sheet
name whereby the sheet name equals the content of cell A1.

Thks

In , Banzai
spake thusly:

Can anybody tell me how to achieve the above. For instance, on
sheet1 if cell A1 contents are 'case 4576' i want to be able to
click on cell A1/sheet 1 which will take me directly to a sheet
named 'case 4576' - or to any sheet name whereby the sheet name
equals the content of cell A1.

Yes. I just did this this week. I posted about it in
microsoft.public.excel.misc, but here is that part of the
code. Not sure if this is the "canonical" way to do this,
and no one chimed in to tell me yes or no, but it does work.

Dim rgIndirect As Variant

' deleted code . . .

datRows = Application.CountA(Columns("D"))

' deleted code . . .


For myRow = 2 To datRows + 1

' value in cell is also a sheet name
rgIndirect = Cells(myRow, "A").Value & "!E2"

With Cells(myRow, "D")
.Activate
.Hyperlinks.Add Anchor:=Selection, Address:="", _
SubAddress:=rgIndirect, _
ScreenTip:=.Text 'from this cell
End With
Next 'myRow


The "rgIndirect" part and the .Hyperlinks.Add, specifically
the SubAddress property, are what you want, I think.
The ScreenTip part was just my own goal, but you might
not care about that part.

That other article of mine was here, fwiw:

Subject: VBA to add Tool Tips
Newsgroups: microsoft.public.excel.misc
Message-ID:
Date: Sun, 05 Aug 2007 13:17:00 -0000

-dman-