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.
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.
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!
Have you ever been working with data in Excel and experienced a "freeze" where the program stops responding? This can be ...Discover More
If you try to open a workbook that someone else has open, Excel lets you know of the conflict. What if Excel tells you, ...Discover More
Want to limit who can use your workbook and on what system? Locking down a workbook can be trickier than you might imagine.Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
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.