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

 

[Fluff]

What are the other cells?

 

[russelldt]

Here is a screenshot of the sheet.

 

[Fluff]

That doesn't tell me where the other DV cells are.

 

[russelldt]

All of the excel sheets, bear the name that is shown in the cell. At present these are accessed by the hyperlink (blue underlined script). EG, clicking on "Labour" opens hidden sheet called "Labour".

Hope that this helps,

Thanks

 

[Fluff]

Are you talking about further data validation cells, or hyperlinks?

 

[russelldt]

Ok, i have figured out how to continue with the hyperlinks and the dropdown. Thanks for the support.

One final question, i used cell A3 in the above example as a reference. The actual cell with the dropdown is C29.

Do I make the change here :If Target.Address(0, 0) = "A3" Then

thanks

 

[Fluff]

That's right

 

[russelldt]

Ok, i have figured out how to continue with the hyperlinks and the dropdown. Thanks for the support.

One final question, i used cell A3 in the above example as a reference. The actual cell with the dropdown is C29.

Do I make the change here :If Target.Address(0, 0) = "A3" Then

thanks

Thanks - and as a Springbok supporter, I have great respect for your Rugby team, and of course your knowledge and support

 

[Fluff]

Glad to help & thanks for the feedback.

 

[russelldt]

Glad to help & thanks for the feedback.

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

Hello, If i want to have more than I dropdown, say in cells C15, and C16, C17 - would I use this script 3 times, just changing "A1" to the relevant cell.