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]

Are they all doing the same thing?

 

[russelldt]

Are they all doing the same thing?

Yes, each dropdown opens a unique tab, same as before.

 

[Fluff]

Ok, how about

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Not Intersect(Target, Range("A1, C15:C17")) Is Nothing 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]

Thanks. I don't understand why there is A1 in this line. I am trying to learn as I work, hence my question.
If Not Intersect(Target, Range("A1, C15:C17"))

 

[Fluff]

It's there because you said

in cells C15, and C16, C17 - would I use this script 3 times, just changing "A1"

So I thought that you wanted it to work on A1 & C15:C17

 

[russelldt]

Ah, I see the reasoning. No, A1 was just a reference. Its only C15, 16 and 17 at present. I will remove the reference to A1.

I will extend the functionality to other cells on the sheet in time

 

[Fluff]

Ok

 

[russelldt]

Ok

This works perfectly, many thanks.

I have a new issue i would like to have help with, can I post it here for you to look at, or start a new thread.

Thanks

 

[Fluff]

This works perfectly, many thanks.

You're welcome & thanks for the feedback.

I have a new issue i would like to have help with, can I post it here for you to look at, or start a new thread.

Best to start a new thread please.

 

[russelldt]

Thank you again for offering your support.


Here is the scenario. When I open a new Job File, I need to add 2 specific sheets (Work and Detail) to this new job file. These sheets are in the quote file (Quote Number). As previous, the options for the Quote and specific sheets are in dropdown menu’s, however this time I need to select from 2 dropdown menus to get the sheet (i.e Quote Number and Work, or Quote Number and Detail). My thinking is that there should be a macro button (shown on the attached screenshot) that is pressed once the Quote number and scope are selected. The same would apply for the Detail.

Both files reside in the same directory (Call it 2021, but in separate sub directories ( Quote subdirectory and Job subdirectory)