b.hill
08-08-2012, 08:51 PM
The beginning of my code checks every 5 seconds if cell A18 in sheet "Sheet1" of workbook "Master" equals "1". If A18 does equal "1", the code will check again in 5 seconds. Once A18 does not equal "1", the rest of the code should continue. Cell A18 contains "1" when a macro is running and after it passes the A18 does not equal "1" test.
I am having trouble getting the code to not execute any further while the macro waits 5 seconds to check again. I have inserted "KeepHere" in an attempt to essentially pause the code but Excel hangs up I think for the same reason I chose to use the ontime method instead of the wait method. Nothing else can occur when the macro is "waiting" and my underlying issue is that I have multiple macros (Macro1, Macro2, Macro3, etc) scheduled with ontime to run at the same time sometimes and am trying to force them to run one after the other if they happen to be scheduled for the same time (scheduling time is dependent on value so macros will sometimes be scheduled at the same time).
The end goal I would like to achieve is for the macros to be forced to run in sequence instead of having multiple macros running simultaneously. If you have any other potential solutions I would love to hear the ideas.
Sub Macro1()
If Workbooks("Master.xlsm").Sheets("Sheet1").Range("A18").Value = "1" Then Application.ontime Now + TimeValue("00:00:05"), "Macro1"
KeepHere:
If Workbooks("Master.xlsm").Sheets("Sheet1").Range("A18").Value = "1" Then GoTo KeepHere
'Code
'Code
'Code
'Code
Dim dt As Date
Dim iYear As Integer
Dim iMonth As Integer
Dim iDay As Integer
Dim iDaysDiff As Integer
dt = Sheet1.Cells(2, 2)
iDay = Day(Now)
iMonth = Month(Now)
iYear = Year(Now)
iDaysDiff = dt - DateSerial(iYear, iMonth, iDay)
Select Case iDaysDiff
Case 0 To 0
Application.OnTime Now + TimeValue("00:00:30"), "Macro1"
Case 1 To 2
Application.OnTime Now + TimeValue("00:01:00"), "Macro1"
End Select
End Sub
I am having trouble getting the code to not execute any further while the macro waits 5 seconds to check again. I have inserted "KeepHere" in an attempt to essentially pause the code but Excel hangs up I think for the same reason I chose to use the ontime method instead of the wait method. Nothing else can occur when the macro is "waiting" and my underlying issue is that I have multiple macros (Macro1, Macro2, Macro3, etc) scheduled with ontime to run at the same time sometimes and am trying to force them to run one after the other if they happen to be scheduled for the same time (scheduling time is dependent on value so macros will sometimes be scheduled at the same time).
The end goal I would like to achieve is for the macros to be forced to run in sequence instead of having multiple macros running simultaneously. If you have any other potential solutions I would love to hear the ideas.
Sub Macro1()
If Workbooks("Master.xlsm").Sheets("Sheet1").Range("A18").Value = "1" Then Application.ontime Now + TimeValue("00:00:05"), "Macro1"
KeepHere:
If Workbooks("Master.xlsm").Sheets("Sheet1").Range("A18").Value = "1" Then GoTo KeepHere
'Code
'Code
'Code
'Code
Dim dt As Date
Dim iYear As Integer
Dim iMonth As Integer
Dim iDay As Integer
Dim iDaysDiff As Integer
dt = Sheet1.Cells(2, 2)
iDay = Day(Now)
iMonth = Month(Now)
iYear = Year(Now)
iDaysDiff = dt - DateSerial(iYear, iMonth, iDay)
Select Case iDaysDiff
Case 0 To 0
Application.OnTime Now + TimeValue("00:00:30"), "Macro1"
Case 1 To 2
Application.OnTime Now + TimeValue("00:01:00"), "Macro1"
End Select
End Sub