Ending loop in a clean way (XL)

[Mikeb1970]

Hi all,

I have a problem that i've been working on for quite some time. It would be to complicated to explain it in full, therefore just an example of what i want. I have too visualize data in a loop, with a timer, slowing it down for me to be able to get a chance to view it. If i see something intresting (mostly see strange things) i should be able to end the loop using a commandbutton, so i can look at that time frame more closely. I have created an example of something that should give anyone of you a chance to explain it to me.
Please remember, i'm pretty new at VBA, used to solve all big problems just using excel and patience.

I created a commandbutton on my worksheet, called commandbutton1 with the next code.

Private Sub CommandButton1_Click()
Load UserForm1
UserForm1.Show
End Sub

then i have two commandbuttons on userform1
one named Start and one named Cancel

Code for startbutton

Private Sub Start_Click()
For a = 1 To 10000
Range("A" & a) = a
Range("A" & a).Select
Next a
Range("A1&quot.Select
End Sub

This will take a few seconds to run, now my question is, what code should i use for the cancelbutton. To stop the code from running, if it needs any special event handling , i would appreciate it when one would include this in the answer.

Any help is greatly appreciated

With Regards

Mike

 

[tbl]

Easiest solution is just to press escape. this stops all code execution.

Richard

 

[Mikeb1970]

Hi tbl,

That is just what i am trying to avoid, because a debug window opens up, which doesn't look very proffesianel imho.

Mike

 

[SkipVought]

Hi,

Try using the DoEvents command. Then your STOP button can set a GLOBAL variable to come value...

Public bGO as Boolean
Private Sub Start_Click()
bGo = TRUE
a=1
Do While bGO
  With Cells(a, 1)
    .Value = a
    .Select
  End With
  DoEvents
  a = a + 1
Loop
Range("A1").Select
End Sub
Private STOP_Click()
  bGO = False
End Sub

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Mikeb1970]

Hi Skip,

This works great, just what i needed.

Thnx Allot

Mike