Format function doesn't recognise [hh]:mm

[jennie]

Hi,

Is there a way of getting the format function in VBA or anothe
function that will do the same job to recognise the custom forma
[hh]:mm?

When reopening a user form, it fills in the values from the relevan
line on the worksheet into the boxes.
However as the time in the worksheet can be over 24 hours, e.g. 4
hours (by using the custom format [hh]:mm), I would like the box i
the user form to show the value in hours and minutes too even if it i
above 23:59:59.

This does not work
cboResponse1.Value = Format (ActiveCell.Offset(0, 8).Value, "[hh]:mm")


This only works for values under 24:00
cboResponse1.Value = Format (ActiveCell.Offset(0, 8).Value, "hh:mm")


cboResponse1.Value = ActiveCell.Offset(0, 8).Value
and this return just displays the time in a numerical format e.g
1.45.....


Thanks for any help you can give
Jenni

 

[Ron Rosenfeld]

Hi,

Is there a way of getting the format function in VBA or another
function that will do the same job to recognise the custom format
[hh]:mm?

When reopening a user form, it fills in the values from the relevant
line on the worksheet into the boxes.
However as the time in the worksheet can be over 24 hours, e.g. 48
hours (by using the custom format [hh]:mm), I would like the box in
the user form to show the value in hours and minutes too even if it is
above 23:59:59.

This does not work
cboResponse1.Value = Format (ActiveCell.Offset(0, 8).Value, "[hh]:mm")


This only works for values under 24:00
cboResponse1.Value = Format (ActiveCell.Offset(0, 8).Value, "hh:mm")


cboResponse1.Value = ActiveCell.Offset(0, 8).Value
and this return just displays the time in a numerical format e.g.
1.45.....


Thanks for any help you can give
Jennie

[h] is NOT a listed user-defined format string for the VBA Format function.

I believe you could use the TEXT worksheet function, though:

cboResponse1.Value = worksheetfunction.text _
(ActiveCell.Offset(0, 8).Value,"[hh]:mm")


--ron

 

[shockley]

jennie,

Failing something more elegant, you could 'force' it with something like
this:

Sub Tester()
cboResponse1.Value = fTimeText(ActiveCell.Offset(0, 8).Text)
End Sub
Function fTimeText(stest)
If stest <> Empty Then
ColonPos = InStr(1, stest, ":")
H = Left(stest, ColonPos - 1)
M = Right(stest, Len(stest) - ColonPos)
fTimeText = H & ":" & M
End If
End Function

HTH,
Shockley

 

[jennie]

Thank you for your help
I will go and try them!

Thanks again
Jenni