Time format in userform textbox

[anon]

Hi all,

I thought this would be an easy solve but it has me baffled. I've read
quite a few old posts and still haven't a solution so would appreciate
any help.

I have a textbox on a userform I am trying to set to a time format.
(The textbox is bound to a ws cell and this is set to time format)

I have tried all of the below;

UserForm5.Controls("TextBox" & i).Text = Format$(ws.Range("g" & K),
"Short Time")
UserForm5.Controls("TextBox" & i).Text = Format$(ws.Range("g" & K),
"h:mm")
UserForm5.Controls("TextBox" & i).Text = Format$
(sheets("sheet4").range("g" & K), "H:mm")
UserForm5.Controls("TextBox" & i).Text = Format(ws.Range("g" & K),
"Short Time")
UserForm5.Controls("TextBox" & i).Text = Format(ws.Range("g" & K),
"h:mm")
UserForm5.Controls("TextBox" & i).Text =
Format(sheets("sheet4").range("g" & K), "H:mm")


None of which have worked - the time still shows as a decimal. I'd
appreciate any pointers as i'm baffled and getting very frustrated!

 

[Norman Jones]

Hi Anon,

Try something like:

'=========>>
Option Explicit

Private Sub UserForm_Initialize()
Dim wb As Workbook
Dim SH As Worksheet
Dim Rng As Range
Dim i As Long

Set wb = ThisWorkbook
Set SH = wb.Sheets("Sheet1") '<<==== CHANGE
Set Rng = SH.Range("A1") '<<==== CHANGE

For i = 1 To 5
Me.Controls("Textbox" & i).Value = _
Rng.Offset(i - 1).Text
Next i

End Sub
'<<=========

 

[anon]

Hi,

Thanks for the reply. I've tried this and is still not working. If i
break the code I can see rng.text = "09:00" for example, however it
still appears in my textbox as .375 (or some other decimal!).

Any other suggestions?

Thanks again

 

[Norman Jones]

Hi Anon,

The suggested code works for me.

If you wish, I can send you a copy of my test
workbook, in reesponse to an email to:

norman_jones@NOSPAMbtconnectDOTcom


(Delete "NOSPAM" and replace "DOT" with a full stop [period] )

 

[Norman Jones]

Hi Anon,

I received your email and I have
sent you my test workbook.





---
Regards.
Norman

Hi Anon,

The suggested code works for me.

If you wish, I can send you a copy of my test
workbook, in reesponse to an email to:

norman_jones@NOSPAMbtconnectDOTcom


(Delete "NOSPAM" and replace "DOT" with a full stop [period] )




---
Regards.
Norman

Hi,

Thanks for the reply. I've tried this and is still not working. If i
break the code I can see rng.text = "09:00" for example, however it
still appears in my textbox as .375 (or some other decimal!).

Any other suggestions?

Thanks again