Remembering Workbook Position and Size

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.

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.

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

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

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

Great code but useless. Just doesn't work

@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

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.

@Randy Vogel
See my comment dated 2020-11-03 below your comment. It addresses your issue.

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.

@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

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?

@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

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.

OOPS! Sorry, I didn't read the rest of the article first where it said to use names.

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.

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.

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?

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?

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

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.

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

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.

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?

Perhaps a better place to put this information would be to place it in customDocumentProperties