question on creating a menu tab

[pyclen]

I found a solution to hide tabs that I don't need to show, the downside is all other tabs get hidden however I want a summary tab to show AND the tab (or tabs) selected.

I have to say I have no clue about VBA therefore I may not completely know how to do things, i.e., I would appreciate detailed guidance if possible.
I am not sure if this XL2BB will convey the info I am after as I would rather upload a file to show what is needed but I posted a screenshot showing the menu and all other tabs.
I would like to have the Menu, Summary and a testing or more tabs show when I select a sheet.
Thank you

Menu.xlsm
	H
9
Menu

 

[pyclen]

Welcome to the MrExcel Message Board!

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: https://www.myonlinetraininghub.com/excel-forum/vba-macros/question-on-creating-a-menu-tab
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.

Apologies, I thought it would be more efficient to do so but I will post something new

 

[pyclen]

Hi Peter

Please see below for answers (in red) to your questions
1. Do you want menu tab always visible? If not, how would you later select different tabs to be be visible? Yes, that is the intent, Menu AND Summary Always visible
2. Do you want Summary tab always visible? Yes, that is the intent, Menu AND Summary Always visible
3. Is sheet Admin_Lists from post #5 the same as sheet 'Lists' from the image in post #1? Yes, the lists are the same, just renamed it, however that tab just contains the dropdown list w/the tab names and I can put that list on the menu tab out of sight to the right and get rid of the List tab
- If so, can you confirm what its actual name is?
- If not can you clarify what the two sheets are?
4. Should the Admin_Lists/Lists sheet(s) be hidden or visible as I note that they are not in the Data validation list? should be hidden, but I can also move the list to the menu tab and move it to the far right so it is not visible
5. Do you want to be able to select multiple 'Testing' sheets to be visible at the same time? If that is possible to have multiple such tabs show that would be great

 

[Peter_SSs]

Thanks for the answers. See if this would suit you.
I have eliminated the Admin_Lists sheet. Of course if there are things on it besides the list of sheets to hide/show then you can keep it.
On the Menu sheet I have made a formal Excel table as shown below. I have named this table (in the Name Manager on the Formulas ribbon tab) as TblShowHide
You can add/delete rows in this table as required.

pyclen.xlsm
	B	C	D	E
1
2		Show/Hide Sheets	Mark to Show
3		All
4		Testing 1
5		Testing 2	x
6		Testing 3
7		Testing 4	x
8
Menu

I have then used the following Worksheet_Change code.
To implement ..
1. Right click the sheet name tab and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1.
3. Test by adding/deleting values in the second table column.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Changed As Range, c As Range, rMTS As Range
  Dim i As Long
  Dim bShowAll As Boolean

  Set rMTS = Range("TblShowHide[Mark to Show]")
  Set Changed = Intersect(Target, rMTS)
  If Not Changed Is Nothing Then
    bShowAll = Len(rMTS.Cells(1).Value) > 0
    On Error Resume Next
    For i = 2 To rMTS.Rows.Count
      Sheets(Range("TblShowHide[Show/Hide Sheets]").Cells(i).Value).Visible = IIf(bShowAll, True, Len(rMTS.Cells(i).Value) > 0)
    Next i
    On Error GoTo 0
  End If
End Sub

To show any of the sheets, put anything in the 'Mark to Show' column. If there is anything in the table beside 'All' then all sheets will be shown and any other values below will be ignored.
If you have 'All' marked then you would need to unmark it before you could hide any of the sheets (by removing any text beside their names)

 

[pyclen]

Thank you very much for this solution, it works like a charm and is easily adaptable and can be expanded for more than these 5 or 6 tabs.
I really appreciate your help and time; this will be particularly useful for me and my project as we sometimes generate some very unwieldy large spreadsheets w/costs, and this way we can blend out some sheets we don't use or to make it easier to summarize things.

 

[Peter_SSs]

You're welcome. Glad you found it useful. Thanks for the follow-up.