Hi Phin.........
Sorry, I really can't send the file, as it contains sensitive information to
the Client, and stripping the sensitive material out would render the program
just a useless shell. But, I can discuss any aspect of the programming, as
it's all conventional stuff. The program opens to a sheet called the
MainMenu. It has a few "housekeeping" buttons to Save, SaveAndExit, Archive,
Help, etc etc. Then it has an array of 42 buttons arranged alphabetically.
Each of these buttons activates a small macro to unhide and "goto" the
respective sheet.
Once on a selected matrix sheet, the user may edit the tasks and/or employee
names. Then the user presses an "Update" button which runs a macro to go to
the database and retireve all relative info and fill in the matrix. The same
macro also puts the current date permanently in a "Last Revised" cell, so the
information on the matrix is married to a specific point in time. Normal,
fixed formulas count the number of qualified entries for each task and enters
this in a header row, which is then summarized in a "Percent 3-deep" cell for
the entire sheet. This capability can also be turned off and on for each
task by the user so certain tasks are not considered in this tally. There is
also a "ReturnToMainMenu" button there which will clear the matrix field,
hide the sheet, and return the user to the MainMenu.
Also included in the workbook is the MasterDatabase which lists each
employee, and all relevant information about their training for an individual
task. Each employee is listed on a separate entry for each task they might
be trained for. Employees sometimes qualify for tasks under different
departments....no matter to the database. Employees sometimes leave the
company and return later, no matter to the database. There name is removed
from a matrix when they leave and the name and data stays in the database.
When they return and their name is added to a matrix, their information is
automatically called up.
There is also a custom "Help" sheet which defines the overall basic
operation of the file, and explains in detail how each button works. And, a
push-button feature that allows Archiving of the entire file to a separate
directory and appends the filename with a date/time. There is a "Sandbox"
button on the MainMenu that will create a separate file, exactly like the
real one, except that it is named differently, does not have Save or Archive
buttons, and is totally divorced from the "real" file. The user is cancelled
out of the real file and put in this one at the push of the button....no
returning allowed. This Sandbox allows new users to become familiar with the
operation of the file without danger of hurting anything. A new "Sandbox" is
created with all current information, each time the button is pressed.
That's it in a nutshell. If you are interested in any particular feature,
just ask back.
Vaya con Dios,
Chuck, CABGx3
Phin Doyle said:
Mucho Gusto Chuck,
Thanks for taking an interest, as you might of gussed ive been learning this
stuff as I go, and although Ive been doing Ok, some guidance would be welcome
recieved.
I have been thinking of transposing my Employee/ Tasks to permit longevity,
as employees well outnumber the tasks. Although I will probably stick with
color coding, I am intrigued by the navigation and operation of you database
- and the use of a "Date Stamp".
Would there be any way I could have a look at an example of this?
Gracias por su socorro!
:
I see Bob is pretty well taking care of your questions, but I thought I would
give you another perspective. I made a similar "Training Matrix" thing for a
client, but didn't use color because they wanted to make copies and not have
to use a color copier. Their Matrix differs in that the Tasks are across the
top and the Employee Names down the side. INstead of dates on the matrix, we
use blank for "no training", O for "In Training", 1 for "Trained", and 2 for
"Qualified to Train others in this task".....each could have an "x" following
it which indicates that the training received has passed it's "expiration
date". There are 42 of these sheets, (all in the same workbook), one for
each Department/Shift. All the data is kept in a master database on one
sheet and transferred to the Sheet of interest at the time of interrogation,
otherwise, all sheets are kept hidden. This is all controlled off a master
Menu sheet with buttons. The working field of each sheet is automatically
cleared upon Sheet De-activation, while the names and tasks are
retained......so each time each sheet is brought up, it contains the most
recent data on the database, along with a DateStamp. Each sheet also
contains information to show if each task has at least 3 people trained in
that area. The file is about 9meg in size and contains approx 15,000 rows in
the master database.
This program has been working for about 4 years, through several upgrades.
It is maintained by an employee with normal Excel skills and no VBA required
on her part.. I thought you might be interested to see how someone else did
it.
Vaya con Dios,
Chuck, CABGx3
:
I have been dwelling on this for some time to no avail; any suggestions would
be gratefully received. Also, please forgive the sparse use of 'technical
terms'.
I have a large spreadsheet that, when finished, will be used as the main
data dump of training status for employees. Along the top (F:EO) is the list
of employee names, and down the side (4:67) is a list of all procedures
needed. The middle section is the corresponding date that a procedure was
trained to an employee along with the appropriate colour: Green +Date =
training valid; Yellow + Date = Training Incomplete/Superseded; or Red +
Blank [no date] = Training Required. All other non corresponding cells are
Grey.
Each Procedure has a 'Revision Number', and when the number is updated (only
ever upwards, i.e. 1 then 2 then 3) all Training Date cells [non-blank??] on
that row should be changed to yellow.
What is the best way of automating this? If buttons, would you suggest a
command button for each procedure or a drop- down list??
Thanks in advance for any help,
Phin
