Why won't this work?

buntykins

Board Regular
Joined
Apr 11, 2002
Messages
76
I have a userform with a textbox on it. The textbox takes it's data from a cell in the spreadsheet. This cell has a time format like this "hh:mm:ss" so a typical entry on the sheet looks like this "01:14:59". Thing is, when it comes through on the userform, it loses it's format and comes through as numbers weird, like 2.6666669696969 and stuff.

How can I get round this?
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Further to this, I have just realised something. The number it is bringing through is the number in the cell, but displayed differently. So for instance 00:24:15 is displayed as 24.158787987987098709879087 in the textbox. Basically I think I just need to change the format of the textbox to hh:mm:ss. Does anyone know how to do this?

Sorry for not noticing the above before, god I am so blonde!
 
Upvote 0
Assuming the cell referenced is A1 then try:
Format(Range("a1"), "hh:mm:ss")
In your code

Russell
 
Upvote 0
Does the source cell need to be time formatted? If it is text formatted, this does not happen.
 
Upvote 0
On 2002-05-09 08:55, buntykins wrote:
Further to this, I have just realised something. The number it is bringing through is the number in the cell, but displayed differently. So for instance 00:24:15 is displayed as 24.158787987987098709879087 in the textbox. Basically I think I just need to change the format of the textbox to hh:mm:ss. Does anyone know how to do this?

Sorry for not noticing the above before, god I am so blonde!

What about something like:

Dim Tme As String

Tme = Range("a1").Value
TextBox1 = Format(Tme, "hh:mm")

One thing I MUST point out is that the 2.6666669696969 you mentioned is not 16:00.
if you format the cell as [h]:mm you will see that it 64:00.

Excel rounds the time to within a day.

i.e. 24:00 is 1 day but format the cell as h:mm and it will look like 0:00 and as general 1

so 2.6666669696969 is 2 Days and 0.6666669696969 of a day, or, 16 hours. so:

2 days 48 hrs + 16 hrs = 64:00 formated as [h]:mm.

I hope I didn't bore you there. if you want the true [h]:mm use:

Dim Tme As String

Tme = Range("a1").Value
TextBox1 = Format(Tme, "[h]:mm")

hope this helps.

P.S. 24.158787987987098709879087 is 579:48:00 hours and minutes or 3:48:00 removing the day part NOT 0:24:15. don't know where you got that No.




_________________
Share the wealth!!
Ian Mac
This message was edited by Ian Mac on 2002-05-09 09:22
 
Upvote 0
Thanks for your help guys, sorry I didn't reply last night but I went home. That code you gave me is great, BUT it's not appearing correctly.

The cell that the textbox is refenced to is showing 00:00:02, but the textbox is showing an actual time, like 09:15. It should be showing an amount of time.

This is crazy, all I want it to do is show exactly what's in the cell. The cell is formatted to [hh]:mm:ss

Anyone got any ideas?

Janie
 
Upvote 0
The cell changes all the time, but it always stays in the same format - hh:mm:ss

The userform textbox changes accordingly, but in the wrong format
 
Upvote 0
Hey Bunty,

That code that Ian gave you, you did change the references in it correctly didn't you...

Audiojoe
 
Upvote 0
Oh sh#t! No I didn't!!!

Oh guys I am so sorry. I forgot to change the cell reference in it! Oh my God, how blonde can someone be!!! It works fine now

I am so sorry to have wasted your time

Thanks Joe, you're a little star

xxxxxxxxxxxxxx
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,923
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top