Remembering Workbook Position and Size

Written by Allen Wyatt (last updated November 14, 2023)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365


25

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.

Note:

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

MORE FROM ALLEN

Where is Toolbar Customization Information Stored?

When you make a change to one of Excel's toolbars, do you know where that change is stored? This tip explains the name ...

Discover More

Calculating Only the Active Workbook

When you enter information into a workbook, Excel automatically recalculates every worksheet in every open workbook on ...

Discover More

Formatting Text in Custom Document Properties

Word allows you to create custom document properties that stay with a document and can be inserted through the use of ...

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)

Creating Individual Workbooks

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

Discover More

Determining If a Workbook is Referenced by Another Workbook

It is a difficult task, in Excel, to determine if a workbook is the target of any external links. This tip examines some ...

Discover More

Using a Single Password for Multiple Workbooks

While password protecting a workbook does provide some security for the contents in the workbook, if you have several ...

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}] (all 7 characters, in the sequence shown) 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 five more than 3?

2023-05-23 09:40:36

Tom

Thanks for the tips. Never knew that there was an after save event! Love learning new stuff! Anyway, my example is just for personal use. It seems to work for our purposes here at our small office for existing workbooks. The reason I use the registry is because the settings are specific to the hardware and not the workbook itself. If multiple people use the workbook as in our case, the last user's settings would trump (if saved). Also, I have had less trouble storing settings in the registry over the years as opposed to files.


2023-05-14 09:52:50

J. Woolley

Re. my latest comment below, if you put Tom's procedures in an Excel add-in as described in my Nov 2020 comments below or in Personal.xlsb, you must change Me.… references to ActiveWorkbook.… references.


2023-05-13 12:31:17

J. Woolley

@Tom
Your procedures are very clever. It is worth noting:
1. They must be located in the workbook's ThisWorkbook document module.
2. They utilize the Windows Registry; see https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/savesetting-statement
Instead of the Registry, the Tip suggests using defined names and William Meyer's comment dated Sep 2015 suggests using CustomDocumentProperties; both suggestions store the information with the workbook.
3. Using Me.Name instead of Me.FullName applies the settings to any workbook with the active workbook's name regardless of location on the current computer.
4. The Workbook_BeforeSave procedure should be replaced by the following; otherwise, Me.Name might not be as expected:

Private Sub Workbook_AfterSave(ByVal Success As Boolean)
If Success Then SaveWinsState
End Sub

Finally, see my comments dated Nov 2020 below.


2023-05-12 13:12:21

Tom

Helpful for those working with multiple windows.

Private Sub Workbook_Open()
RestoreWinsState
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
SaveWinsState
End Sub

Private Sub SaveWinsState()
Dim win As Window, winData As String
For Each win In Me.Windows
Debug.Print Join(Array(win.ActiveSheet.Index, win.Top, win.Left, win.Width, win.Height), "/")
winData = winData & Join(Array(win.ActiveSheet.Index, win.Top, win.Left, win.Width, win.Height), "/") & "\"
Next
SaveSetting Me.Name, "UserSettings", "WinsState", Left(winData, Len(winData) - 1)
End Sub

Private Sub RestoreWinsState()
Dim winData As String, winsData() As String, d() As String, x As Integer
winData = GetSetting(Me.Name, "UserSettings", "WinsState", "")
If winData = "" Then Exit Sub
winsData() = Split(winData, "\")

For x = 0 To UBound(winsData) - Me.Windows.Count
Me.NewWindow
Next
For x = 1 To UBound(winsData) + 1
d() = Split(winsData(x - 1), "/")
With Me.Windows(x)
.Activate
Sheets(CInt(d(0))).Select
.WindowState = xlNormal
.Top = d(1)
.Left = d(2)
.Width = d(3)
.Height = d(4)
End With
Next
End Sub


2022-12-18 05:25:01

Hans

Hi, I solved this by storing the position and windowsize I want in my Personal Macrobook in the workbook_before_close section.
So now any other workbook I open sets it's own size but when the Excel session ends my initial values are restored.
You can alos add a button to the QAT to reset it to those values


2022-12-17 12:21:13

tomassi

Great code but useless. Just doesn't work


2021-02-09 18:39:24

Anthony Rose

@Hans Hellebeek, I had the same thought. I always have one Excel workbook open in which I track my work. I saved the following code into its ThisWorkbook module. This code uses the Excel instance's App level events. It works well for all other workbooks I open and close.
It also addresses a weird bug where ActiveWindow returns Nothing, instead I use the affected Workbook's window

Put the following code into the ThisWorkbook module of an excel Workbook that can stay open the whole day:

' Define 'App' and say we want events
Private WithEvents App As Application
' and on opening this workbook, grab this Excel instance as our 'App'
Private Sub Workbook_Open()
Set App = Application
End Sub

' When any workbook is opened...
Private Sub App_WorkbookOpen(ByVal Wb As Excel.Workbook)
Dim fileName, inputStr As String

With Wb.Windows(1)

fileName = FileWindowSettingsName(.Caption)

If Dir(fileName) > "" Then
Open fileName For Input As #1
.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
Close #1
End If

End With

End Sub

' When any workbook is closed
Private Sub App_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean)

With Wb.Windows(1)

Open FileWindowSettingsName(.Caption) For Output As #1
Write #1, .Top
Write #1, .Left
Write #1, .Height
Write #1, .Width
Close #1

End With

End Sub

Private Function FileWindowSettingsName(ByVal winCaption As String) As String

winCaption = winCaption & "."
FileWindowSettingsName = Left(winCaption, InStr(1, winCaption, ".") - 1) & "-window.txt"

End Function


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 Thisworkbook.name & ".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

@Beau
To run certain code every time a workbook is opened or a new workbook is created, create an Excel add-in (see www.cpearson.com/Excel/AppEvent.aspx):

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: https://excelribbon.tips.net/T010939_Setting_Program_Window_Size_in_a_Macro.html#comment-form-hd
And you might be interested in my web site: sites.google.com/view/MyExcelToolbox


2020-11-02 15:27:01

Beau

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

helpful_inc

@Modify_inc

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

Modify_inc

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

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.