Activating a Sheet When an Excel File is Opened

Its a macro

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.

Sheet1.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

Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.