Timesheet with Button to insert current time

donnyp02

New Member
Joined
Dec 5, 2005
Messages
40
I'm working on a timesheet for my Teir 2 staff, they dont have exact clock in/out times, so what I'm wanting to do is have a button at the top of the worksheet that thay can click and it will add the timestamp into either the active field, or preferably the next time slot that is null. Thanks for any help in advance!
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
Welcome to the Board!

With an ActiveX Button drawn from the Control Toolbox you could start with something like this:
Code:
Private Sub CommandButton1_Click()
    If ActiveCell = "" Then ActiveCell = Time
End Sub
Hope that helps,

Smitty
 

donnyp02

New Member
Joined
Dec 5, 2005
Messages
40
Thanks for your quick reply, that works beautifully. But is it possible to do something like this, I just dont know what to replace "B9" with to pull it out of the worksheet.

If B9 = "" Then B9 = Time
Else
B10 = "" Then B10 = Time
ElseIf
B11 = "" Then B11 = Time
...
etc...

Do I need to explain more?

Thanks for your help!!
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
This will add the Time stamp to each successive empty cell in column B;
Code:
Private Sub CommandButton1_Click()
    Range("B" & Rows.Count).End(xlUp).Offset(1) = Time
End Sub
Smitty
 

donnyp02

New Member
Joined
Dec 5, 2005
Messages
40

ADVERTISEMENT

That worked but it wouldnt start inputting the time until B15, the only field in B that is populated is B9, B1-B8 is empty, B10-B14 is empty and formatted for time. the button starts populating at 15.

Week of: Saturday Sunday Monday Teusday Wensday
Time In
Time Out
Time In
Time Out
Total 0:00

This is part of my table, I was looking for a button that when you came in would populate the first Time In, you click the button again to clock out for lunch, again to come back from lunch, and again to leave for the day, the next day you come in if the last Time Out is pupulated, if b13 = "" else c10 = Time...
any ideas?

Thanks all you smart ppl!!
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
That'll require a bit more, unfortunately, I'm really pressed for time at the moment, so maybe someone can step up for me.

Smitty
 

donnyp02

New Member
Joined
Dec 5, 2005
Messages
40

ADVERTISEMENT

Is there anyway to specify a specific cell in VBA?
 

XLGibbs

Well-known Member
Joined
Feb 25, 2005
Messages
2,446
Yes there are many ways to reference a specific cell.

If I understand correctly, you only want them to press one button, which would place the time of button press into the next open cell, with the exception of B9?

Code:
Private Sub CommandButton1_Click()

Dim x as integer

For x = 1 to 14
IF  isempty (cells(x,2)) then 
cells(x,2).Value = Time
Exit for
End if
Next x 

End Sub

Assumtions:

-You have a formula in B9 (therefore it will not read as empty)
-there will be no missed clicks, as it will find the first empty cell in column b and insert the time there. This will allow for manual entry into the cells as well, but clicking the button will place the time in the first empty cell found from B1 down.
 

donnyp02

New Member
Joined
Dec 5, 2005
Messages
40
One more thing, in VBA, before it enters the time into a field, is it possible to make it round the time to the nearest quarter hour? I've looked through a bunch of timesheet posts and posts about VBA rounding, just cant find anything that did this specifically.
 

Watch MrExcel Video

Forum statistics

Threads
1,119,273
Messages
5,577,144
Members
412,769
Latest member
VK12345
Top