Format problem in displayed userform

[Anthony]

Hi all,

In sheet 'DATA' cells B12 and I12 are the source data
these are copy/pasted into cells B2 and I12 of 'RESULTS' sheet
all of these cells are formatted as 'Custom hh:mm' however when I place
these values into my userform they are with no format ie 0.42342423154238

Any ideas as to why??

many thanks

 

[Jarek Kujawa]

0.42342423154238 stands for 10:09:44

you should (re)format yr values put into userform

with .NumberFormat = "h:mm:ss" for instance

 

[Anthony]

Hi Jarek and thanks for ur reply,

the code in my userform is as follows...

ws.Cells(iRow, 2).Value = Me.txtTime.Value

how do I reformat this so that is is correctly formatted as HH:MM?

 

[Jarek Kujawa]

am not sure but you might try Me.txtTime

or use sth. like

ws.Cells(iRow, 2).Value =
Application.WorksheetFunction.Text(Me.txtTime,"hh:mm")

 

[Dave Peterson]

with ws.cells(irow,2)
.numberformat = "h:mm:ss"
.value = me.txttime.value
end with

 

[Anthony]

Hi Dave,

Sorry I think I placed the wrong part of the code..

it should have been

frmOldJob.Label18 = Range("B2")

So how do I get this formatted as hh:mm??

 

[Jarek Kujawa]

why not:

frmOldJob.Label18.Caption =
Application.WorksheetFunction.Text((Range("B2"), "hh:mm:ss")


HIH

 

[Anthony]

Hi Jarek,

A slight typo but your suggestion works just great...

Application.WorksheetFunction.Text((Range("B2")), "hh:mm:ss")

Many thanks

 

[Dave Peterson]

If B2 is already formatted nicely:
frmOldJob.Label18 = Range("B2").Text

or use VBA's own format function:
frmOldJob.Label18 = format(Range("B2").value, "h:mm:ss")