A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Programming
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Time format in userform textbox



 
 
Thread Tools Display Modes
  #1  
Old May 10th 08, 09:34 PM posted to microsoft.public.excel.programming
anon
external usenet poster
 
Posts: 77
Default Time format in userform textbox

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!
Ads
  #2  
Old May 10th 08, 11:56 PM posted to microsoft.public.excel.programming
Norman Jones[_2_]
external usenet poster
 
Posts: 421
Default Time format in userform textbox

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
'<<=========



---
Regards.
Norman
"anon" > wrote in message
...
> 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!


  #3  
Old May 11th 08, 09:47 AM posted to microsoft.public.excel.programming
anon
external usenet poster
 
Posts: 77
Default Time format in userform textbox

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

  #4  
Old May 11th 08, 12:05 PM posted to microsoft.public.excel.programming
Norman Jones[_2_]
external usenet poster
 
Posts: 421
Default Time format in userform textbox

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


"anon" > wrote in message
...
> 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
>


  #5  
Old May 12th 08, 06:03 PM posted to microsoft.public.excel.programming
Norman Jones[_2_]
external usenet poster
 
Posts: 421
Default Time format in userform textbox

Hi Anon,

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





---
Regards.
Norman


"Norman Jones" > wrote in message
...
> 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
>
>
> "anon" > wrote in message
> ...
>> 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
>>

>


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Format TextBox on UserForm ryguy7272 Excel Programming 7 August 5th 07 03:46 PM
Format a Userform textbox to Military time Christy Excel Programming 2 August 24th 05 10:15 PM
TextBox in UserForm with > 24h time format Mika Excel Programming 1 February 1st 05 07:45 PM
format textbox in userform jeffP Excel Programming 1 January 25th 05 01:10 AM
Format of a TextBox in a userform MD Excel Programming 4 January 13th 05 05:42 PM


All times are GMT +1. The time now is 10:03 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.