hyperlink to sheet name where sheet names=cell content
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 |
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") .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- |
All times are GMT +1. The time now is 11:47 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com