A challenge in time

[tommy]

Hi Here’s a challenge for some one what I would like to do is, in cell A2 I
would like to click on it with the mouse and a tick goes in the cell which
would also subtract C2 from B2, with the answer in D2 and I would like B2 to
be a drop down box with 00:00 to 24:00 as time. And the same in C2, so if B2
is 14:00 and C2 is 22:00 the answer would be 08:00 in cell D2.
Any Takers? thanks in advance.
Tommy.

 

[Bob Phillips]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Const WS_RANGE As String = "A:A" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target

.Offset(0, 3).Value = .Offset(0, 2).Value - .Offset(0, 1).Value
.Value = "a"
.Font.Name = "Marlett"
.Offset(0, 1).Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.

 

[tommy]

Thanks Bob, Excuse my ignorance but what does it do.
Tommy

 

[Bob Phillips]

Try it and see.

 

[tommy]

Fantastic !! thank you , at the risk of being rude where do I get the drop
down box, with the time in.
Tommy

 

[Bob Phillips]

I presumed that you are had that.

Build a list of times and use Data>Validation>List and refer to that list.

 

[tommy]

Thanks Bob, It works great, Your a Gentleman and scholar.