Time drop down selector

[bvwalker1]

I'm working with time card data and I would like to be able to select a cell and have it provide a way to choose both the hour and minute. For example, one cell is time in: 8:01 am and another cell is time out 5:05pm. It doesn't necessarily need to be a drop down, I just want a way to select the time without having to type it in.

[mjr veverka]

It can be without any macros.

[Logit]

.

Option Explicit
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Column = 11 Then 'MsgBox "column 11 is clicked"
    With Sheet1
        .Unprotect
        With Target
            .Font.ColorIndex = 4
            .Value = Environ("username") & " " & Format(Now(), "dd/mm/yyyy hh:mm:ss AM/PM")
        End With
        If CDbl(Time) > .Cells(Target.Row, 4) Then Target.Font.ColorIndex = 3
        .Protect
        Cancel = True
    End With
End If
If Target.Column = 12 Then 'MsgBox "column 11 is clicked"
    With Sheet1
        .Unprotect
        With Target
            .Font.ColorIndex = 4
            .Value = Environ("username") & " " & Format(Now(), "dd/mm/yyyy hh:mm:ss AM/PM")
        End With
        If CDbl(Time) > .Cells(Target.Row, 5) Then Target.Font.ColorIndex = 3
        .Protect
        Cancel = True
    End With
End If
End Sub

[torachan]

Late entry
Click on cells in column A or B
Dropdown appears, click on hour or minute figures to update.
Confirm selection by clicking 'tick'
Times transfered to target cell.
torachan

[bvwalker1]

Late entry
Click on cells in column A or B
Dropdown appears, click on hour or minute figures to update.
Confirm selection by clicking 'tick'
Times transfered to target cell.
torachan

This caused an error for me when running. Apparently I'm missing date time picker add-in, but I can't seem to install it. I followed the instructions online and it states it was successful, but it doesn't appear as drop down choice in the ActiveX controls.

[torachan]

I now remember this procedure being somewhat fiddly when I did it many years ago.
It certainly took a number of steps to achieve something that should be intuitive to me.
In the VBA editor can you check that you have at least the minimum boxes checked as per the attached screen captures.
selecting Tools > References.
CaptureRefs.JPG

With a blank UserForm inserted and ToolBox visible right click on ToolBox in additional tools is DatePicker checked with a 'X'
CaptureDTP.JPG

[bvwalker1]

I now remember this procedure being somewhat fiddly when I did it many years ago.
It certainly took a number of steps to achieve something that should be intuitive to me.
In the VBA editor can you check that you have at least the minimum boxes checked as per the attached screen captures.
selecting Tools > References.
Attachment 618106

With a blank UserForm inserted and ToolBox visible right click on ToolBox in additional tools is DatePicker checked with a 'X'
Attachment 618107

Torachan - Thank you for taking the time to do this. I have all the references except, Microsoft Windows Common Controls. I do not see it as a selectable option though for me. Based on your picture, it looks like it is related to mscomct2.ocx I found a Youtube video showing how to add this to Excel and followed the steps and it stated it was successful, but I still don't see common controls or date picker as options for me. I gave you some rep for your time. Thank you very much.

[torachan]

@bvwalker1
Sorry you have had difficulty installing Date/Time picker.
Consultation prize attached, my version of time picker.
It should work as it only uses standard form elements.
The userform is modeless enabling you to access sheet at the same time.
If you close the timepicker it will reappear as you click in cells in columns A or B
The timepicker floats near to selected cell as the sheet scrolls up.
The time is relevant to the cell selected to enter the time click green button.
Only use the picker for time entry, manual entry into 'IN' or 'OUT' cell will not compute across row.
torachan

[bvwalker1]

@bvwalker1
Sorry you have had difficulty installing Date/Time picker.
Consultation prize attached, my version of time picker.
It should work as it only uses standard form elements.
The userform is modeless enabling you to access sheet at the same time.
If you close the timepicker it will reappear as you click in cells in columns A or B
The timepicker floats near to selected cell as the sheet scrolls up.
The time is relevant to the cell selected to enter the time click green button.
Only use the picker for time entry, manual entry into 'IN' or 'OUT' cell will not compute across row.
torachan

Torachan - Thanks! This works great. It is exactly what I was after. You the man!