Hyperlink to hidden sheet - target.name does not return value??

[imhoffp]

this code will not work in my spreadsheet... trying to hyperlink to a hidden sheet. The error is on the 3rd line. I believe the shtname does not have a value. I have the VB in the worksheet under follow hyperlink. What am I missing?

dim shtname as string
shtname=target.name
sheets(shtname).visible=xlsheetvisible
sheets(shtname).select

 

[6StringJazzer]

Please show the entire sub. How is Target defined? If it is a Range parameter to a Sub then you need this:

shtname=target.parent.name

I would skip dealing with the name altogether and do this:

dim sht as Worksheet
Set sht = Target.Parent
sht.Visible=xlsheetvisible
sht.select

 

[imhoffp]

thanks... here is the entire code I'm using based on another suggestion. When run, no error but does not jump to the other sheet

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)

Dim ShtName As String
ShtName = Left(Target.SubAddress, InStr(1, Target.SubAddress, "!") - 1)
Sheets(ShtName).Visible = xlSheetVisible
Sheets(ShtName).Select

End Sub

 

[imhoffp]

the addition of "parent" did not help

 

[GWteB]

Your sheet name probably contains spaces, try the below.

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    Dim ShtName As String
    ShtName = VBA.Replace(VBA.Left(Target.SubAddress, VBA.InStr(1, Target.SubAddress, "!") - 1), "'", "")
    Sheets(ShtName).Visible = xlSheetVisible
    Sheets(ShtName).Select
End Sub