Excel: How to get a workbook to always look the same when you open it – even size and location on your screen

This handy time-saving tip will open a specific workbook in a desired window size, on a specific part of the computer screen and show you your chosen sheet. Spoiler alert: it’s super easy.
Tired eyes? Listen to it instead.
Voiced by Amazon Polly

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: 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

If you want the window maximized, change to .WindowState = xlMaximized

If you want the window minimized, change to .WindowState = xlMinimized

And remove 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, close and open again. It should go back to the state you intended.


And that’s it. Did this help you? Let me know in the comments below.

Leave a Reply