Hyperlink List

[AMcGuckin1]

Hello,

I am trying to create a drop down list that will hyperlink and take me to different pages in the workbook, I have managed to make it work when all of the pages are visible but I am not able to make it work when I hide said sheets.

If anyone could help please that would be amazing.

 

[duggie33]

Check out this video from Mr. Excel...

Hope that helps,

Doug

 

[AMcGuckin1]

Hello Doug,

I followed this but it doesn't seem to work, I have a dropdown list in a cell eg. A1 on sheet 1 and when I click on the drop down list choose an option on there it doesn't seem to link to certain pages. I have tried a name function hyperlink with an indirect function to call the name and if the pages are there it works but not when they are hidden.

Aaron

 

[duggie33]

Hi Aaron,

As you discovered, hyperlinks to hidden sheets do not work. You have to enter the VBA code for each sheet per the video. The code will make the sheet visible, select the sheet to make it active, and then select the specified cell.

Doug

 

[mumps]

Assuming that you have the drop down list in A1 of Sheet1, copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your Sheet1 and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Select a sheet from the drop down list.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address(0, 0) <> "A1" Then Exit Sub
    Application.ScreenUpdating = False
    With Sheets(Target.Value)
        If .Visible = xlHidden Then .Visible = True
        .Activate
    End With
    Application.ScreenUpdating = True
End Sub

 

[AMcGuckin1]

Assuming that you have the drop down list in A1 of Sheet1, copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your Sheet1 and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Select a sheet from the drop down list.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address(0, 0) <> "A1" Then Exit Sub
    Application.ScreenUpdating = False
    With Sheets(Target.Value)
        If .Visible = xlHidden Then .Visible = True
        .Activate
    End With
    Application.ScreenUpdating = True
End Sub

That works thank you, If I was to then say have a list next to it would it be possible to do the same for example one week for weekly sheet on for monthly and then a yearly?

 

[Peter_SSs]

That works thank you,

The marked solution has been changed accordingly. In your future questions, please mark the post as the solution that actually answered your question, instead of your feedback message as it will help future readers. No further action is required for this thread.