Running a macro in ThisWorkbook with OnTime function

  • Hi!


    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.



    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

    Code
    Worksheets("sheeta").Activate
    Windows(2).Activate
    Application.Windows("book2.xlsx").Activate


    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:

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


    Thanks

  • Re: Running a macro in ThisWorkbook with OnTime function


    you are mistaken... the code does not need to be in the thisworkbook module.



    sorry for typing errors.... cooking accidentr has made a finger tip psart of dinner.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!