Consulting

Results 1 to 6 of 6

Thread: time format a text box in excel userform

  1. #1

    time format a text box in excel userform

    Hi
    I found this code to format a textbox as time format:
    Private Sub txtTime_AfterUpdate()
    
    Dim tString As String
     With txtTime
    
    'Check if user put in a colon or not
     If InStr(1, .Value, ":", vbTextCompare) = 0 And Len(.Value) > 1 Then
    
    'If not, make string 4 digits and insert colon
     tString = Format(.Value, "0000")
     tString = Left(tString, 2) & ":" & Right(tString, 2)
      txtTime.Value = Format(TimeValue(tString), "hh:mm")
    Else
    
    'Otherwise, take value as given
     .Value = Format(.Value, "hh:mm")
    End If
    End With
    It works for time up to 23:59 ,but when user enters a value higher than 23:59 it gives error.Since I'm a new to VBA ,please fix it to show the time in format [h]:mm not in d;hh:mm
    Thanks

  2. #2
    VBAX Expert Logit's Avatar
    Joined
    Sep 2016
    Posts
    606
    Location
    If you enter a time after midnight as (an example) 01:10 or 0110, does it still give an error ?

  3. #3
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    What Values do you suspect a User to enter, besides > 24:59 and "hhmm"?

    What do you want to happen when the User enters a value that cannot be converted to a Time, eg >=2400 ?
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  4. #4
    With given value in input column the text box shows like output column;

    Input value output value
    0 00:00
    1 00:01
    10 00:10
    100 01:00
    0110 01:10
    2359 23:59
    2400 Error
    12523 Error
    since i'm calculating total time it may be more than 100 hours so i'd like to format the timer to show time like "hhh:mm" or [hhh]:mm

  5. #5
    I want when user enters e.g.
    Input Display
    2400 24:00
    4205 42:05
    15857 158:57
    Result to be shown as display column.

  6. #6
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    So... If a Colon (":") is not present, then assume that...
    One or two digit entries are in minutes
    Three or more digit entries are in hours and minutes

    Is that correct?

    One or two digit entries are as simple as Entry = ":" & Format(entry, "00")

    What if a two digit entry is greater than 59? eg. 90 = 1 hour 30 minutes?





    For this:
    Input Display
    2400 24:00
    4205 42:05
    15857 158:57

    Entry = Left(Entry, Len(Entry) - 2) & ":" & Right(Entry, 2)
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

Posting Permissions

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