Hyperlink links to validation list, but will not display sheets when hidden . . .

[Learner123]

Hi World,

I am having a problem setting up a Hyperlink to hidden sheets.

My workbook has numerous sheets that link into a master sheet. The master sheet contains a validation (drop down menu) that feeds into a hyperlink formula.

If I were to click on a specific value within the validation (drop down menu), the Hyperlink value changes respectively. Then, if I were to click on the Hyperlink, it brings me to the appropriate sheet. However, if the sheet is hidden, the hyperlink will not work.

Is there anyone out there who can help me with this issue?

Thank you in advance for your time!

V

 

[Smitty]

Welcome to the Board!

You can use the FollowHyperlink event:

<font face=Calibri><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_FollowHyperlink(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Hyperlink)<br>    <SPAN style="color:#00007F">With</SPAN> Sheets("Sheet2")<br>        .Visible = <SPAN style="color:#00007F">True</SPAN><br>        .Activate<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

HTH,

 

[jasonb75]

May be of use

http://www.ozgrid.com/forum/showthread.php?t=41731&page=1

code there to extract sheet name from the hyperlink.

 

[Learner123]

Thanks for the response Smitty!

I tried your command out but it ran into an error.

The command that you provided works if I hyperlink directly to the hidden sheet. However, my hyperlink is fed by a validation (drop down menu), which I believe prevents the hidden sheets from being pulled.

Example: =HYPERLINK("[Attendence.xls]'" & B1 & "'!b1"); where B1 equals the drop down menu.

Any ideas on how to correct this issue?

Thanks again.

 

[Learner123]

Thanks Jason, but that command chain didn't work either...

 

[Smitty]

That's probably even easier because you can just use a Change event. Then just unhide and activate the sheet called in the drop down.

 

[Learner123]

Excuse my ignorance, but what does a change event mean? Does it refer to a specific code structure? If so, how would that be applied to the code provided above?

 

[Smitty]

A Change event is a VBA procedure that responds to physical changes on a worksheet. Fortunately, a Data Validation selection will trigger it, so you can capitalize on it.

Do the items in the DV list correspond to your sheet names (exactly)? If so you can use this:

Private Sub Worksheet_Change(ByVal Target As Range)
    '   Code goes in the Worksheet specific module
    Dim rng As Range
        '   Set Target Range, i.e. Range("A1, B2, C3"), or Range("A1:B3")
        Set rng = Target.Parent.Range("A1")
             '   Only look at single cell changes
            If Target.Count > 1 Then Exit Sub
            '   Only look at that range
            If Intersect(Target, rng) Is Nothing Then Exit Sub
            '   Action if Condition(s) are met (do your thing here...)
            With Sheets(Target.Value)
                .Visible = True
                .Activate
            End With
End Sub

So, in this case the code is looking for a change in cell A1 (if it's different, just change it to suit). When it does, the code reads the value in that cell (the Target) and unhides/activates the sheet that was selected from the list.

The code goes in the sheet module, not a general module.

 

[Learner123]

Thank you again for your patience with me...

The items in my DV list do in fact match the worksheet names.

I have placed the code above in the worksheet module and updated the target range to reflect cell "C1", which is where the hyperlink formula resides. However, after following your instructions, the hyperlink still fails to activate the hidden sheet.

Am I missing a step? Am I supposed to place an additional code after the line that say "do your thing here"?

 

[Smitty]

The key to this is that you're not using Hyperlinks, but directly activating the sheet from the validation selection, so the range address in the change event needs to match the validation location, not the hyperlinks.