I have a Sheet with the Name (in the TAB at the bottom) MainMenu
In the Visual Basic Editor I select the module for ThisWorkbook
which is just below the Sheets objects
In ThisWorkbook module I have the following code:
Private Sub Workbook_Open()
Sheets("MainMenu").Activate
End Sub
Will immediately activate my MainMenu sheet when the workbook is opened.
On this sheet is where I have the setup previously described:
Range A1:AA100 are one solid color.
I have a Title for my workbook
and a cell or drawing object with the words: "Main Menu"
Below this I have Macro Buttons
Each macro button is assgned to a macro
Some of these macros simply activate another sheet.
Others will activate a sheet then perform various tasks.
You said you already have macro with buttons, so just place these buttons
(or make new buttons) on the MainMenu sheet. You likely will have to add a
line at the start of your macros to activate a certain sheet, then continue
with your remianing code.
To get back to the MainMenu sheet you have a couple of options:
1. If a macro just performs an operation like copying/pasting something and
then you want to immediately return the use to the MainMenu, you can enter a
line of code at the end of the macro: Sheets("MainMenu").Activate before
End Sub
2. If you want the user to continue viewing/interacting with another sheet
when your macro ends, then don't reactivate the MainMenu sheet. The user can
get back to the MainMenu by clicking on that TAB at the bottom of the screen,
OR you can have a Button on each sheet that says "Return to Main Menu" The
macro behind each of these buttons would be
Sub GoToMainMenu()
Sheets("MainMenu").Activate
End Sub
Hope this helps