Stoping and resuming a running macro

[radubalmus]

i am trying to have a brake in the running of the code...
while a cycle is running(ex: For......Next), before incrementation i want to stop and to see what has been happening in the exel sheet, eventualy do some hand modifying, and then to resume the code...

i was thinking with a userform, and when i click i button to resume, but i am having problems with the modal property: if it is modal i can't modify, and if i set it vbModeless i doesn't stop...

 

[andrew299]

click on the VBA box on the windows toolbar can't think what it is called but I mean the toolbar with the start button. Then press escape. This should pause the code so that you can see what you have done.
Andrew

It may have hit every branch on its way out of the ugly tree, but hey! It works. (but don't quote me on that)

 

[radubalmus]

i don't want to work with toolbars, i want to do it automated because it is a big cycle (about 1000 cycles), and the most cycles will be corect.
so i just want to push a button "ok" = move to next cycle!
thats why i was thinking that just before a new cycle to show a userform interface with a button and just to push ok if i don't have to modify anything


There are simple solutions for almost evrery problem! The hard part is to see them

 

[radubalmus]

'searching for word files
With fs
.NewSearch
.LookIn = "C:\Documents and Settings\......\Desktop\PCR\test"
.FileType = msoFileTypeWordDocuments
.Execute
For i = 1 To .FoundFiles.Count

Set objApp = GetObject(, "Word.Application")
Set objApp = CreateObject("Word.Application")
With objApp
.Visible = True
.Documents.Open CStr(fs.FoundFiles(i))
End With

With objApp
.Selection.WholeStory
.Selection.Copy
.DisplayAlerts = False
End With

ThisWorkbook.Worksheets("Sheet1").Activate
ThisWorkbook.Worksheets("Sheet1").Range("A1").Select
ThisWorkbook.Worksheets("Sheet1").Rows("1:300").Select
Selection.Delete Shift:=xlUp
ThisWorkbook.Worksheets("Sheet1").Range("A1").Select
ActiveSheet.Paste
Call Module3.ClearClipboard 'empty clipboard
objApp.Quit
Call Module1.exportTests 'transforming data form word into usefull data

UserForm1.Show
??????

Next i

So there i want to stop and seee what i have done, then modify if needed, and then push a button and resume procedure



There are simple solutions for almost every problem! The hard part is to see them!!!!

 

[GlennUK]

Why don't you just set a break-point in your code?

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[WalkerEvans]

"it is a big cycle (about 1000 cycles), and the most cycles will be corect"

If this is the case, why do you want to call the Userform every single time you go through the loop? Glenn's solution is the simplest way to do this, but if you want more control you might consider another interior loop to halt execution at some fraction of .FoundfFiles.Count.

 

[radubalmus]

exacly i would like more control....
and i don't have a clue how to halt the execution and than resume at the same point without using the breakmode(toggling from VB editor to excel everytime)
(

There are simple solutions for almost every problem! The hard part is to see them!!!!

 

[GlennUK]

Hi radubalmus,

don't re-invent the wheel, you have tons of control of when you want the code to break by using Watch debug events. It's a small price to pay to have to toggle between VB editor to Excel, especially if you have then in windows side by side ( or one above the other ).

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[radubalmus]

)))
thanks glenn,
you have a point....
but he there is even the smallest chance to make my work more easy i'll lock fort it and grab it

There are simple solutions for almost every problem! The hard part is to see them!!!!