_______ VBA Project _______
In an attempt the help the shop, I decided to try to make my own setup so that we could
do away with all the paperwork on the shop floor. Some programs were over 70 pages,
most were over 20 and they were all out of date 20 minutes after they were printed.
It started as a way to replace the paper read out for all the programs, but once I started adding functions I couldn't stop. I started with the issues that were most common across the shop, then I started adding all the things that were on the 'wish list' for management.
I tend to be most productive and have the most fun while troubleshooting issues. I ended up having an amazingly fun and fulfilling experience creating this program. Not only was the entire thing a 'troubleshooting' product in itself, but all of the daily battles that were fought and won writing the code, figuring it out and learning VBA to create this were fun.
It started when I was gifted un-usable excel files that 'IT' had been attempting to fix
for 3+ years at that point. For the main list that held all information about our programs, there was an excel file for each machine. For some reason, still un-known to this date -
the columns were not used for this list. I ended up fixing the data and creating a
Master Program List that held all the data for each program, that shop management
would need on a daily basis. The lists started like below:
Once I fixed the information and combined all of the individual "per machine" files
into one Main Master List, I tried to standardize the data and that is when
the logic was needed and started being implemented with the list. To keep everything spelled the same and correctly (so that a filtered search was possible without the need of selecting multiple different spellings and details) Drop-Down lists were introduced.
Examples of the 'simple' logic added to the Master List:
- Automatic 'Program' Cell Highlighting, based on 'Machine Type' Cell
- Multi-Part Program: Auto Check and Highlight if x1 Program does Multiple Parts.
- Fixture Work Needed: Auto highlight cell is "X" in cell for Fixture Work Needed.
- Program Status Drop Down List Added:
- Current: Basic Bold text for Ready to Run/Proven Programs.
- Need Update: Auto Color Red text if Program needs update.
- Need New: Auto highlight cell Red if New Program needed.
- Obsolete: Auto grey out and strike-through text in cell.
- Order of Operations Drop Down List Added.
- Auto Hyperlinks for Program, Toolsheet and Setups per Program Number.
Once I had all the above information, a 2-step User Form was created.
Before the first form opens, it will fill the combo box with
all the part numbers from the Master Program List:
Once a part number is selected, it will setup and show the operations/programs.
This will gather and alert the operator to important information about the part:
- If "Fixture Work Needed" for program, will show "Fixture Issues"
(where they can click to see all notes: what needs to be fixed/etc)
- If Program is "NEED New" or "NEED Update", it will show "Program Issues"
(it will lock out the "Display Program" button and send them to management.)
Once the operator has selected the program to be run,
by clicking on "Display Program"
- All excel/non-used files will be closed,
- The program and toolsheet files for the job will open and
will automatically setup the screen (see below)
- Files will open in Word, for the "Track Changes" abilities
along with "Accept/Reject" Changes abilities for management.
(A)
(B)
(C)
(D)
(E)
BOX (A) = Lists all info of program number currently being run.
Job Number, Part Number, Operation Description, Program Number, Operator
BOX (B) = 'Clock' Buttons for detailed job run times, history and troubleshooting.
'Job Setup' is clicked when operator first starts setting up job.
When "PART START" is clicked for part #1, the time between Setup to 'Part Start' is
entered under "Setup Time" so that the time is not applied to the production times.
'Tooling' is clicked when they have an issue during the run. For example:
While running part #3, they break tool #2. When 'Tooling' is clicked, the "Current Time"
counter is paused and a text box pops up asking operator "What happened?"
Once the operator enters the info: Tool #, Where in Program, Etc it is saved into:
- The Main report for the job, documents the issue and the time spent on tooling.
- Also goes to a "Tooling Issue" Report, where the Job Number, Part Number, Pgm Number, Operator Name, Date, Time, Workstation and Issue are added.
'Program' & 'Other Issue' Buttons work the same way. When Clicked, a text box will
pop up and ask the operator 'What happened?" The info will be stored in the reports.
The '@' Buttons: Are setup to once again have a text box pop up and ask the operator
for information on what happened, or what the issue is. However these buttons are
setup for sending emails to the correct departments/people. It will automatically
pull all the Job info and add it to the email. (Job, Part, Pgm, Name, Date, Issue, Etc)
The only thing the operator has to do is enter the issue, everything else is automatic.