Stop macro when stop button clicked

[YasserKhalil]

Hello everyone

I have a code with looping statements like that

Code: Select all

Sub Test()
    Dim i As Long
    
    For i = 1 To 10000
        'code
    Next i
End Sub

I have two buttons on the worksheet. One for the Start which is assigned to Test macro
and the other button is the Stop which I need to create a macro that stops the first macro if clicked
Is that possible? And if possible I need to know the value of the variable i to know where is the code stopped exactly

[HansV]

For example:

Code: Select all

Dim StopIt As Boolean
Dim LastI As Long

Sub Test()
    Dim i As Long
    StopIt = False
    For i = 1 To 10000
        Application.StatusBar = "Processing " & i
        DoEvents
        If StopIt Then
            LastI = i
            GoTo ExitHere
        End If
    Next i
ExitHere:
    Application.StatusBar = False
End Sub

Sub StopNow()
    StopIt = True
    MsgBox "Loop stopped at i = " & LastI
End Sub

[YasserKhalil]

Thanks a lot Mr. Hans
For the first time the code works and gives me 0 for the variable i and for the second time it gives me the previous i correctly. How can I get the correct i from the first time?

I have figured this point by moving the message box to the Test macro like that

Code: Select all

ExitHere:
    If stopIt Then MsgBox "Loop Stopped At " & lastI
    Application.StatusBar = False

[HansV]

Sorry about that. You can also do it like this:

Code: Select all

Sub Test()
    Dim i As Long
    StopIt = False
    For i = 1 To 10000
        Application.StatusBar = "Processing " & i
        DoEvents
        If StopIt Then
            LastI = i
            MsgBox "Loop stopped at i = " & LastI
            GoTo ExitHere
        End If
    Next i
ExitHere:
    Application.StatusBar = False
End Sub

[YasserKhalil]

Thanks a lot for your assistance my tutor.