Remembering Workbook Position and Size

by Allen Wyatt
(last updated November 14, 2019)


Walter has a need to always open a particular Excel workbook in the same location on the screen and with a particular window size. He wonders if there a way to have Excel remember this location and size information upon closing the workbook so it is used when that same workbook is next opened.

One relatively simple solution is to position your workbook (or, often, workbooks) as you want them on the screen and then save the workbooks as a workspace. You do this by displaying the View tab of the ribbon and clicking the Save Workspace tool. It should be noted, though, that this approach works only in Excel 2007 and Excel 2010—the ability to create workspaces was removed from Excel 2013, although you can still open them.

For a solution that works in all modern versions of Excel you'll need to use a macro. You can set up a macro that automatically runs when you close the workbook and another that runs when you open it. The one that runs when you close can save positioning information, and then the one that runs when you open can retrieve that information and use it to locate where the workbook should be displayed.

The question, though, is where to save the positioning information. One solution is to simply write it out into a text file in the same folder where the workbook is stored. That's the approach taken in the following pair of macros.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim fileName As String
    Dim myWindow As Window

    Set myWindow = ActiveWindow
    fileName = "config.txt"
    Open fileName For Output As #1
    With myWindow
        Write #1, .Top
        Write #1, .Left
        Write #1, .Height
        Write #1, .Width
    End With
    Close #1
End Sub
Private Sub Workbook_Open()
    Dim inputStr As String
    Dim fileName As String
    Dim myWindow As Window

    Set myWindow = ActiveWindow
    fileName = "config.txt"
    If Dir(fileName) <> "" Then
        Open fileName For Input As #1
        With myWindow
            .WindowState = xlNormal
            Line Input #1, inputStr
            .Top = inputStr
            Line Input #1, inputStr
            .Left = inputStr
            Line Input #1, inputStr
            .Height = inputStr
            Line Input #1, inputStr
            .Width = inputStr
        End With
        Close #1
    End If
End Sub

The macro that runs when the workbook is closed saves the four positioning locations (.Top, .Left, .Height, and .Width) into a file called config.txt. This file is then read when the workbook is next opened and the appropriate properties for the workbook window are set. If the config.txt file is missing for some reason, then the workbook opens at its default location.

This approach works great if you have a single workbook in the folder that uses the positioning information. If you have multiple ones, each would be overwriting the config.txt file, and you would likely end up with workbooks not being opened where you wanted them.

For some folks, a better approach may be to store the positioning locations in the workbook itself, in named references. That's the approach taken in the following macros.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    On Error Resume Next
    With ThisWorkbook
        With .Names
            .Add Name:="WinTop", RefersToR1C1:="=1"
            .Add Name:="WinLeft", RefersToR1C1:="=1"
            .Add Name:="WinWidth", RefersToR1C1:="=1"
            .Add Name:="WinHeight", RefersToR1C1:="=1"
        End With
        On Error GoTo 0
        .Names("WinTop").RefersTo = ActiveWindow.Top
        .Names("WinLeft").RefersTo = ActiveWindow.Left
        .Names("WinWidth").RefersTo = ActiveWindow.Width
        .Names("WinHeight").RefersTo = ActiveWindow.Height
    End With
End Sub
Private Sub Workbook_Open()
    With ThisWorkbook
        ActiveWindow.Top = Val(Mid(.Names("WinTop").RefersTo, 2))
        ActiveWindow.Left = Val(Mid(.Names("WinLeft").RefersTo, 2))
        ActiveWindow.Width = Val(Mid(.Names("WinWidth").RefersTo, 2))
        ActiveWindow.Height = Val(Mid(.Names("WinHeight").RefersTo, 2))
    End With
End Sub

With this approach you can easily transfer the workbook anywhere you want and the macros take care of saving and reusing the positioning information for the workbook window.


If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (10091) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Office 365.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...


Understanding Discussions

Discussions in a valuable tool for workplace collaboration on the same Word document.

Discover More

Quickly Inserting Tables that Don't Go From Margin to Margin

Adding a table to your document is easy. Adding one that doesn't extend from margin to margin may seem a bit harder. ...

Discover More

Figuring Out the Low-Score Winner

Need to figure out the lowest score in a range of scores? Here's the formulas to get the information you need.

Discover More

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!

More ExcelTips (ribbon)

