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.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (10091) applies to Microsoft Excel 2007, 2010, and 2013.
Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!
Want to create a printed record of the properties associated with a workbook? There is no easy way to do it in Excel. ...Discover More
If you are afraid of messing up a workbook, consider doing your work on a clone of the workbook. Excel provides an easy ...Discover More
Need to allow others to contribute to your Excel workbook? It's easy to do if you just share it. This tip provides an ...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.