Remembering Workbook Position and Size

by Allen Wyatt
(last updated September 5, 2015)

6

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.

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

MORE FROM ALLEN

Understanding Strikethrough Formatting

The strikethrough text feature in Word can be used as part of your document or to indicate that changes have been made to the ...

Discover More

Indent and Justify Command

WordPerfect users are familiar with the F4 command, which indents and justifies a paragraph. Word does not have an equivalent ...

Discover More

Setting a Length Limit on Cells

Limiting what can be entered in a cell can be an important part of developing a worksheet that other people use. Here are a ...

Discover More

Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!

More ExcelTips (ribbon)

Printing Workbook Properties

Want to create a printed record of the properties associated with a workbook? There is no easy way to do it in Excel. Here's ...

Discover More

Remembering Workbook Settings from Session to Session

You can spend a lot of time getting your workbook to look "just right." Wouldn't it be great if Excel was able to remember ...

Discover More

Seeing All Open Workbook Names

Ever want to see a list of all the workbooks that are open? If you open more than nine, Excel only displays the first nine ...

Discover More
Subscribe

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

View most recent newsletter.

Comments

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 nine more than 9?

2017-11-18 08:44:13

Steve454

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

K.O.

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

Allen

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.

-Allen


2017-07-18 09:36:25

K.O.

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
Subscribe

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.