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 did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,545
Messages
5,832,387
Members
430,129
Latest member
EmilyCrandall

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
Top