PDA

View Full Version : time format a text box in excel userform



mortezataher
02-22-2017, 05:14 AM
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

Logit
02-22-2017, 08:13 AM
If you enter a time after midnight as (an example) 01:10 or 0110, does it still give an error ?

SamT
02-22-2017, 08:25 AM
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 ?

mortezataher
02-22-2017, 10:43 PM
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

mortezataher
02-23-2017, 06:03 AM
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.

SamT
02-23-2017, 09:16 AM
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)