creating a buttom to stop a macro

[daniboymu]

Hi guys! could someone help me
I need a macro so I can create a buttom to stop another macro
this vba code will run this code every 15 minutes, but sometimes I need to turn off this macro and I want to do it whenever I want it
so how can I create another macro to stop that one?

Sub COPIAR()
    If Time < TimeValue("09:00:00") Or Time >= TimeValue("18:15:00") Then End
    Sheets("Banco de Dados").Select
    Range("A5:B5").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Range(Selection, Selection.End(xlUp)).Select
    Range("A6").Select
    ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
        IconFileName:=False
    Range("A2").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    Range(Selection, Selection.End(xlToLeft)).Select
    Range("A5").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Call Temporizador
End Sub


Sub Temporizador()
    'call a macro after  10 minutes
    Call Application.OnTime(Now + TimeValue("00:00:05"), "COPIAR")
End Sub

 

[vw412]

How much experience do you have with VBA?

What you need is a CommandButton on the spreadsheet with a Click event routine that sets a flag (boolean variable) that the Copiar routine tests to see if it should call the Temporizador routine.

 

[daniboymu]

How much experience do you have with VBA?

What you need is a CommandButton on the spreadsheet with a Click event routine that sets a flag (boolean variable) that the Copiar routine tests to see if it should call the Temporizador routine.

unfortunately I know very little of vba, could help me ?

 

[vw412]

Here is some code to do what you want. You will have to put 2 Buttons on the worksheet. One will set the StopTimer variable to False and run the Copiar routine; the other will set the StopTimer to True so the Copiar routine can stop. Then there is an If/Then statement containing the call to the timer routine.

Another way to do this would be to use a cell on the worksheet which is monitored by the routine for a change in value. If that is more to your liking let me know.

Dim StopTimer As Boolean
Sub COPIAR()
    If Time < TimeValue("09:00:00") Or Time >= TimeValue("18:15:00") Then End
    Sheets("Banco de Dados").Select
    Range("A5:B5").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Range(Selection, Selection.End(xlUp)).Select
    Range("A6").Select
    ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
        IconFileName:=False
    Range("A2").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    Range(Selection, Selection.End(xlToLeft)).Select
    Range("A5").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    If Not StopTimer Then
      Call Temporizador
    End If
End Sub

Sub Temporizador()
    'call a macro after  10 minutes
    Call Application.OnTime(Now + TimeValue("00:00:05"), "COPIAR")
End Sub

Sub Button1_Click()
  StopTimer = False
  COPIAR
End Sub
Sub Button2_Click()
  StopTimer = True
End Sub

 

[vw412]

Did this solve your issue or do you need more help?

 

[daniboymu]

Did this solve your issue or do you need more help?

it did, thank you so much!