Subtract Values In Combo Box

[NeedsHelp2]

On a Useform I've a two combo boxes which are filled with the times in 1 min intervals over a period of 24 hours.

Also on the user form I've a label which I want to populate with the variation between the two times once both combo boxes have data selected.

I also need to show the output in the label as a time format hh:mm


Any suggestions would be appreciated.

 

[daniels012]

HOw are the combo boxes populated?

Michael

 

[NeedsHelp2]

I use this piece of code to populate the combo boxes

Dim NoDupesE As New Collection
Dim ListSortE As Range
Sheets("LookUps").Select
Range(("J2"), Range("J65536").End(xlUp)).SpecialCells(xlCellTypeVisible).Activate
Selection.Activate

On Error Resume Next

For Each cell In Selection
NoDupesE.Add cell.Value, CStr(cell.Value)

Next cell

On Error GoTo 0
'Dim lngIndex As Long
For lngIndex = 1 To NoDupesE.Count
cmbE.AddItem Format$(NoDupesE(lngIndex), "hh:mm")
Next lngIndex

 

[daniels012]

I do the same thing with 2 combo boxes. Only I have all the times in cells (f3:f1442) then I use the "cell link" of the combo boxes to subtract the difference.

I can go into more detail if you think it will help.

Michael

 

[NeedsHelp2]

If you can give me more info it would be appreciated.

 

[daniels012]

In cells:
=Sheet1!$F$3:$F$1442
I have all my times listed.

I have a combo box that has a cell link to C5

I have a 2nd combo box which links to C7

I have this in B10:
=IF(C7>C5,(C7-C5)+1,(C7+1440)-C5+1)
This is in case they work from 11pm to 3am.

In cell B12:
=ROUNDDOWN(B10/60,0)
In cell B13
=ABS(((B12*60)-B10)+1)

Then in cell B2 I have:
="He Worked "&B12&" Hours "&B13&" Minutes"

Hope some of this helps you. I know I really go the round about way to do things, but it gets the results I need.

Hope this Helps!
Michael

 

[NeedsHelp2]

Thanks, but need to set the result to show in the label as soon as both combo boxes have been selected.

Can anyone assist further?

 

[daniels012]

Sorry, I missed a part.

Select the text box and goto the formula bar
and enter the cell reference as =A1 or wherever you have the data.
="He Worked "&B12&" Hours "&B13&" Minutes"

This should work!

Michael

 

[NeedsHelp2]

I'm almost there I think. The code below works but I'm having a problem with getting the result to appear as hh:mm. Can someone please advise how I can format it correctly?

Label33 = TimeValue(cmbF.Value) - TimeValue(cmbE.Value)