Why won't this work?

[buntykins]

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?

[buntykins]

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!

[Russell]

Assuming the cell referenced is A1 then try:
Format(Range("a1"), "hh:mm:ss")
In your code

Russell

[Mr Nick]

Does the source cell need to be time formatted? If it is text formatted, this does not happen.

[Ian Mac]

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 ]

[buntykins]

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

[Russell]

If you change the cell you are referencing does the text box change and if so how?

Russell

[buntykins]

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

[Audiojoe]

Hey Bunty,

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

Audiojoe

[buntykins]

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