Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help finding a "batch processing" technique for a userform???
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help finding a "batch processing" technique for a userform???
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. -- Regards, Tom Ogilvy "WillRn" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
Delete "Unused names" in a batch | Excel Discussion (Misc queries) | |||
Batch Job "Text to column" in excel | Excel Discussion (Misc queries) | |||
Batch Job "Text to column" in excel | Excel Discussion (Misc queries) | |||
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel |