Variable Hyperlink in VB

[russelldt]

Hello

I hope some genius out there can help me.

I have a cell in sheet 1(call it the source cell), hyperlinked to a hidden sheet. The text in the source cell and Sheet Name are the same, so when i select the cell, the hidden sheet opens. Here is the VB routine i am using:


Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)

Dim shtName As String
shtName = Target.Name

Sheets(shtName).Visible = xlSheetVisible
Sheets(shtName).Select
End Sub


I now have a dropdown menu in the source cell and I have to select different text depending on which sheet i want to access by the hyperlink. There is always a unique sheet for the different text options. I just can't get this to work.

Looking for help. please

Thanks

 

[earthworm]

Please share small sample data along with sheet names

 

[russelldt]

Thanks for the response.

The dropdown menu looks like the image below, and the sheet names are"Tub", "Shower" and "Tub and Shower". All the sheets are hidden, and I want to be able to open the hidden sheet corresponding to the selection in the drop down menu.

 

[Fluff]

How about

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Address(0, 0) = "A3" Then
      If Evaluate("isref('" & Target.Value & "'!A1)") Then
         Sheets(Target.Value).Visible = xlSheetVisible
      Else
         MsgBox "Sheet " & Target.Value & " does not exist"
      End If
   End If
End Sub

 

[russelldt]

Thanks Fluff.

Next question, how do i set up the hyperlink in cell A2 (above picture)?

 

[Fluff]

Why do you need a hyperlink?

 

[russelldt]

I guess, because that's how it is set up at present. I was using the hyperlink and above VB script to open the hidden spreadsheet, but now i want to be able to open a different sheet, based on the options in the dropdown (all hidden sheets).

 

[russelldt]

Thanks Fluff.

Next question, how do i set up the hyperlink in cell A2 (above picture)?

So I see that the VB unhides the actual sheet, it does not open the sheet, as a Hyperlink does. That is what the hperlink did, opened the sheet at A1

 

[Fluff]

If you want to do that, how about

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Address(0, 0) = "A3" Then
      If Evaluate("isref('" & Target.Value & "'!A1)") Then
         With Sheets(Target.Value)
            .Visible = xlSheetVisible
            Application.Goto .Range("A1"), True
         End With
      Else
         MsgBox "Sheet " & Target.Value & " does not exist"
      End If
   End If
End Sub

 

[russelldt]

You are a genius, many thanks.

If / when i move the dropdown from A3 to a different cell, (i have about 20 of these dropdown cells all opening different bespoke sheets), how do i change the VB script