Accessing a Problem Shared Workbook

What are you to do is you share a workbook with others, and then suddenly the workbook won't open properly? Dealing with ...

Discover More

Reducing File Size

As you work with a workbook (particularly one that contains macros) you may notice that the workbook size can become ...

Discover More

Stopping a Workbook from Persistently Auto-Loading

Excel has the capability to automatically open workbooks when you first start the program. You may not want to have one ...

Discover More

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.


If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 6 - 6?

2020-11-15 09:37:43

Hans Hallebeek

A separate text file is a great solution.
Instead of naming it Config.txt why not name it & ".txt"

That way you can keep one for each workbook.

2020-11-14 10:09:33

J. Woolley

@Randy Vogel
See my comment dated 2020-11-03 below your comment. It addresses your issue.

2020-11-13 15:56:39

Randy Vogel

I'm thinking that the solutions outlined so far ignore the fact that some folks often work on excel files via multiple windows.

For example, when working with complex spreadsheets, it's really common for me to have 2 or 4 windows open so as to be able view/interact with separate tabs/ranges of the worksheet.

Presumably the code above could be embedded in a loop that runs through all of the sub windows of a document...guess I need to go study my VBA references to see how that could be done.

2020-11-03 10:49:55

J. Woolley

To run certain code every time a workbook is opened or a new workbook is created, create an Excel add-in (see

1. Open a new workbook, then press Alt+F11 to open Visual Basic Editor (VBE).
2. Select ThisWorkbook and press F7 to View Code.
3. Copy and Paste the following code, adding your code where indicated:

Private WithEvents MyAppEvents As Application
Private Sub Workbook_Open()
Set MyAppEvents = Application
End Sub
Private Sub MyAppEvents_WorkbookOpen(ByVal Wb As Workbook)
If Wb Is ThisWorkbook Then Exit Sub
... add your code here ...
End Sub
Private Sub MyToolboxEvents_NewWorkbook(ByVal Wb As Workbook)
MyAppEvents_WorkbookOpen Wb
End Sub

4. Select VBAProject and press F4, then change the Name property from VBAProject to MyAppEvents.
5. Close the Visual Basic Editor.
6. Press F12 and pick Save as type: Excel Add-in (*.xlam), then File name: MyAppEvents. Copy the folder location for use in step 7 (default is like C:\Users\[Name]\AppData\Roaming\Microsoft\AddIns), then click Save.
7. Close Excel (without saving the temporary workbook created in step 1), then reopen Excel.
8. In Excel's Backstage view, pick Options > Trust Center > Trust Center Settings… > Trusted Locations. If the folder location copied from step 6 is not already listed, click Add new location…, then add it.
9. Pick Options > Add-ins > Manage: Excel Add-ins > Go… > Browse…, then select MyAppEvents.xlam. (Excel Add-ins can also be managed using the Developer ribbon.)

As long as the MyAppEvents add-in is enabled, your code will run every time a workbook is opened or a new workbook is created.
BTW, you might be interested in this related Tip:
And you might be interested in my web site:

2020-11-02 15:27:01


This is a great idea. it seems like you have to add this code to every workbook that you open and work on and then save it after you insert this macro. Is there any way to have this code run on any workbook that is opened or closed that way I don't have to insert this code into every workbook? Is there a spot that I can put this code so it will trigger with any workbook that I work on?

2020-05-04 00:12:29



try this:

With ThisWorkbook
ActiveWindow.WindowState = xlNormal
ActiveWindow.Top = Val(Mid(.Names("WinTop").RefersTo, 2))
ActiveWindow.Left = Val(Mid(.Names("WinLeft").RefersTo, 2))
ActiveWindow.Width = Val(Mid(.Names("WinWidth").RefersTo, 2))
ActiveWindow.Height = Val(Mid(.Names("WinHeight").RefersTo, 2))
End With

2020-02-06 14:21:17


I tried the 2nd method with Excel 2016, but I keep getting this error:
Run-time error 1004: Unable to set the Top property of the Window class

I'm assuming it has something to do with the "named references". What exactly does it mean when he says " store the positioning locations in the workbook itself, in named references." Is named references the same as name ranges because every time I google search it, it comes back as name ranges, yet I still don't understand how that would play into getting this to work.

2019-11-14 19:21:34

Alan Cannon

OOPS! Sorry, I didn't read the rest of the article first where it said to use names.

2019-11-14 19:19:34

Alan Cannon

To avoid an external file that could be edited or deleted, just store the values in worksheet names then retrieve and apply them when the workbook is opened. Names in workbooks/worksheets don't have to reference a cell range or address; they can store constants. They can even store a formula.

2019-11-14 04:47:17

Ken Varley

I would disagree with this approach because if the workbook is moved from its current folder at some point in the future, the text file might not be moved with it.

My solution would be to add a worksheet to the workbook. Call it CONFIG. Then write the information into that sheet. The sheet could be hidden so as not to be a distraction to users.

Doing it this way ensures that the saved info travels with the workbook, wherever the workbook gets moved to in the future.

Aso, I haven't tried it, but am I not correct in saying that if the workbook & the text file get seperated, it will result in error messages when opening the workbook.

2019-06-14 14:06:30

Ged L

I am using the above version saving to named ranges but when I reopen the workbook moves to the correct position but loses height.
each time the position is saved and then the workbook re-opened, it loses more height.

Also, I had to use Application.Top etc rather than ActiveWindow.Top as I get an error in Excel 2010 that relates to the ActiveWindow.Top property being read only in 2010 onwards?

2018-04-20 08:54:43

Glenn Rowe

I am using the second method (without the config file). It works except for the fact that I have used the "New window" option and have multiple tabs in different windows. How do I save that?

2017-11-18 08:44:13


Can you please provide step-by-step instructions on which macro to use first? I use two Excel windows. The first file contains information and macros and always must stay active. The second window will be files I work on which are controlled by the first Excel macro file. I just need specifics on how to begin to run the macro. Thanks! -Steve

2017-07-18 10:38:38


Amending my prior comment ...

I was able to get this to work in Excel v2016. The problem I had was something to do with the directory in which the config.txt file was being saved as when I created a brand new workbook put the code in and saved to my desktop it worked. Then I went to the workbook in which I wanted to save location/size (e.g. ABC.xlsm) and added the code and it saved the config.txt file back to the desktop (not sure why). So I modified the code to ensure the config.txt file is saved to the same directory in which the workbook (e.g. ABC.xlsm) exists. In addition I added code to append the file name (e.g. ABC) to the end of "config" and thus it makes a unique config file for each workbook.

In the config.txt version I added the following 3 lines of code following "Set MyWindow as Window" in both the BeforeClose and Open events.

filename = ThisWorkbook.Name
filename = Left(filename, InStrRev(filename, ".") - 1)
filename = Application.ActiveWorkbook.Path & "\config " & filename & ".txt"

So for my example ABC.xlsm the config file will be saved to the same directory ih which ABC.xlsm is stored and the config file will be name "Config ABC.txt".

I could have used your second option (saving the size/location w/i the workbook itself) but if I move this file to another computer I don't want the workbook to necessarily open in the same location (at least not the first time) . With displays of differing resolution (4k, 1080p, etc) I didn't want to have to worry about that.

Thanks again for your original post.

2017-07-18 09:51:50


KO: It should work just fine under Excel 2016, provided that config.txt is being written to a folder for which you have permission. If you don't have permission, then you should get an error when the macro runs. If you see no error, then the file is being written, but probably to an unexpected folder of some type.

You can check that out by figuring out a place you want it to be written. For instance, pick a full path, and then expand the line in the code to reflect that path:

fileName = "c:\thisfolder\thatfolder\anotherfolder\config.txt"

Of course, you MUST change this to reflect something appropriate for your particular system. With this full path in place, you should be able to track down the file with no problem.


2017-07-18 09:36:25


Thanks Allen. Very simple solution but when I tried the first version (i.e. the config.txt) in Excel 2016 (365) and it didn't work at all. No config.txt was even created. Is there any way you could take a look and update your post for Excel 2016.

2017-03-08 08:15:19

George Sullivan

Not quite working as I hoped. While my spreadsheet opens in the same position, the size is not maintained. If I start Excel and spread it out almost full screen, do some work in a cell, then save it as TEST1.xls and quit that spreadsheet the subsequent opening of my "One-Size-Only" spreadsheet is the size of TEST1.xls not the smaller fixed size I hoped for. Any ideas?

2015-09-06 12:33:04

William Meyer

Perhaps a better place to put this information would be to place it in customDocumentProperties

This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.