+ Reply to Thread
Results 1 to 2 of 2

Application.Ontime issue running macros in wrong spreadsheet with multiple sheets open

  1. #1
    Registered User
    Join Date
    09-23-2013
    Location
    Gold Coast, Australia
    MS-Off Ver
    2010
    Posts
    50

    Application.Ontime issue running macros in wrong spreadsheet with multiple sheets open

    Sorry for the long-winded thread title!

    I've found that when using the Application.Ontime function in multiple spreadsheets that once triggered, the respective subs will be run in ONLY the active spreadsheet.

    For example, I have two spreadsheets that operators use to capture data. I have Application.Ontime functions to trigger macros at specific times of the day for shift changes. The code simply locates the first blank column, inserts the text "Shift Changeover" at the top of the column, and unhides a row for reviewing the document. I've found though that the macros will ONLY fire in the active spreadsheet, and not the spreadsheet in the background - thus making the process redundant:

    Example code in the spreadsheets:

    Please Login or Register  to view this content.
    Please Login or Register  to view this content.

    The same code exists in both spreadsheets, but the cell reference for the "Shift Change" header, and the "Reviewed By" lines are different. As one spreadsheet evolved from the other, they had originally had the same sub names, but I have modified the spreadsheets to give them unique sub names, but still get the same result.

    I cannot figure out how to set timed events using the Application.Ontime function so that subs will be triggered LOCALLY in the respective spreadsheets, and not just in the active spreadsheet.

    Apologies for the wordy explaination, I hope it makes sense to someone and they can help me to figure this one out.

    Thank you!

    A

  2. #2
    Registered User
    Join Date
    09-23-2013
    Location
    Gold Coast, Australia
    MS-Off Ver
    2010
    Posts
    50

    Re: Application.Ontime issue running macros in wrong spreadsheet with multiple sheets open

    Apologies for the poorly worded subject line - it should have read "...in multiple workbooks"

    I've done some research and have decided to try the cpearson method. I wasn't previously aware of the necessity to stop the timer loops, and while this is a side issue, it does answer another problem I was having!

    In an attempt to differentiate the variables from one another across each of the workbooks, I have given all of them unique names. I'm unsure whether I will need to give the procedures unique names also so workbook 4 doesn't call the procedure of the same name from workbook 5.

    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Application.OnTime TimeValue Running at unspecified Times
    By jedahl in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-30-2013, 01:07 PM
  2. Application.Ontime running as soon as WB is opened.
    By jasonfish11 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-30-2012, 01:05 PM
  3. Application.Ontime running behind a UserForm
    By Walter12 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-11-2012, 01:45 PM
  4. Very interesting Application.OnTime problem. (running two sub simultaneously?)
    By kidd1158 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-02-2012, 02:47 AM
  5. [SOLVED] Open Form (VBA Running) and Application.OnTime
    By Ronald Dodge in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-16-2005, 10:45 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1