Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    Dec 2009
    Location
    Pittsburgh, PA, USA
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    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




    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    New Lounger
    Join Date
    Dec 2009
    Location
    Pittsburgh, PA, USA
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.SortField s.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortField s.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

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    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
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    New Lounger
    Join Date
    Dec 2009
    Location
    Pittsburgh, PA, USA
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Can you post your actual workbook?
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    New Lounger
    Join Date
    Dec 2009
    Location
    Pittsburgh, PA, USA
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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
    Attached Files Attached Files

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    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

    Regards,
    Rory

    Microsoft MVP - Excel

  9. #9
    New Lounger
    Join Date
    Dec 2009
    Location
    Pittsburgh, PA, USA
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Oops - missed that part.

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

    Denis

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •