View Single Post
  #2   Report Post  
Old August 6th 07, 12:46 PM posted to microsoft.public.excel.programming
Dallman Ross Dallman Ross is offline
external usenet poster
First recorded activity by ExcelBanter: Jul 2006
Posts: 390
Default hyperlink to sheet name where sheet names=cell content

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")
.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
Date: Sun, 05 Aug 2007 13:17:00 -0000