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.
Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!
Leave your own comment:
Comments for this tip:
Alan Elston 23 Dec 2016, 14:35
I agree using the Code Name is a good idea – It was some time before I recently realised there was one – so a very good point that many will have unlikely changed it.
Alternatively if you know which Tab Number ( Index ) is always used, then you can use that. This +ve Integer Number is the position counting from the left of the Tabs shown at the bottom of the Excel Workbook Window, ( when all worksheets are visible ). Changing Name or Code Name of a Worksheet has no effect on this Number. The number only changes if you change the positions of a Tab, for example, by holding the left mouse down on the Tab and dragging it to a different shown position
Remember if using a variable for this number, be sure you dimension the variable as a number, oor convert it to a Number variable before using it as the Worksheet Index. If you do not do this, it may occur that the number is taken as a String, in which case you will be trying to reference a worksheet which has a String Name of that Number
Sub WorksheetsRef() ' Referrence 2nd Tab counting from the left.
Dim wsTab2 As Worksheet
Set wsTab2 = ThisWorkbook.Worksheets.Item(2)
Set wsTab2 = ThisWorkbook.Worksheets(2)
Dim Tab2No As Long
Let Tab2No = 2
Set wsTab2 = ThisWorkbook.Worksheets.Item(Tab2No)
Set wsTab2 = ThisWorkbook.Worksheets(Tab2No)
In the above code you can, of course, remove ThisWorkbook if the code appears in the ThisWorkbook Class Code Module that has a name something like MyWorkbook or ThisWorkbook
One small point, - Regarding the idea of using The Code Name:
Remember if sharing a code rather than a complete Workbook, and relying on default settings, you will need to ensure that the recipient has the same language version. For example, In English Excel the Code Name of, for example, the First Tab is, I believe “Sheet1”. In my German Excel this it “Tabelle1”
Merry Xmas everyone :)
Barry 22 Dec 2016, 06:02
I appreciate it that you posted several months ago, but my response may help others with similar issue.
Two other contributors have mentioned possible problems Matt on 5-Jan-2016 and MilesS on 8-Aug-2014. As a supplement to Matt's post there is an issue which would cause your problem if someone changed the name of the Tab but didn't reflect this in the macro. The easiest approach is to use the worksheets codename to address it rather than the Tab name (the codename is not something an average (or most advanced) Users would know how to change).
The codename is created when the workbook is first created is the same as the tab name i.e. "Sheet1" has codename Sheet1, etc. and it can be found in the VB Editor navigation pane with the current Tab name next to it. So, for example, if the sheet you want always to be displayed first has the name "FirstSheet" and codename "Sheet1" then the workbook open macro should be written to "Sheet1.Activate".
The issue MileS discusses is still present and I would recommend implementing that as well as you will not know what the security settings are set to on another Users computer and therefore whether or not macros will be allowed to run or not.
saeed ziedan 21 Dec 2016, 18:28
thank you very much for your achievements
you are talented person
i so much use your site and get a lot of useful thing for my work
thank you very much
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?