There are times when you would like to have the same sheet visible each time a file is opened. You can achieve that with a Workbook Event macro.
The file needs to be saved as a Macro-enabled (.xlsm) or a Binary file (.xlsb). Standard (.xlsx) Excel workbooks can’t contain macros.
The command to activate a specific sheet is easy. The image below has the code to activate Sheet1 – this is the codename, not the tab name. The tab name is Control. The codename is the name shown on the left of screen (highlighted yellow), the tab name is shown in brackets.
The Workbook_Open Event runs when the file is opened. It requires that macros are enabled for the Event to be triggered.
The command is the sheet codename followed by the full stop and the word Activate.
To access the Workbook Events click the ThisWorkbook option under the Microsoft Excel Objects and then click the left drop down, above the code window – see image below – and select Workbook. This will create a blank Workbook_Open event. Enter the single line of code as shown in the images.
To access other Events you click the drop down on the right side above the code window – see image below.
There are many Workbook Events to choose from. Some of the more common ones to use are
- BeforePrint – runs before the file is printed or page setup is displayed
- BeforeSave – runs just before the file is saved
- BeforeClose – runs before the file is closed