Date and time stamping with a (macro) button

[ArcticWolf]

Hi,

I have a piece of machinery that breaks down (as they do). I want my
production manager to start recording the occurances of these break downs.
He is not PC literate and want him to register these by just clicking a
button - simple.

In sheet 1, I want 9 (macro?) buttons each of which relate to a certain
breakdown cause eg oil leak, low pressure etc. I want him to just press the
relevant button everytime it occurs. Then on sheet 2, I want column A to
show which of the 9 buttons were pressed, and in column B date/time stamp.
Each time a button is pressed it records the information on the next
availible row in sheet 2. Periodically I will remove the information from
sheet2 so it will need to start from row 1 again. Can this be done in Excel?

Thanks in advance,

AW

 

[Mike H]

Hi,

Repeat this 9 times or as many times as you have faults to report.

View|Toolbars|Control Toolbox
Put a button on your sheet and change the caption to the name of the fault.
Right click the button and View Code and paste this in

Private Sub CommandButton1_Click()
lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
Cells(lastrow + 1, 1) = CommandButton1.Caption
Cells(lastrow + 1, 1).Offset(, 1).Value = Time
End Sub

Note that every time you create a button this line will change automatically
Private Sub CommandButton2_Click()

But this line will require editing to the same as the command button.
Cells(lastrow + 1, 1) = CommandButton1.Caption
:



Mike

 

[ArcticWolf]

Hi Mike,

Thanks for the swift reply. This works great, I could only get it work on
one sheet (not over the two) so have moved the buttons 'away' from where the
data is populating.

Much appreciated, ATB.

AW

 

[Mike H]

Thanks for the feedback. If you want it to work on a sheet different from the
button sheet use this

LastRow = Sheets("Sheet2").Cells(Cells.Rows.Count, "A").End(xlUp).Row
Sheets("Sheet2").Cells(LastRow + 1, 1) = CommandButton1.Caption
Sheets("Sheet2").Cells(LastRow + 1, 1).Offset(, 1).Value = Time

Change sheet2 to whatever you want

Mike

 

[Mike H]

Thanks for the feedback. If you want it to work on a sheet different from the
button sheet use this

LastRow = Sheets("Sheet2").Cells(Cells.Rows.Count, "A").End(xlUp).Row
Sheets("Sheet2").Cells(LastRow + 1, 1) = CommandButton1.Caption
Sheets("Sheet2").Cells(LastRow + 1, 1).Offset(, 1).Value = Time

Change sheet2 to whatever you want

Mike