Pausing Macro

[Dohko]

Hi

Is there a way to pause a macro while it is running?
For example:

COL A COL B
1 2
b 4
5 5

My current macro highlights all characters in this case b.
Is there a way to make it stop when it highlights b, and make it continue running when I want? I'm using a Form for this purpose.

Thanks

[Leith Ross]

Hello Dohko,

Place a MsgBox in your macro code at the point it finishes highlighting the data.

Please Login or Register  to view this content.

Sincerely,
Leith Ross

[Dohko]

Yes, I tried that. But I need to play with the data before I can continue running the program.
MsgBox won't let me touch the spreadsheet unless I click yes or no. or close it.

[dominicb]

Good afternoon Dohko

So why not split your code into two macros?

HTH

DominicB

[Dohko]

Actually it's 2 macros.
The actual situation is this.
I have a macro that colors cells that have errors.
I want to be able to modify them and then continue running the macro.
Like for example I fixed one cell and then just press continue or something like that

[bhofsetz]

There are several things you could do depending on the complexity of your macro code.

-When the error is found you could have the code open an input box which
would prompt for the correct information then overwrite the errenous cell
with the correction. The code would be 'paused' until a value is entered into
the input box. This would also allow you to check the value entered into the
input box before continuing.

-You could open a modeless userform which alerts the user to the errenous
cell and will allow them to interact with the spreadsheet while the userform is
open. Then when the correction is made they can click 'Continue' or
whatever button you choose on the userform.

-If your code and data being checked is relatively small you could highlight
the errenous cell and simply have the code end. Then recheck all data from
the beginning with the new correction in place and if your code or data isn't
extreemly large then the user won't see any significant difference in
execution time.

HTH

[Dohko]

I tried the Modeless form, I created a userform and added a continue button, however the whole macro keeps running with the button sitting in the background.

Ending the code is not that practical since I'm dealing with 250 columns and 64000 rows.

if there was a way for the form to popup and stop the macro. Let me edit and continue when I click the button, that would work

any more ideas?

[mikerickson]

How about a static variable that will let the routine know if it is being called from scratch or is resuming its error check?

Or you could create a UserForm that will allow you to edit via Userform rather than directly to the sheet.

[bhofsetz]

For the modeless userform assign a public variable in your regular module such as CB1Clicked as Boolean

Have your 'Continue' or 'OK' button on the userform reassign the variable to true.

Then before calling the userform make sure that the variable is set to False
Show the userform modeless
Follow the show.userform line with a do loop that continues until the variable is true.

i.e.
in a regular module place the following code.

Please Login or Register  to view this content.

In the code for UserForm1 with one CommandButton place the following code

Please Login or Register  to view this content.

You could also set the value for CB1Clicked in the initialize statement for the userform.

Alternatively an InputBox might be a simpler way to go and give you more control over what is being changed on the sheet while your code is running.

HTH

[Dohko]

The button worked, however the program stops running once i press continue.

[bhofsetz]

What is your next line after:

loop

?