Remembering Workbook Position and Size

by Allen Wyatt
(last updated May 6, 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, and 2013.

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


Creating a Footer

Adding a predefined footer to your worksheets is easy, and it helps convey valuable information when you make a printout. ...

Discover More

Random Width and Height Changes

Have you ever been using a workbook, only to open it one day and find that Excel has changed the height of your rows or ...

Discover More

Copying Cells to Fill a Range

Excel provides two really helpful shortcuts you can use to fill a range of cells, either horizontally or vertically. ...

Discover More

Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!

More ExcelTips (ribbon)

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

Duplicate Workbooks Opening

If you ever open a workbook and always see two workbooks instead of one, chances are good the reason is because of the ...

Discover More

Noting When a Workbook was Changed

Do you need to know when a workbook was last changed? There are a couple of ways you can go about keeping track of the ...

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 seven more than 6?

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.