Dynamic hyperlinks to hidden worksheets

[jvalent9]

I am working on a project that would contain data from multiple sites.

On the "Welcome" worksheet (the main page) there is a dropdown menu (form control combo box) that will select a site location (which then prompts a picture of the site to appear)

I also want a link to appear that will link the user to that sites data worksheet.

I have the following code in VBA:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)       Dim strSheet As String        If InStr(Target.Parent, "!") > 0 Then      strSheet = Left(Target.Parent, InStr(1, Target.Parent, "!") - 1)   Else      strSheet = Target.Parent   End If       Sheets(strSheet).Visible = True   Sheets(strSheet).Select    End SubPrivate Sub Worksheet_Activate()       Dim ws As Excel.Worksheet       For Each ws In ThisWorkbook.Worksheets      ws.Visible = (ws.Name = Me.Name)   Next    End Sub

This code works great if i make "static" links that appear all the time by going to the insert hyperlink button in the menu then choosing the worksheet while it is unhidden, then entering the code into the vba... the link will open all my hidden worksheets.
---But i don't want visible links for all the worksheets to show up all the time, I only want the link to the selected worksheet to show up (the site chosen from the dropdown menu)----

SOOO I created a dynamic link: =HYPERLINK("[.\]"&C4, B4)

B4 contains a vlookup dependent upon the output from the dropdown menu which returns the site name and therefore is what i use as the friendly name for the hyperlink. C4 is just text added to the site name in B4 so that it refers to the name of a worksheet (i.e. for the CATLETTSBURG site B4 contains "Catlettsburg" and C4 contains "#Catlettsburg!A1" )

This dynamic link works PERFECTLY when the worksheets are not hidden... however if I hide the worksheets it fails. I am very very new to VBA.... today was my first day playing with it. I was able to get it to work for the static links following Mr. Excel tutorials and a few other forums. I have a feeling that by tweaking some part of the VBA I can get it to work for the dynamic link but not sure what to do.

 

[Worf]

Welcome to the Board

The follow hyperlink event is not firing, try this instead:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim av
' assumes B4 or equivalent containing a sheet name
If InStr(Target.Formula, "LINK") > 0 Then
    av = Replace(Split(Split(Target.Formula, "&")(1), ",")(1), ")", "") ' get B4 from formula
    Sheets(CStr(Me.Range(av))).Visible = 1
    Sheets(CStr(Me.Range(av))).Activate
End If
End Sub

 

[jvalent9]

I had an epiphany this morning and ended up solving the issue, what I did was created a macro to change the subaddress of a static link since opening hidden worksheets only seems to work with static links.

simple macro:

Sub Macro1()'
' Macro1 Macro
'


'
    Selection.Hyperlinks(1).SubAddress = Range("B4")
End Sub

 

[jvalent9]

Welcome to the Board

The follow hyperlink event is not firing, try this instead:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim av
' assumes B4 or equivalent containing a sheet name
If InStr(Target.Formula, "LINK") > 0 Then
    av = Replace(Split(Split(Target.Formula, "&")(1), ",")(1), ")", "") ' get B4 from formula
    Sheets(CStr(Me.Range(av))).Visible = 1
    Sheets(CStr(Me.Range(av))).Activate
End If
End Sub

trips up at

 If InStr(Target.Formula, "LINK") > 0 Then

however I think that is because it is a dynamic hyperlink and is the same issue I ran into with my original code. I did find a solution if interested check out my last reply up above. Used a macro.

 

[Worf]

trips up

I'm glad you solved it, but also curious. What error did you get? That code worked for me with your original setup.