Selecting a Time in a cell w/out inputting the data

[cheri]

Hello,

I am creating a time sheet for our employees to clock in & out of. Wha
I want to do is set up each cell for the Start Time, Lunch Out, Lunc
In and End Time so the employee has choices of the hours broken down i
fifteen minute intervals to select from without typing in the hours.

What I would really like is to create a time clock similar to the P
time clock in the right hand corner where you can change the tim
accordingly. I'm really not sure if this is possible or how to do thi
so I am hoping some one can help me.

If the above is not possible then how can I create a drop down windo
showing all the hours based on military time? The only way that I kno
how to do this is data validation; is this the only way to do this?

Thank you so much for your help,
Cher

 

[Dick Kusleika]

Cheri

Here's a couple of things that I've tried for entering time:

Use a Spin Button
Create a spin button on your sheet, and change the Max to 1440, Min to 0,
Small Change to 15. Use the Worksheet_SelectionChange event to determine
which columns will show the spin button. Like this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

'If the user selects column B through E
If Target.Column >= 2 And Target.Column <= 5 Then
With Me.SpinButton1

'Make the spin button visible and place it next to
'the selected cell
.Top = Target.Top
.Left = Target.Left + Target.Width
.Visible = True

'If the selected cell doesn't have a time already
If IsEmpty(Target) Then

'If it's start of the day (col B), then set it
'equal to 8AM to start, otherwise set it to
'the cell-to-the-left's time to start
If Target.Column = 2 Then
.Value = TimeValue("8:00 AM") * 1440
Else
.Value = .TopLeftCell.Offset(0, -2).Value * 1440
End If

'If the selected cell already has a time, then use that
'time to start
Else
.Value = Target.Value * 1440
End If
End With
Else

'If the selected cell is outside of the range, hide the
'spin button
Me.SpinButton1.Visible = False
End If

End Sub

Then, use the Change event of the spin button change the time in the cell

Private Sub SpinButton1_Change()

With Me.SpinButton1
.TopLeftCell.Offset(0, -1).Value = CDate(.Value / 1440)
.TopLeftCell.Offset(0, -1).NumberFormat = "hh:mm"
End With

End Sub

I used that method for a while, but got tired of clicking the spin button.
Now I just enter the military time as four digit number and use the
Worksheeet_Change event to format it properly. Here's what I use now:

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False

On Error GoTo errhndlr

If Target.Column >= 2 And Target.Column <= 5 Then
If IsNumeric(Target.Value) And Target.Value <> "" Then
Target.Value = TimeValue(Left(Target.Value, Len(Target.Value) - 2) _
& ":" & Right(Target.Value, 2))
Target.NumberFormat = "h:mm"
End If
End If

ChangeExit:
Application.EnableEvents = True
Exit Sub

errhndlr:
MsgBox Err.Number & vbCrLf & Err.Description
GoTo ChangeExit
End Sub

I know that doesn't answer your specific questions, but I thought it might
help.