Button for stopping macro?

Topic

New Reply

I have just the most rudimentary understanding of VBA, and any macros I create are done using the Macro Recorder in Excel, so keeping that in mind…..

I made up a little randomizing/sorting routine in Excel, and I added a button to the sheet to cause that macro to run. I have DO loops in there so that the sort will repeat itself for quite a while. I’d like to add a button to interrupt the macro. I can hit the ESC to do that, but that brings up the “RunTime error 1004” window. I can close that window, and all is well, but I’d like to add a button to the sheet and have that just interrupt/end the macro more cleanly.

Can someone give me a tip on this?

Thanks,
Denis

Reply | Quote

Replies

What’s the actual code?

Essentially, you will need to add a DoEvents line inside your loop and add a check for a Boolean variable value to the loop part. The code for the other button will then just need to set the Boolean to True. Roughly:

Code:

Dim blnStop As Boolean
Sub StartRandomize()
    Dim n As Long
    blnStop = False
    Do
      DoEvents
        Range("A1").Value = n
        n = n + 1
      ' your code here
    Loop While Not blnStop And n < 100000
End Sub
Sub StopLooping()
    blnStop = True
End Sub

Reply | Quote

Here’s my code. The macro is named “Pick12”, and there’s a button form on the sheet associated with that name to run it. I’d like to insert something into this – I assume it would go between the “End With” and “Next n” lines, near the end – that would cause the thing to stop running, and (I assume) just jump straight to the “End Sub” line.

Thanks for the help
Denis

– – – – – – –

Sub Pick12()
‘
‘ Pick12 Macro
‘
‘ Keyboard Shortcut: Ctrl+a
‘
For n = 1 To 1000000
Range(“e3:F14”).Select
ActiveWorkbook.Worksheets(“Sheet1”).Sort.SortFields.Clear
ActiveWorkbook.Worksheets(“Sheet1”).Sort.SortFields.Add Key:=Range(“e3:e14”) _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets(“Sheet1”).Sort
.SetRange Range(“e2:F14”)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Next n
Range(“G1”).Select
End Sub

Reply | Quote

Much as I suggested then:

Code:

Dim blnStop as Boolean

Sub Pick12()
'
' Pick12 Macro
'
' Keyboard Shortcut: Ctrl+a
'

blnStop = False
For n = 1 To 1000000

doevents

if blnStop then Exit for
Range("e3:F14").Select
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("e3:e14") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("e2:F14")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Next n
Range("G1").Select
End Sub


Sub StopLooping()
    blnStop = True
End Sub

Reply | Quote

Almost there – I added the lines to the existing code. Now, how do I make it actually work? I added a new button, and associated it with the “stoplooping” sub, but it doesn’t do anything – when one of the “Sort” subs are I get the hourglass, and I’m unable to click on any other buttons. What am I missing?

Thanks so much for the help

Denis

Reply | Quote

Can you post your actual workbook?

Reply | Quote

Sure. As I’m sure you’ll figure out, this is a little workbook designed to take 12 names, and randomize them, one at a time. The “12th Pick” button selects all 12 names, randomizes them, and then sorts (repeatedly). The “11th Pick” button does the same, but just for the first 11 names. the “10th pick” button selects the first 10 names.

I want this to work so that it does that in controllable stages. I could, I suppose, arrange it so that it just does that all from one button, without stopping. But I’m making a big production of this for the people involved, and I want to be able to push each button, have it run for a while, and then manually interrupt.

Thanks for your interest.

Denis

Reply | Quote

You didn’t implement my code. You have to have:

Code:

Dim blnStop as Boolean

at the top of the module. Then inside each loop, you need to have:

Code:

doevents

if blnStop then Exit Sub

Reply | Quote

Oops – missed that part.

Works like a charm now – thank you so much for the help!

Denis

Reply | Quote