Are you annoyed with always having to adjust the size and location of an Excel workbook you open every day? Here’s the solution you didn’t know you needed.
Audience: Basic Excel skills. No prior programming or VBA experience needed.
Challenge: When I open this workbook, I want it in the corner of the screen and sized just enough to see the content of the Start sheet.
Solution: VBA!
This method will work even if you have multiple Excel windows open, as separate sessions or the same session. Even if you hit “Save” on a different window size, once reopened, VBA code will execute and put the starting point of your workbook exactly as you want it.
Step 1. On the Developer tab, click Visual Basic.
If you don’t see Developer tab, go to Options > Customize Ribbon > add Developer to the right-side list of visible tabs.
Step 2. Open ThisWorkbook
You’ll see a list called Microsoft Excel Objects in a Project – VBA Project window. On the list, you’ll find each sheet within your workbook. Since we will write code that affects the entire workbook, we want to open the last choice in the list. Double-click ThisWorkbook to open it.
Step 3. The code
Let’s analyze the code we’ll need.
Workbook_Open() is meant to specify what you want VBA to perform when the workbook is opened.
.WindowState specifies if the window should be maximized, minimized or normal (resized by you).
.Top and .Left will specify where you want your Excel window to open on your screen.
.Width and .Height will specify what size the Excel window should be.
.Goto will provide focus on a specific cell in a specific sheet.
In other words, even if last time you used this workbook, you had it maximized, and focused on a different sheet, when you open it again, it will be exactly the size you want, placed on the part of the computer screen you want to see it in and the starting sheet will always be the same.
Use below code as a template and adjust it to the location, size and focus cell you want.
Private Sub Workbook_Open()
'Set small window size and start on Start sheet.
With Application
.WindowState = xlNormal
.Top = 25
.Left = 25
.Width = 150
.Height = 240
End With
Application.Goto Worksheets("Start").Range("A1"), True
End Sub
Tip:
If you want the window maximized, change to .WindowState = xlMaximized
If you want the window minimized, change to .WindowState = xlMinimized
For both options, you won’t need top, left, width, and height properties. You can then shorten the code to:
Private Sub Workbook_Open()
'Set maximized window size and start on Start sheet.
Application.WindowState = xlMaximized
Application.Goto Worksheets("Start").Range("A1"), True
End Sub
Step 4. Test
To check if the code works and to get the size just right, hit the green arrow (or hit F5) with your cursor within the Private Sub Workbook_Open() code. Feel free to do this as many times as you need.
Step 5. Save
Save the workbook in .xlsm format to preserve the VBA code.
Step 6. Test again
Close the workbook and open it again to confirm that it opens exactly the way you wanted it. Resize it, move it on the screen, open a different sheet, save, and close. Open it again. It should go back to the state you programmed.
And that’s it. Did this help you? Let me know in the comments below.
