Help finding a "batch processing" technique for a userform???

[Guest]

I have a delimma on a complex userform that I have developed for my nurse
managers. I will explain . . .

We must monitor several compliance issues on a weekly basis. I have
developed a userform that allows my managers to input their information into
a spreadsheet. Each time we are not in compliance, another userform gathers
followup information that populates another spreadsheet. So far it works well.

My problem comes in when the nurse manager must try to do his/her followup
with employees that were not in compliance on a certain issue. Usually, this
is just a verbal reminder to "remember to complete such-n-such form, etc.

The problem occurs in two ways:

First, the manager is a busy person and can't always do these followups as
they occur. Although, I have developed a userform that can go into the
spreadsheet and pull a specific incident and update it, often there are
multiple incidents by a given nurse in a particular time period. It then
becomes very cumbersome for the manager to use the existing routine
(userform) to post what follow up she did because of the issue. Did he/she
get all of the incidents that occured involving a given employee??

Second, they must enter this information event by event onto the
spreadsheet. This also, with multiple occurances can be time consuming.

Is there a way that my existing form can be used to "batch process" each
employee by name (or some ID number if necessary). I envision a macro that
could search the spreadsheet for an employee, and one by one, rotate through
each occurance enabling the manager to indicate what followup they did. It
would be helpful if the screen could default in the type of followup done
(but I think that I can handle this part).

The macro would look for any occurance that had a blank "Follow Up Complete
Date" cell for a given employee. I am aware that I will need a unique
identifier for each employee to pul this off.

It would be nice to be able to modify the existing form (which searches by a
unique indentifier number for each incident) to search for all incidents with
a particular employee/nurse until all incidents have a "Follow Up complete"
date entered.

I know that this task is probably better suited to Access, but we can't
afford the upgrade to get all of the managers to have Access on their PCs. So
I am stuck with Excel for now.

Keep in mind, I am a nurse that his taught himself VB and can have trouble
with some of the more "abstract" procedures of writing macros . . . : )

At any rate, any suggestions or help is greatly appreciated.

Sincerely,

Will

 

[Tom Ogilvy]

Most of what you ask is dependent on your own implementation.

Your key question is to find multiple records for a specified critieria.

This can be done using autofilter or advanced filter under the filter option
of the Data menu. this can of course be done with code as well. You can
apply the filter (probably autofilter would be the easiest) manually with
the macro recorder on to see the code. It is then a matter of looping
through the visible records which can be done using specialcells(xlvisible).

an alterntive is to use the Find/Findnext methods of the range object.
Sample code for finding mutliple records is given in the VBA help example of
the FindNext method. You would search for each occurance of the incident
and when found, then process any that also have the specified nurse as the
responsible party.

If you need sample code, post back. (or search this newsgroup in google
groups for autofilter or FindNext and Ogilvy as author.