Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 10 of 10

Thread: Why won't this work?

  1. #1
    Board Regular
    Join Date
    Apr 2002
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default


    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?

  2. #2
    Board Regular
    Join Date
    Apr 2002
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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!

  3. #3
    New Member
    Join Date
    Mar 2002
    Posts
    39
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

    Russell

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    Manchester, UK
    Posts
    95
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Newcastle, UK
    Posts
    1,174
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  6. #6
    Board Regular
    Join Date
    Apr 2002
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default


    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

  7. #7
    New Member
    Join Date
    Mar 2002
    Posts
    39
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

    Russell

  8. #8
    Board Regular
    Join Date
    Apr 2002
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default


    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

  9. #9
    Board Regular
    Join Date
    Feb 2002
    Location
    Where the wild roses grow
    Posts
    285
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default


    Hey Bunty,

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

    Audiojoe

  10. #10
    Board Regular
    Join Date
    Apr 2002
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •