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()
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:
- 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.
- Press Alt+F11 to display the VBA Editor.
- 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.
- 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.
- 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.
- 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.
- At the bottom of the list of worksheets is the ThisWorkbook object. Double-click on it. A code window is opened.
- In the code window, paste or create the macro shown above. Make sure you name it exactly as shown.
- Close the VBA Editor.
- 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.
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:
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 = " "
Private Sub Workbook_Open()
Sheets("Clients").Visible = True
Sheets("_instructions").Visible = False
'do whatever needs to be done here
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?