Displaying a specific tab when opening a worksheet

I am a big fan of including an instructions worksheet tab on my dashboards and complex reports. However, most people that read my reports completely ignore this tab.  One day I was thinking about this problem and decided to find a solution to have a workbook open to the instructions tab.  Now by default, Excel will display the worksheet displayed when the workbook was last saved. So when I save my workbook, I always have the Instructions worksheet selected.


However, some people (yes Tom and Nancy I am referring to you) will open my workbook then save it when it’s on a different worksheet, thus negating my effort to have the workbook open to the instructions worksheet tab.


Well I decided to take a different approach when it was necessary for the workbook to open to the instructions tab. I can use the following 3 lines of code in a macro to force the workbook to open to my instructions tab.


Private Sub Workbook_Open()
Worksheets("Instructions").Activate
End Sub


This 3 line macro will always open my workbook to the Instructions tab. Now of course you can modify the worksheet name to be any worksheet tab in a workbook. Just be sure to exactly type the name of the worksheet you want to open.


Okay so how do we add this code??? Follow these steps:


Open the workbook where you want to add this macro.


Press Alt+F11 to display the Visual Basic Editor.


In the Project Explorer window you will see a list of the open workbooks and templates. If you do not see the Project Explorer window, Press CTRL+R or select Project Explorer from the View menu.

Locate your workbook in the Project Explorer. It will be named something like VBAProject (Book1) (if the worksheet has not yet been saved) or the name of your workbook.


You want to expand the workbook to view the individual tabs. You can do that by clicking on the plus sign to the left of the current workbook in the Project Explorer. In the above example I have three worksheet tabs, Instructions, Data and Results. If you only see a list of folders, click on their plus signs to see the worksheet tabs.


Double click on the This Workbook object located at the bottom of the list of worksheets to open the code window.


In the code window, type or paste the 3 lines of blue code.


Private Sub Workbook_Open()
Worksheets("Instructions").Activate
End Sub


Next close the Visual Basic Editor window by clicking on the X at the top right of the window.


Save your workbook.


Now, whenever you open your workbook, the specified worksheet will always be displayed first (regardless of which worksheet was displayed when the workbook was saved)