Pausing and continuing a macro

[Chris Watts]

I have written a number of vba macros to check data, in bulk, entered into
an Excel 2007 spreadsheet. Typically each one will come up with several
errors and report them using a MsgBox. I would like to interupt the macro
as each error is detected (ideally with the MsgBox still on screen), allow
the user to correct it and then continue. The changes that the macros
report may be in the same or a different sheet or workbook so the user must
be able to navigate around them before continuing the macro. For example a
data code could have been mistyped or a new entry might need adding to the
master list - the user must decide.

Any suggestions as to how to achieve this would be welcom.

TIA
Chris

 

[Pete_UK]

Your description is a bit too general to offer specific solutions. If
you apply your macros in batch mode, i.e. enter the data and then run
the macros to check the data, then you might think about an approach
which reports the errors in a new sheet as a log rather than using a
MsgBox for each one. The User can then work through the log making the
necessary corrections and then the macros can be re-run. If it is more
dynamic than this, then your macro can apply data validation rules and
instead of just a MsgBox you can enter a loop where the User can input
an alternative item of data until it is valid. If your macro detects
that a data item is new, then it could ask the User if this needs to
be added to the master list and if so it takes the User through some
process to add that new item.

Hope this helps.

Pete

 

[Chris Watts]

Thanks, Pete. A selection of very useful thoughts.
cheers
Chris

Your description is a bit too general to offer specific solutions. If
you apply your macros in batch mode, i.e. enter the data and then run
the macros to check the data, then you might think about an approach
which reports the errors in a new sheet as a log rather than using a
MsgBox for each one. The User can then work through the log making the
necessary corrections and then the macros can be re-run. If it is more
dynamic than this, then your macro can apply data validation rules and
instead of just a MsgBox you can enter a loop where the User can input
an alternative item of data until it is valid. If your macro detects
that a data item is new, then it could ask the User if this needs to
be added to the master list and if so it takes the User through some
process to add that new item.

Hope this helps.

Pete

 

[Pete_UK]

You're welcome, Chris - thanks for feeding back.

Pete