VBA code for Production Tmesheet

[chris1986]

Hi,

I work for a printing company and on one of our presses, the operators manually write out time sheets to record their work. This in turn has to be typed up in Excel to do some analysis on performance etc. I am trying to change this so the operators input the information directly into Excel from their terminal. I would like to utilise some of the features that VBA allows to make input easier, and also automate the analysis of the input to produce KPI’s without manual intervention.

The problem is, I can’t write in VBA! I was hoping to call on the experience of this forum that has been extremely helpful for me previously!
My ideal solution would be as follows:

“Home page” there is an Excel worksheet simply with a button “Start Shift” by pressing this button, the user has an option to complete: “Operator” (ideally this option would be from a drop down list that has been specified) – once this has been selected there is pre-formatted layout:

Job No Time Started Time Finished Clicks % complete Comments


The operator would then fill out each row of data with the relevant information as they produce each job.

At the end of the shift, there would be a button on the Excel worksheet “Finish Shift”. When the operator would press this button, they would be prompted to enter the “machine reading”.

After doing this, the macro would cut all the data on the worksheet and paste it into separate Excel workbook on the network. The data would be in exactly the same format recorded above, however there would be 2 x additional columns: “operator” & “date”. The operator text string captured when pressing the “Start shift” button would be recorded & it would also need to record todays date and insert that into a separate column.

On the same workbook on a different worksheet, the “machine reading” figure would be inserted into one column and the date into an adjacent one.

The other workbook on the network would then have all the shifts recorded, and analysis would be carried out on that.

I hope someone can help with this – many thanks in advance!
​

 

[VoG]

That's a pretty enormous ask that might have been better split into smaller questions. Having said that, take a look at UserForms

Create an Excel UserForm

Excel UserForm With ComboBoxes

 

[dmt32]

As already mentioned - your requirement is rather large & what you are looking for is a custom end to end solution.
Excel can do what you want but would require extensive work to achieve required result.

In a previous life, I used to work in printing industry & we did it all on paper then - seems not a lot has changed.

To get you started - have a look at this site Production scheduling tutorial with Excel download for free

They have some sample workbooks you can download which may give you other ideas to solve your problem.

Dave

 

[chris1986]

Many thanks for your replies!

I guess I would have been more advised to split the project down into smaller tasks!

I have followed advice and created a user form that the operators will fill out. The operator will only see the “Production Log” worksheet with 2 x buttons to press:

1. Click here to record job – this will activate the user form for the operator to record job information
2. End shift – the operator will press this at the end of their shift.

What I would like to achieve now is to create a macro that will activate when the “end shift” button is pressed.

The purpose of this macro will be to:

• Prompt the user to enter the machine reading, this will be recorded along with today’s date and saved onto the next available row on “MachineReading” worksheet on a separate workbook on the network
• Cut all the data (excluding titles) from the “ClickData” worksheet, and paste it into the next available row on “ClickData” worksheet on the same separate workbook outlined above.

The data recorded from the user form on the "ClickData" worksheet is in the following format:

Operator

Date

Job Number

Time Started

Time Finished

Colour Clicks

B&W Clicks

<colgroup><col span="7" width="64"></colgroup><tbody>
</tbody>

This will allow the operators to enter their “timesheets” directly to the IgenProdLog workbook, this will then be compiled on a separate workbook where fancy graphs and statistics can be set up to give KPI’s.

Many thanks for your help so far – I hope someone can help with this macro!

Chris