Selected Answer
Short code. Long explanation. Start with sheet names.
Every sheet (there are pivot sheets and chart sheets besides worksheets) has 2 names, the tab name, which the user can change from Excel's UI, and the CodeName which can only be changed in the VB Editor's properties. When a sheet is created both names are the same.
In the attached workbook I named the Home tab "Home" and gave it the CodeName "Dashboard". Therefore Worksheets("Home").Name and Dashboard.Name will both return "Home" while Worksheets("Home").CodeName and Dashboard.CodeName will both return "Dashbard". Observe the different syntax. The point is that the user can change the name of the tab to whatever comes to his mind but the code will still find the sheet by its CodeName.
In the attached workbook there are 4 buttons which I numbered carefully. Button Cmd00 has the caption "Tab 0", Cmd01 has the caption "Tab 01" etc. Later, you will replace the captions with your real sheet names but the button numbers and their significance will remain. So, pay attention to these numbers.
In the codesheet for Dashboard there are 4 procedures named like this one.
Private Sub Cmd00_Click()
' 215
GoToTab 0
End Sub
Each of these procedures responds to the command button in its declaration. The above one will run when Cmd00 is clicked. It will call the procedure GoToTab with the parameter 0. The 4 procedures are all identical except for the button name and the parameter. The parameter is always the same as the the serial number in the button's name. As you add more sheets to the system you will need to create more procedures like these four:- Copy > Paste > Change button number > Change parameter = Done. It's easy but must be exact.
The procedure GoToTab is located in a standard code module, TXL_5321 in the attached workbook. It will make the tab identified by the parameter visible and activate it. To be able to do that it needs to know the name of the worksheet. Actually, the code uses the CodeName so that the tab name doesn't matter (except that it's in the button caption which the user sees). The CodeName is identified by the calling argument and extracted from this string in the GoToTab procedure:-
Const CodeNames As String = "Sheet2,Sheet3,Sheet4,Sheet5"
Here you need to list the CodeNames of all your sheets (except Dashboard). The string is split on the commas (be sure not to insert any blank spaces next to the commas) into a 0-based array. So, CodeName(0) = "Sheet2" and 0, of course, is the argument with which the sub was called when Cmd00 was clicked, and the caption of Cmd00 is "Tab 0". Therefore "Sheet2" is the CodeName of "Tab 0".
In the attached workbook, the 4 tabs are VeryHidden. VeryHidden is different from Hidden. A Hidden sheet can be made visible by the user from Excel's UI. A VeryHidden sheet can only be made visible by changing the Visible property in the VB Editor. The user can't even know that it exists without looking into the VBE.
When you click on one of the buttons on the Home tab the chosen tab will be made visible. On it you will see a button. All sheets have copies of the same button. It's called CmdHome, same name on all sheets. Each sheet has this code in its code module - same code for all sheets. Observe the use of the CodeName to address the "Home" tab.
Private Sub CmdHome_Click()
' 215
Dashboard.Activate
End Sub
When the Dashboard is activated the Activate event procedure in its code module will be triggered.
Private Sub Worksheet_Activate()
' 215
Dim Ws As Worksheet
For Each Ws In ThisWorkbook.Worksheets
If Not Ws Is Dashboard Then
If Ws.Visible <> xlSheetVeryHidden Then Ws.Visible = xlSheetVeryHidden
End If
Next Ws
End Sub
This code loops through all sheets and hides them (except the Dashboard itself). This is where you can change xlSheetVeryHidden to xlSheetHidden if you want to allow the user to easily unhide sheets. I decided to hide all sheets at this point although only one should be visible. This is because the user might have made a sheet visible that isn't the one on which he clicked the "Home" button.
BTW, you might copy/paste the entire code module of the Dashboard to the appropriate code module in your own project.