VBA code to unhide excel sheet based off hyperlink selection?

Question

Hello,

I have a workbook with about 16 total sheets, 15 of which are hidden. On the one visible sheet (i'll call this page 1), there are a number of hyperlinks that link the user to the 15 hidden worksheets. I need some help with vba code though.

There are two steps I'd like to accomplish:

1. When the user clicks the hyperlink on page 1, the hidden sheet it is referencing is unhidden and the user will be brought to that page (call this page 2).
2. I'd like to have maybe a button or something on page 2, that when clicked, will bring you back to page 1. When this button is clicked, I also need page 2 to revert back to being a hidden worksheet.

Sub linktest()

' linktest Macro

    Range("S33").Select

    Sheets("Page1").Select

    Sheets("Page2").Visible = True

End Sub

The above code isn't working because it is referencing the cell name (s33) and not the cell value (Say, "Page 2 Link" or something like that). These links are not static and will move around based on slicer selection, so the macro needs to take this into account.

Answer 1

This is awesome, thank you so much Andreas.

Answer 2

Replace the code with this one.

Andreas.

Sub Button1_Click()

  Dim SaveSelection As Object

  Set SaveSelection = Selection

  Application.ScreenUpdating = False

  Range("A1:AA60").Select

  ActiveWindow.Zoom = True

  Application.DisplayFullScreen = True

  Application.DisplayFormulaBar = False

  ActiveWindow.DisplayWorkbookTabs = True

  ActiveWindow.DisplayHeadings = False

  ActiveWindow.DisplayGridlines = False

  On Error GoTo ExitPoint

  SaveSelection.Select

ExitPoint:

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

  If Target.Count > 1 Then Exit Sub

  If Intersect(Target, Columns("S")) Is Nothing Then Exit Sub

  On Error GoTo ExitPoint

  With Sheets(Target.Value)

    .Visible = True

    .Select

    .Range("C5").Select

  End With

ExitPoint:

End Sub

Answer 3

This is great, but not really what I need in this instance. It's my bad, I should have been more clear.

The hyperlinks are actually labels on a chart, so in this instance, the combo box may not be the best option.

I did get it worked out another way, but I am running into another issue now...

I have another command button on this sheet that resizes the window when clicked. The VBA code for that button is: 

Sub Button1_Click()

ActiveSheet.Range("a1:AA60").Select

ActiveWindow.Zoom = True

Application.DisplayFullScreen = True

Application.DisplayFormulaBar = False

ActiveWindow.DisplayWorkbookTabs = True

ActiveWindow.DisplayHeadings = False

ActiveWindow.DisplayGridlines = False

ActiveSheet.Range("a1:AA60").Select

ActiveWindow.Zoom = True

Application.DisplayFullScreen = True

Application.DisplayFormulaBar = False

ActiveWindow.DisplayWorkbookTabs = True

ActiveWindow.DisplayHeadings = False

ActiveWindow.DisplayGridlines = False

End Sub

And my code for the hyperlinks is as follows:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim HL As Hyperlink

If Not Intersect(Target, Columns("S")) Is Nothing Then  'Adjust column letter to suit

    If Not Sheets(Target.Value).Visible Then

        With Sheets(Target.Value)

            .Visible = True

            .Select

            .Range("C5").Select

        End With

    End If

End If

End Sub

The problem is that when I click the resize button now,  I get an error message stating:

Run-Time Error' 13':

Type Mismatch

And when I debug the code, this line is highlighted off the hyperlink code:

    If Not Sheets(Target.Value).Visible Then

Would you know how to resolve this issue or have any suggestions to improve the code so that I don't receive this error?

Answer 4

The way I would do it would be to insert an ActiveX combobox onto Sheet1 to which I would give the name cmbSheets, and then have the following code combobox

Private Sub cmbSheets_Change()

Dim strSheet As String

strSheet = cmbSheets.Value

With Sheets(strSheet)

    .Visible = True

    .Activate

End With

End Sub

Private Sub cmbSheets_GotFocus()

Dim strsheets As String

Dim i As Long

strsheets = ""

For i = 2 To Sheets.Count

    strsheets = strsheets & " " & Sheets(i).Name

Next i

strsheets = Mid(strsheets, 2)

cmbSheets.List = Split(strsheets)

End Sub

When that combobox gets the focus, it will be populated with the names of the other sheets and when a sheet is selected from the list, it will be displayed and activated.

One each of those sheets, I would insert an ActiveX command button with a caption of "Back" or something similar and the following code

Private Sub CommandButton1_Click()

Dim i As Long

For i = 2 To Sheets.Count

    Sheets(i).Visible = False

Next i

Sheets(1).Activate

End Sub