Routine only runs once- VBA

[ViperRR]

Hi fellas,

I’m busting my head here and I simply cannot get this bloody thing to work; what I’m trying to do is to create a macro that runs a routine, which is check if a columns has either ‘OK’ or not and paint it accordingly, (activated by a button, CommandButton1) every x minutes (defined by cRunIntervalSeconds).

The thing is, when I click on the button it runs once but on the second time I get the 'Cannot run the macro CommandButton1_Click . The macro may not be available in this workbook or all macros may be disabled' error; any ideas on how to fix it?

I've tried everything so far, even going for a cup of coffee , but I cannot get the thing to work.


Here's the code:

'This macro's objective is to run a routine (pop-up box, in this case) every x minutes- Me
 
Public RunWhen As Double
Const cRunIntervalSeconds = 10 ' it will run every ten minutes (60 seconds * 10)
Const cRunWhat = "CommandButton1_Click"  ' the name of the procedure to run
 
 
Private Sub StartTimer()
    RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
    Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat, _
        Schedule:=True
End Sub
 
Private Sub CommandButton1_Click()
   
    Dim rng As Range
   
    Set rng = Range("B2:B12") ' You must define a range for this macro to work, which might limit its efficiency...
   
    For Each Cell In rng
        If Cell.Value <> "OK" Then
         MsgBox "There's a Delay!", vbCritical, "Alert!"  ' message that will appear when there's an issue
         Cell.Interior.ColorIndex = 3    ' 3 is Red
         Else
         Cell.Interior.ColorIndex = 4    ' 4 is Green
                         
         End If
    Next
   
    StartTimer ' Reschedule the procedure every cRunIntervalSeconds
   
End Sub

Thanks!!

 

[RoryA]

Your commandbutton_click is an event routine and is therefore private by default, so OnTime can't run it. Move the code to a public routine in a normal module and call that from the button click event. The routine should schedule itself, not the button click code.

 

[steve the fish]

Plus 10 will be ten seconds. And if all cells <> "OK" you will get a procession of messageboxes.

 

[ViperRR]

Your commandbutton_click is an event routine and is therefore private by default, so OnTime can't run it. Move the code to a public routine in a normal module and call that from the button click event. The routine should schedule itself, not the button click code.

Thank you so much, mate: it worked now, even though now the 'warning message' pops up twice every time for each event.

I'll post the solution below, in case someone comes looking for the same thing.

So, the commandbutton_click no calls the collowing code:

Private Sub CommandButton1_Click()

    Call Module1.Routine ' Calls the routine that colours the cells or shows message

    Call Module1.StartTimer ' Reschedule the procedure every cRunIntervalSeconds
    
End Sub

As for the Routine, it has the following code on it:

Public RunWhen As Double
Const cRunIntervalSeconds = 10 ' it will run every ten minutes (60 seconds * 10)
Const cRunWhat = "Routine"  ' the name of the procedure to run


Sub StartTimer()
    RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
    Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat, _
        Schedule:=True
End Sub

Sub Routine()
  
    Dim rng As Range
   
    Set rng = Range("B2:B12") ' You must define a range for this macro to work, which might limit its efficiency...
   
    For Each Cell In rng
        If Cell.Value <> "OK" Then
         MsgBox "There's a Delay!", vbCritical, "Alert!"  ' message that will appear when there's an issue
         Cell.Interior.ColorIndex = 3    ' 3 is Red
         Else
         Cell.Interior.ColorIndex = 4    ' 4 is Green
                         
         End If
    Next
   
       StartTimer  ' Reschedule the procedure every cRunIntervalSeconds
  
End Sub


Sub StopTimer()
    On Error Resume Next
    Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat, _
        Schedule:=False
End Sub

I've also created a second button that calls the 'StopTimer' so the user clicks on it whenever he wants it to stop.

As for the cRunIntervalSeconds, I've setted it for 10 seconds just for testing purpose.


Thanks a lot; now I'm work on it so the error mesage stops showing twice for each incident.