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

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