Remembering Workbook Position and Size

by Allen Wyatt
(last updated September 5, 2015)

2

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

Creating the 'Mils' Symbol

Different industries use their own terminologies and symbols. In the military, one symbol is referred to as the "mils" ...

Discover More

Forcing a Macro to Run when a Worksheet is Recalculated

Normally a macro is only calculated when you specifically tell Excel to calculate it. Some macros need to be calculated ...

Discover More

Using a Standard Format in a Suggested File Name

Many companies (and some individuals) use specific formats for naming their documents. If you want Word to recognize your ...

Discover More

Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!

More ExcelTips (ribbon)

Turning Off Sharing

All good things must come to an end at some point. When you are done sharing your workbook with others, this is how you can ...

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

Creating Individual Workbooks

Workbooks can contain many worksheets. If you want to pull a workbook apart and create a whole series of workbooks based on ...

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. 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 five less than 5?

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.