Pause VB Code to Allow User Input on Worksheet

[Jana]

I have some extensive code that is formatting data in an excel
spreadsheet for import into another program. There are times when we
will use a default value for a field called "Collectability Code", but
sometimes the user will want to manually populate that column. If
we're using a default, my code can insert the appropriate code without
any input from the user other than indicating whether they want to
input them manually. So, my question is this: How can I pause
execution of the code to allow for manual entry in a certain column,
then resume when the user is ready and I've validated the data?
Furthermore, if the data they enter is invalid (or missing), I'll need
to give the user the opportunity to correct their entries before
finalizing the data for import (basically loop through any invalid/
missing records until it passes the validation process).

Thanks in advance for any assistance,
Jana

 

[Tom Ogilvy]

There is no command for this.

It is usually suggested that you break the macro into two pieces and end the
first macro, then utilize some type of event code to detect when to start the
second.

 

[Jana]

I have some extensive code that is formatting data in an excel
spreadsheet for import into another program. There are times when we
will use a default value for a field called "Collectability Code", but
sometimes the user will want to manually populate that column. If
we're using a default, my code can insert the appropriate code without
any input from the user other than indicating whether they want to
input them manually. So, my question is this: How can I pause
execution of the code to allow for manual entry in a certain column,
then resume when the user is ready and I've validated the data?
Furthermore, if the data they enter is invalid (or missing), I'll need
to give the user the opportunity to correct their entries before
finalizing the data for import (basically loop through any invalid/
missing records until it passes the validation process).

Thanks in advance for any assistance,
Jana

Another thought:

My original files always have 20 columns of data. Am I better off to
instruct users that, if they want to manually input, to put the data
in the last column, and then check to see if the file has an extra
column? This way, I could just validate their data entry and exit the
macro if any manually entered data fails the validation process.

Still trying to wrap my brain around the best solution...

Thanks,
Jana

 

[Tom Ogilvy]

Since your into workarounds, almost anything that gets the job done is a
candidate.

Just another example.
You could create a user interface using a userform and prompt the user for
input - controlling the whole process outside the worksheet - when the input
from the user is typed into the userform, validate it an update the sheet
with your code - that would avoid the need to pause the macro.

 

[Jana]

Since your into workarounds, almost anything that gets the job done is a
candidate.

Just another example.
You could create a user interface using a userform and prompt the user for
input - controlling the whole process outside the worksheet - when the input
from the user is typed into the userform, validate it an update the sheet
with your code - that would avoid the need to pause the macro.

--
Regards,
Tom Ogilvy









- Show quoted text -

Tom:

Thanks for the input, just needed to think outside the tiny box I
tossed myself into!

Jana