VBA button click from within a macro

[Chris_yorks]

Hi,
I have a lovely workbook set up with a few, not so complicated macros for displaying a timer countdown. Various buttons for initialising, starting, stopping and changing tabs.

I can stop the timer quite easily by clicking on the stop button I created - This calls a macro stop_timer
I would like to call this macro when a certain cell has reached zero - I have that part working, however when I try to call this macro from another sub, I get an error "Runtime error 1004 Method 'OnTime' of object '_Application' failed

The final line of this sub, is highlighted in yellow when it fails :-
Sub stop_timer()
ActiveSheet.CommandButton3.Visible = False
ActiveSheet.CommandButton1.Visible = False
ActiveSheet.CommandButton2.Visible = True
ActiveSheet.CommandButton2.Activate


Application.OnTime EarliestTime:=interval, Procedure:="timer", Schedule:=False
End Sub

I was hoping that calling the sub would be the same as clicking on the button The button is CommandButton3.
Whether it is visible or activated or not, the same error occurs

For info, I have also tried calling a button click, to simulate the actual click which gives the same error.

Any help would be greatly appreciated.

Thanks for your time

 

[6StringJazzer]

Please show all of the code (and it is much easier to read your code if you mark it as code using the VBA button), including the code that sets the timer, and the code that calls sub stop_timer. In particular I need to see where the variable interval is declared, set, and used.

 

[Chris_yorks]

Thank you so far.
I hope this is what you needed...

 Public interval As Date

 Sub timer()
 
 

ActiveSheet.CommandButton1.Visible = False
ActiveSheet.CommandButton2.Visible = False
ActiveSheet.CommandButton3.Visible = True
ActiveSheet.CommandButton3.Activate
 
     interval = Now + TimeValue("00:00:01")
     Rem If Range("F2").Value = 0 Then Call next_tab
     If Range("F2").Value = 0 Then Range("F2") = Range("F2") + 1
     If Range("D3").Value = 0 Then Range("D3") = Range("D3") + 1
     If Range("D4").Value = 0 Then Range("D4") = Range("D4") + 1
     If Range("D5").Value = 0 Then Range("D5") = Range("D5") + 1
     If Range("D6").Value = 0 Then Range("D6") = Range("D6") + 1
     If Range("D7").Value = 0 Then Range("D7") = Range("D7") + 1
     If Range("D8").Value = 0 Then Range("D8") = Range("D8") + 1
     If Range("D9").Value = 0 Then Range("D9") = Range("D9") + 1
     If Range("D10").Value = 0 Then Range("D10") = Range("D10") + 1
     If Range("D11").Value = 0 Then Range("D11") = Range("D11") + 1
     If Range("D12").Value = 0 Then Range("D12") = Range("D12") + 1
     If Range("D13").Value = 0 Then Range("D13") = Range("D13") + 1
     If Range("D14").Value = 0 Then Range("D14") = Range("D14") + 1
     If Range("D15").Value = 0 Then Range("D15") = Range("D15") + 1
     If Range("D16").Value = 0 Then Range("D16") = Range("D16") + 1
     If Range("D17").Value = 0 Then Range("D17") = Range("D17") + 1
     If Range("D18").Value = 0 Then Range("D18") = Range("D18") + 1
     If Range("D19").Value = 0 Then Range("D19") = Range("D19") + 1
     If Range("D20").Value = 0 Then Range("D20") = Range("D20") + 1
    

     Range("F2") = Range("F2") - TimeValue("00:00:01")
     Range("D3") = Range("D3") - TimeValue("00:00:01")
     Range("D4") = Range("D4") - TimeValue("00:00:01")
     Range("D5") = Range("D5") - TimeValue("00:00:01")
     Range("D6") = Range("D6") - TimeValue("00:00:01")
     Range("D7") = Range("D7") - TimeValue("00:00:01")
     Range("D8") = Range("D8") - TimeValue("00:00:01")
     Range("D9") = Range("D9") - TimeValue("00:00:01")
     Range("D10") = Range("D10") - TimeValue("00:00:01")
     Range("D11") = Range("D11") - TimeValue("00:00:01")
     Range("D12") = Range("D12") - TimeValue("00:00:01")
     Range("D13") = Range("D13") - TimeValue("00:00:01")
     Range("D14") = Range("D14") - TimeValue("00:00:01")
     Range("D15") = Range("D15") - TimeValue("00:00:01")
     Range("D16") = Range("D16") - TimeValue("00:00:01")
     Range("D17") = Range("D17") - TimeValue("00:00:01")
     Range("D18") = Range("D18") - TimeValue("00:00:01")
     Range("D19") = Range("D19") - TimeValue("00:00:01")
     Range("D20") = Range("D20") - TimeValue("00:00:01")
  
    
    
     Application.OnTime interval, "timer"
   

 End Sub
 Sub stop_timer()
 ActiveSheet.CommandButton3.Visible = False
 ActiveSheet.CommandButton1.Visible = False
 ActiveSheet.CommandButton2.Visible = True
 ActiveSheet.CommandButton2.Activate
 
      Application.OnTime EarliestTime:=interval, Procedure:="timer", Schedule:=False
 End Sub
 
 Sub initialise()
'
' initialise Macro
' Macro recorded 19/12/2022 by Wi
'

'
complete = 0
    Range("S3:S20").Select
    Selection.Copy
    Range("D3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Rem Track remaining
    Range("R3").Select
    Selection.Copy
    Range("F2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
       
    Range("A1").Select
   
   
   
 
   
    ActiveSheet.CommandButton3.Visible = False
    ActiveSheet.CommandButton1.Visible = True
    ActiveSheet.CommandButton2.Visible = False
   
    ActiveSheet.CommandButton1.Activate
   

       
End Sub



Sub next_tab()


If ActiveSheet.Index = Worksheets.Count Then
Worksheets(1).Select
Else
ActiveSheet.Next.Select
End If
End Sub

I wanted to stop timer when cell F2 reaches zero, and then run next_tab
Stopping timer by clicking the command button works, but not when I run stop_timer from within timer (That is when I receive the error 1004 Method 'OnTime' of object '_Application' failed

I could simply exit timer at F2=0 , but I am unsure hot to then how to then run next_tab automatically

I have successfully run next_tab when cell F2 reaches zero, using

 If Range("F2").Value = 0 Then Call next_tab

which is currently REMmed out. When I do this though, timer is still running.



Thank you