Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hyperlink
I am having three sheets in Excel workBook. I created two hyperlinks in
Sheet1 to open the respective sheets (Sheet 1 & 2) on clicking the hyperlink. But the Sheet2 & 3 reamin hidden once the workbook is open and only when hyperlink is clicked on Sheet 1 should the respective links should work. But the proble I am facing is that I applied the code in Sheet1 as Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink) For hyperlink on Sheet1 'Sheet3.Visible = xlSheetVisible Sheet3.Activate End Sub But whenever I click any of the two hyperlinks the same Sheet3 is visible. How to make visible different sheets when respective hyperlink is there and clicked on the same sheet |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hyperlink
Sonali,
You need to determine which sheet the hyperlink will follow - this can be determined from the target's address, though you need to use just the first part of it (e.g. get "Sheet1" from "Sheet1!A1"). Try something like: Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink) Dim aSheet As String Dim pos As Integer aSheet = Target.SubAddress ' find the position of the '!' in the address pos = InStr(1, aSheet, "!", vbTextCompare) aSheet = Left(aSheet, pos - 1) Sheets(aSheet).Visible = xlSheetVisible Sheets(aSheet).Activate End Sub HTH Tim "Sonali" wrote in message ups.com... I am having three sheets in Excel workBook. I created two hyperlinks in Sheet1 to open the respective sheets (Sheet 1 & 2) on clicking the hyperlink. But the Sheet2 & 3 reamin hidden once the workbook is open and only when hyperlink is clicked on Sheet 1 should the respective links should work. But the proble I am facing is that I applied the code in Sheet1 as Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink) For hyperlink on Sheet1 'Sheet3.Visible = xlSheetVisible Sheet3.Activate End Sub But whenever I click any of the two hyperlinks the same Sheet3 is visible. How to make visible different sheets when respective hyperlink is there and clicked on the same sheet |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can't make hyperlink function work for hyperlink to website | Excel Worksheet Functions | |||
Moving rows with Hyperlink doesn't move hyperlink address | Excel Discussion (Misc queries) | |||
Intra-workbook hyperlink: macro/function to return to hyperlink ce | Excel Discussion (Misc queries) | |||
Macro to Copy Hyperlink to another file as a HYPERLINK, not text... | Excel Programming | |||
reading html when hyperlink address not hyperlink text diplayed | Excel Programming |