need VBA software "breakpoint"

[mattrix]

Hi,
I have a VBA LOOP that processes a sheet and writes a result.
Unfortunately there seem to be more exceptions than rules.
At the moment I've got a breakpoint at the end of the loop which works but requires lots of clicks per loop:

* The break-point takes me to the VBA window
* then I go to the excel window
* check result and make any changes required
* go back to the VBA window
* F5 to continue


what i'd like is to avoid the VBA window altogether, use VBA to pause execution at the end of each loop, so that I can see the result on the sheet and press a key on the keyboard to loop and calculate the next result; and still be able to make changes to the sheet if necessary.

Any ideas on how I might do this?

[mikerickson]

something like
Do
' your code
If MsgBox("next?", vbYesNo) = vbNo Then Exit Do
Loop until DoneYou may want to exit more than the do loop, since you won't be able to edit the worksheet.

[mattrix]

Thanks mikerickson,
Unfortunately I need to scroll &/or make changes to the sheet between loops

[CodeNinja]

Mattrix,
Using Mickerson's code, you create a command button to re-activate the macro...

Dim bbool As Boolean

Sub test()
Dim i As Integer
For i = 1 To 20
bbool = False
MsgBox (i)
If MsgBox("next?", vbYesNo) = vbNo Then
Do Until bbool = True
DoEvents
Loop
End If
Next i


End Sub

Sub buttonClick()
bbool = True
End Sub

[mikerickson]

Another approach would be to keep track of where you are in your loop.
This uses Names, but a discreet cell or a document property or a couple of different ways could be contrived.


Dim i As Long

i = Evaluate(ThisWorkbook.Names("loopCount")

Do
' your code
i = i + 1
If MsgBox ("pause", vbYesNo)= vbYes Then
ThisWorkbook.Names.Add name:="loopCount", refersTo:= "=" & i
Exit Sub
End If
Loop Until i = 1000
ThisWorkbook.Names.Add name:="loopCount", refersTo:= "=0"

[Paul_Hossler]

You could use a non-modal userform

Paul

[mattrix]

Hi guys,
Thanks so much for the ideas.
I ought to be able to get something working.
A few things I didn't know / hadn't thought of ... we are always learning!
mattrix