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!
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
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
 
Upvote 0
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!!
 
Upvote 0
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
 
Upvote 0
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!!
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,089
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top