Hyperlink

[Sonali]

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

 

[Tim Barlow]

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