VBA to rehide a worksheet when hyperlink to the main sheet is clicked - Excel 16

[dreamsinpixels]

I am looking for some code that will automate the re-hiding of a worksheet when a hyperlink to the main sheet is clicked. Is this even possible? I've attempted several different lines of code but am failing miserably. If there is any help out there it would be greatly appreciated!

Thank you and take good care.

 

[Micron]

Not enough info about the hyperlink to be specific. Maybe in worksheet_followhyperlink event (where the sheet to be hidden is the displayed text of the link)

Sheets(Target.Name).Visible = True (or false as the case may be)
I think that would require the hyperlink subaddress to be (or be on) the sheet you want to show/hide.
There are other properties of the hyperlink that you might need instead, but as stated, info is sketchy.

 

[portews]

I would do it all through VBA, not with a hyperlink. When you click in the cell where the hyperlink would be, it runs the code to hide the sheet and activate the index page.

Put this in the code page for each page where the link is:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Range("A1"), Target) Is Nothing Then ' change to the cell where the link would be
    ThisWorksheet.Visible = xlSheetHidden 'or xlSheetVeryHidden, depending on how bad you want to hide it.
    Sheets("Sheet1").Activate ' change to your index sheet name.
End If
End Sub

If you already have the hyperlinks installed, you'll need to remove them.

 

[dreamsinpixels]

Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Not Intersect(Range("A1"), Target) Is Nothing Then ' change to the cell where the link would be ThisWorksheet.Visible = xlSheetHidden 'or xlSheetVeryHidden, depending on how bad you want to hide it. Sheets("Sheet1").Activate ' change to your index sheet name. End If End Sub

This is great and it worked once...

What about just hiding the sheet when the primary sheet is selected? I think this might be a more straightforward way to go about it.

 

[portews]

What about just hiding the sheet when the primary sheet is selected?

Makes sense. Put this in the code for your primary page. This will hide _all_ the sheets other than the primary page.

Private Sub Worksheet_Activate()
Dim shts As Worksheet
For Each shts In ActiveWorkbook.Worksheets
    If Not ActiveSheet.Name = shts.Name Then
        shts.Visible = xlSheetVisible
    End If
Next
End Sub

 

[dreamsinpixels]

Makes sense. Put this in the code for your primary page. This will hide _all_ the sheets other than the primary page.

Private Sub Worksheet_Activate()
Dim shts As Worksheet
For Each shts In ActiveWorkbook.Worksheets
    If Not ActiveSheet.Name = shts.Name Then
        shts.Visible = xlSheetVisible
    End If
Next
End Sub

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: hide a ws with either hyperlink or when main sheet is activated
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.