Excel.Tips.Net ExcelTips (Menu Interface)

Default Worksheet when Opening

Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Default Worksheet when Opening.

When you open a workbook, Excel normally displays the worksheet last displayed when the workbook was last saved. You may want a specific worksheet to always be displayed when the workbook is opened, regardless of the worksheet displayed when the workbook was last saved.

You can control which worksheet is displayed by using this macro:

Private Sub Workbook_Open()
End Sub

This macro will always display a worksheet named StartSheet. You will obviously need to change the worksheet name to something different; it should exactly match the name of the desired worksheet.

For this macro to work properly, it has to be associated with the workbook object. Follow these steps:

  1. Make sure you have only a single Excel workbook open. While this isn't exactly mandatory, it will make creating the macro a bit easier.
  2. Press Alt+F11 to display the VBA Editor.
  3. In the Project Explorer window you will see a list of the open workbooks and templates. If the Project Explorer is not visible on your screen, choose Project Explorer from the View menu.
  4. Locate your current workbook in the Project Explorer. It will be named something like VBAProject (MyWorkbook), where "MyWorkbook" is the name of the actual workbook.
  5. If there is a plus sign to the left of the current workbook in the Project Explorer, click on it. You should see a list of worksheets appear underneath the workbook.
  6. If you don't see a list of worksheets, but instead see a list of folders with plus signs to their left, click on the plus sign to the left of Microsoft Excel Objects. Now you should see the worksheets.
  7. At the bottom of the list of worksheets is the ThisWorkbook object. Double-click on it. A code window is opened.
  8. In the code window, paste or create the macro shown above. Make sure you name it exactly as shown.
  9. Close the VBA Editor.
  10. Save your workbook.

Now, whenever you open the workbook, the specified worksheet will be displayed.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2014) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Default Worksheet when Opening.

Related Tips:

Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!


Leave your own comment:

  Notify me about new comments ONLY FOR THIS TIP
Notify me about new comments ANYWHERE ON THIS SITE
Hide my email address
*What is 5+3 (To prevent automated submissions and spam.)
           Commenting Terms

Comments for this tip:

Rachel Cook    25 Aug 2016, 05:45
Ive done the above but when i re open my workbook it shows a run time erro 9 subscript out of range message. Any ideas?
Matt    05 Jan 2016, 12:21
Reply to Anand.

Make sure there are no spaces in your sheet name. Try using a single word.
Kuttotta    18 Jun 2015, 04:09
Wow at last I got a resolution for what I was looking for long time, thank you very much.
MilesS    08 Aug 2014, 02:33
Reply to Meyn.
Yes very easy to setup.
1: On before_close, hide all windows except startup sheet.
2: Startup sheet says "switch on macros"
3: On_load unhides sheets and takes the user to the page you want to go to.

4: As creator, save the file. 1 above is triggered. New user opens the file and they only see the "enable macro sheet"

sample code below

'note a for each loop would have been better

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'note use a for each instead of specified below
    Sheets("_instructions").Visible = True
    Sheets("Clients").Visible = xlVeryHidden
'xlVeryHidden means programming change to open required

    Application.StatusBar = " "
End Sub

Private Sub Workbook_Open()
    Sheets("Clients").Visible = True
    Sheets("_instructions").Visible = False

'do whatever needs to be done here

End Sub
Meyn    18 Jul 2014, 01:28

this is always a good tip, when the user has already allowed to use macros on the specific document. But my point is as long the document has "never" been opened on the user's computer. So long he will see the last used sheet. Is there a way to force excel to open with a specific sheet without using VBA?
Diogenes Ballesteros    03 Apr 2013, 04:17
I'm very thankful for this tip and finally I was able to make it work and submit my work to my superior.

Again, thank you very much.
Anand    03 Apr 2013, 01:50
I have followed the procedure as you have stated, but my workbook still opens it in sheet where i saved it.. please help..
Azeem    28 Feb 2013, 01:52
You would be very happy too see that ppl like me are still benefited from your site. Thanks a lot for your effort.
Your step by step explanation made a dump like me feel like a programmer hehe.
Nina    21 Feb 2013, 23:20
This was excellent advice and simple to apply - worked a treat, thank you!
Jordan Freedman    19 Nov 2011, 08:03
Will you point me toward some information about the basics of writing a macro using the VBA editor?

Our Company

Sharon Parq Associates, Inc.

About Tips.Net

Contact Us


Advertise with Us

Our Privacy Policy

Our Sites


Beauty and Style




DriveTips (Google Drive)

ExcelTips (Excel 97–2003)

ExcelTips (Excel 2007–2016)



Home Improvement

Money and Finances


Pests and Bugs

Pets and Animals

WindowsTips (Microsoft Windows)

WordTips (Word 97–2003)

WordTips (Word 2007–2016)

Our Products

Helpful E-books

Newsletter Archives


Excel Products

Word Products

Our Authors

Author Index

Write for Tips.Net

Copyright © 2016 Sharon Parq Associates, Inc.