Following code is used to arrange the layout of the 3 windows opened in the same instance of excel so that all 3 are visible together on the same screen in a particular sequence overlapping each other.
Window(1) of Book1 will be "Sheeta", Window(2) will be "Sheetb" of Book1 and Window(3) will be Sheet1 of Book2.
Option Explicit Sub win() On Error Resume Next Dim w1 As Window, w2 As Window, w3 As Window Set w1 = ThisWorkbook.Windows(1) Set w2 = ThisWorkbook.Windows(2) Set w3 = Workbooks("Book2.xlsx").Windows(1) With w1 .WindowState = xlNormal .Top = -13 .Left = 0 .Height = Application.UsableHeight * 1.25 .Width = Application.UsableWidth * 0.25 End With With w2 .WindowState = xlNormal .Top = 300 .Left = 0 .Height = Application.UsableHeight * 0.75 .Width = Application.UsableWidth End With With w3 .WindowState = xlNormal .Top = -13 .Left = 255 .Height = Application.UsableHeight * 0.75 .Width = Application.UsableWidth * 0.75 End With Worksheets("sheeta").Activate Windows(2).Activate Application.Windows("book2.xlsx").Activate End Sub
I need to run the above code every minute using the OnTime Function so as to regularly re-arrange the layout in the in case the layout changes for whatever reasons (such as saving the workbook, clicking on some other sheet of one of the workbooks etc.).
Problem is that the above code needs to be placed in ThisWorkbook module. If I place it in a standard module along with the OnTime Function the code following code gives error
In order to run the OnTime Function, I need to place the code running OnTime in a Standard Module from where I cannot call the above code.
My OnTime code looks like this:
Sub Save() Application.ScreenUpdating = False: Application.Calculation = xlCalculationManual: Application.EnableEvents = False ThisWorkbook.Save TimeToRun = Now + TimeValue("00:00:17") Application.OnTime TimeToRun, "Save" Application.EnableEvents = True: Application.Calculation = xlCalculationAutomatic: Application.ScreenUpdating = True End Sub
Any ideas how to solve this?
Sample Workbook having the above code attached.