Routine only runs once- VBA

ViperRR

New Member
Joined
Sep 9, 2014
Messages
2
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 :LOL:, but I cannot get the thing to work.


Here's the code:

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!!
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
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.
 
Upvote 0
Plus 10 will be ten seconds. And if all cells <> "OK" you will get a procession of messageboxes.
 
Upvote 0
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:

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:

Code:
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.
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,927
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top