Halt or Pause a Macro

[Guest]

I need to stop and allow the user to look over and make any necessary changes
to a workbook before my macro opens all the other workbooks and updates their
values. Is there a Pause or Halt process in VBA with Excel 2003

 

[Guest]

You are not really looking to halt or pause a running macro, so much as you
want to end a macro and allow the user to do whetever stuff they need to do
before you proceed to the next macro. Not knowing your circumstances it is
hard to say just how you want to trigger the next macro. I usually just use a
button (which I may keep disabled until the user has performed some sort of a
required action which will enable the button).

 

[Guest]

How does the button work? I can't just prompt the user if they need to make
changes they usually know in advance if changes are going to need to be made.
Could the button end the Macro and than step back into the Macro at the same
place or do I just make a toolbar button and assign a macro to it? I already
have the choice of two different macros depending on which type of sheet they
are updating from now it would turn into four and I would have to do this for
each day of the week adding another 14 macros which I already am having
difficulty with because I don't know how to do a relative file location code
to follow the file the macro resides in (see my post "Change a Macro commands
reference to file locations")

 

[Guest]

Lets take a step back. What is this macro going to do? Will the modifications
made by the user change the flow of the macros?

Perhaps a high level discussion of what this spreadsheet does is in order.
What is the flow. When does the user need to get involved and for what
reason. Maybe if we redisign the process a bit we can make this entire
project a lot easier.

 

[Guest]

OK! There are five steps in the process (payroll). A spreadsheet is
generated by our customer containing all of our jobs for the day each job has
13 columns of data including our technicians number that completed the job
and three columns of billing codes. The originals and two columns of audited
codes one column that subtracts and one that adds. My workbook has a master
sheet and a tab for each of my techs which uses an offset formula to pull
every job for that tech. The first step of the Macro imports all of the
information from the e-mail into the Master sheet and runs a text to columns
process to break every code into its own column and assigns a dollar value to
it. At this point I need a break so if there are any tech number changes
they can take place before the next step. Only tech number changes are made
because often times we are forced to close a job under a borrowed tech number
due to the wait time for a new tech to be processed into the customers
system. We change them so the right person gets paid. Once the changes are
made I need the macro to continue. It than opens only the individual tech
files that had jobs that day by comparing to our tech column on the Master
sheet. The tech files consist of sheets sunday thru saturday and a weekly
totals page that compiles quantities of the codes drawn from the daily sheets
which include an address and account number for each job and a grid that
counts the number of codes charged for each job. This page is layed out in a
format required by the customer and is printed and turned in with a copy of
the techs workorders for auditing purposes. The macro opens, updates, closes
and saves it. The third step is the weekly payroll workbook. This workbook
has a linked copy of each weekly sheet from the individual tech file and is
also the main workbook that updates every tech file with current line items,
pricing and date ranges based on a week number entered on the invoice
page.There are also three tally sheets one for weekly totals one for invoice
totals and an Employee sheet where we add other payroll items and office
personell, this sheet is than e-mailed to corporate for payroll processing.
This sheet is our main source of record keeping for every project. We have
several projects with this basic setup with many similarities and also many
differences based on the type of billing system our customer uses. Some
systems we use this to bill the customer with and other systems like the one
I'm working on now use a reverse billing system where the information is
submitted to us and we are required to parse it and submit a discrepency
report. Which brings me to my fourth step which I have not started on. I
have to use a weekly report imported from our customers website. I will use
a column from that import to search the daily master sheets from the first
step and verify that all codes were paid. The step that is missing is the
first step which uses the same macro with a couple of small changes made so
it imports from the original pre -audited code sheet and dumps the
information into the tech files and prints that day for each tech that worked
so it can be turned in with the workorders for auditing purposes. When I run
the second macro called the final it delets all the data and replaces it with
the audited data for final payroll. I could almost use the same Macro but the
customer changes the column order when they do the final and I don't want to
leave it up to my office people to reorder the columns its just easier to use
the second macro. If I ended the macro after the first step I could use the
last half on the pre-audited or final with no changes.- Hope this helps. I
really need some guidance on this one.

 

[Guest]

If you are still monitoring this thread, then here is my take on things. The
big issue is does the user need to modify the techs becuase the data includes
a tech that does not exist in some sort of a master list. If it was me I
would be inclined to update a mater list of Techs prior to running this macro
of yours. When the macro is running have a piece of code that validates the
techs from the master file against the techs in the source data. If
everything matches then call a sub to move on to the next steps in the
process and don't bother the user since everything is OK. If there is any
kind of a discrepancy then have the macro exit with a message letting the
user know that there is a problem and list the tech(s) that do not match. At
this point all of the macro execution ceases and a button becomes visible on
the sheet which will allow the user to re-enter the exectuion of the macro.
Now the user has control and there is the opportunity to fix up whatever
needs to be fixed. The button re-validates the techs aginst the master list
and calls the sub to continue the process of the data. If this does not make
sense then e-mail me directly and I will try to mock something up for you to
demonstrate what I mean.