Exit a loop with a button click

[SQUIDD]

Hi All

So basically, i was wondering if i could exit a loop if i made a command button.

I want to call this within my other code.
But wish to click a command button if possible to end it.

Sub nagg()
    For I = 1 To 100
        Call Application.Speech.Speak("Attention required")
        Application.Wait (Now + TimeValue("00:00:30"))
    Next I
End Sub

cheers

dave

 

[DILIPandey]

Hi Dave,

What if you press break key (break or ctrl + Break) or escape key ?


Regards,
DILIPandey

 

[dmt32]

Using Wait or Sleep locks you out until the time period has finished. You can use CTRL+BREAK to interrupt the macro, but Excel will not accept input from the keyboard or mouse whilst paused using either method. If users are not aware of this feature, it can look like your Excel code has hung.
You can use a loop instead where you can pause or exit VBA code

Give following a try & see if any help to you:

Place all code in your worksheet (or Forms) Code Page

Dim StopIt As Boolean

Private Sub CommandButton1_Click()
StopIt = True
End Sub



Sub nagg()
    Dim RunningTime As Double
    Dim TimerStart As Double
    
    TimerStart = Timer
    
    Do Until StopIt
        RunningTime = (Timer - TimerStart) / 24 / 60 / 60
        If (Format(RunningTime, "ss") Mod 30) = 0 Then Application.Speech.Speak ("Attention required")
        DoEvents
    Loop
    StopIt = False
End Sub

Code should keep looping until you press your button & give speech message every 30 seconds.

Note the StopIt variable – this MUST sit at the very TOP of the code page OUTSIDE any procedure.

Hope Helpful

Dave

 

[SQUIDD]

Hi DILIPandey

I was unaware that i could use CTRL break, i knew about escape, is there any difference between them?
Thnaks for your help

DMT32
Thats awsome mate, the code works great, i will get this intergrated into my project.
Thankyou very much for taking the time to help me.

Really appriciate both of your help.

Dave

 

[DILIPandey]

Great..!! Happy to help.


Regards,
DILIPandey