Grouping worksheets

[tartanthing]

I have a whole series of worksheets in a single workbook split in to groups then sub groups. EG Main Worksheet will be 1000 to 2000 with several hundred entries per worksheet going up in increments of 100, so the next group will be 1100, 1200, 1300 etc. those are then split further into 1100.1, 1100.2, 1100.3.
I've seen other workbooks (sorry, tried to find an example I thought I had downloaded) where the only tabs you see are 1100,1200,1300. When you click on a tab-eg 1100 it expands to reveal 1100.1,1100.2,110.3 etc, click on the 1100 tab again and it all contracts and hides the sub groups.
I cant for the life of me find a way to describe this in search which doesn't give me a video or tutorial telling me about groups and altering data. That's not what I want. I just want to clock on a tab and have the next worksheets expand or contract back to the main sheet.
Can someone please help, preferably without VBA? I need to be able to send this off to others to use without having to doomscroll through each open tab to find the right one.
Thanks for your help!

 

[Fluff]

Hi & welcome to MrExcel.

preferably without VBA?

Not that I know of, even with VBA you would need a button or something to run it, as there is no way to detect you have clicked on a tab.

 

[Candyman8019]

You could set up scripts in each of your tabs that when the tab is selected it will show all sub tabs and hide subs from other main tabs. So while activating the tab will display what you want...in order for it to be hidden again, you'll need to select another tab. For example:

Private Sub worksheet_activate()
'replicate this line for subsheets to be displayed
Sheet6.Visible = xlSheetVisible
'replicate this line for subs from other tabs to be hidden
Sheet12.Visible = xlSheetHidden
End Sub

Unfortunately, I think VBA is your only option here...

 

[Candyman8019]

Another option...which feels a bit more clunky to me, but does work, is:

Private Sub worksheet_beforerightclick(ByVal target As Range, cancel As Boolean)
If Sheet6.Visible = xlSheetHidden Then
Sheet6.Visible = xlSheetVisible
' sheet 7.visible = xlSheetVisible
' etc
Else
Sheet6.Visible = xlSheetHidden
' sheet 7.visible = xlSheetHidden
' etc
End If
End Sub

This way, the user can right click anywhere on the sheet and it will toggle hiding and unhiding the subsheets for that section.

 

[Fluff]

Or you could just use a shape/button on each sheet which just calls one macro, rather than having loads of event code.

 

[Candyman8019]

That would work also

 

[tartanthing]

You could set up scripts in each of your tabs that when the tab is selected it will show all sub tabs and hide subs from other main tabs. So while activating the tab will display what you want...in order for it to be hidden again, you'll need to select another tab. For example:

Private Sub worksheet_activate()
'replicate this line for subsheets to be displayed
Sheet6.Visible = xlSheetVisible
'replicate this line for subs from other tabs to be hidden
Sheet12.Visible = xlSheetHidden
End Sub

Unfortunately, I think VBA is your only option here...

I'll give this a try cheers. Will update if successful!