Show a worksheet when user click on it and hide it once user switched to another sheet

[saeid025]

Hey guys, first of all thanks for awesome work you done here, there is a lot of useful information here
My question is I have multiple source sheet, each of the source sheet can have 0 or more sub worksheet that are hidden; in source sheets I have hyperlinks that I want to open the corresponding sub sheet when clicked on, the problem is seems like this won't work if the sheet is hidden, my question is how I can do it?
I found this useful video, the problem with it is, it'll leave the opened sub sheet open... I want the sub sheet to close if the user switched to another source sheet or closed the workbook...
so what can I do?
thanks in advance.

 

[DanteAmor]

Hi and welcome to MrExcel!!!

I found this useful video, the problem with it is, it'll leave the opened sub sheet open

I assume you already figured out how to make the sub sheet visible.

it'll leave the opened sub sheet open... I want the sub sheet to close if the user switched to another source sheet

To hide sub sheet put the following code in the workbooks event:

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
  Select Case Sh.Name
    Case "Sheet1", "Sheet2", "Sheet3"   'source sheet names
    Case Else
      Sh.Visible = 0
  End Select
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
  'Select the first source sheet
  Sheets("Sheet1").Select
  ThisWorkbook.Save
End Sub

In this line of the macro you must put all the names of the source sheets.

Case "Sheet1", "Sheet2", "Sheet3"   'source sheet names

And on this line put the name of the first source sheet

 Sheets("Sheet1").Select

ThisWorkbook EVENT

- Open the VB Editor (press Alt + F11).​

- Over in the Project Explorer, double click on ThisWorkbook.​

- In the white panel that then appears, paste the above code.​

----- --
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
----- --

 

[Skyybot]

This is going to be a lot. But I think it will achieve your desired results. First, I'll show you my Sheet setup and then I will post the code.

First picture. All Sheets Un-hidden so that you can see the structure.



Then when the Workbook opens


When I select first hyperlink



When I select the first hyperlink on the next Sheet



When I select the previous Sheet



Here is the code.

Place in ThisWorkbook code module

Option Explicit


Sub HideTabs()
Dim i
For i = 2 To ThisWorkbook.Sheets.Count
    ThisWorkbook.Sheets(i).Visible = False
Next i
End Sub

Private Sub Workbook_Open()
HideTabs
End Sub

Place this code in all of the next sub-level sheets

Option Explicit

Private Sub Worksheet_Activate()
Dim i As Integer
For i = Me.Index + 1 To ThisWorkbook.Sheets.Count
    ThisWorkbook.Sheets(i).Visible = False
Next i
End Sub

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Dim x As Variant
Set x = Target
With ThisWorkbook.Sheets(Target.Name)
    .Visible = True
    .Activate
End With
End Sub

 

[saeid025]

thank you very much for fast answer, I have a few question
in here

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    'Select the first source sheet Sheets
    ("Sheet1").Select
    ThisWorkbook.Save
End Sub

we are selecting the first sheet and then saving, are we sure that we receive `Workbook_SheetDeactivate` event before and there is no sheet visible (except source ones)

 

[saeid025]

w

This is going to be a lot. But I think it will achieve your desired results. First, I'll show you my Sheet setup and then I will post the code.

First picture. All Sheets Un-hidden so that you can see the structure.

View attachment 118457

Then when the Workbook opens
View attachment 118457

When I select first hyperlink

View attachment 118459

When I select the first hyperlink on the next Sheet

View attachment 118460

When I select the previous Sheet

View attachment 118461

Here is the code.

Place in ThisWorkbook code module

Option Explicit


Sub HideTabs()
Dim i
For i = 2 To ThisWorkbook.Sheets.Count
    ThisWorkbook.Sheets(i).Visible = False
Next i
End Sub

Private Sub Workbook_Open()
HideTabs
End Sub

Place this code in all of the next sub-level sheets

Option Explicit

Private Sub Worksheet_Activate()
Dim i As Integer
For i = Me.Index + 1 To ThisWorkbook.Sheets.Count
    ThisWorkbook.Sheets(i).Visible = False
Next i
End Sub

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Dim x As Variant
Set x = Target
With ThisWorkbook.Sheets(Target.Name)
    .Visible = True
    .Activate
End With
End Sub

Wow! thank you for the full code, this look very interesting, just two question:
1. when we close the excel does the state reset to just having the root?
2. with this can we have multiple source sheet that we can navigate in them freely?

again thank you guys! I didn't expected to get answers this fast!

 

[DanteAmor]

are we sure that we receive `Workbook_SheetDeactivate` event before and there is no sheet visible (except source ones)

Yes

When closing, select a source sheet, you will automatically deselect a sheet and hide it.

Careful! the instruction is:

Sheets("Sheet1").Select

And you are putting something else:

----- --
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
----- --