PDA

View Full Version : Help with code



jcutler
04-08-2014, 04:26 PM
Private Sub txtTestTimeInput_Exit(ByVal Cancel As MSForms.ReturnBoolean)

With txtTestTimeInput
txtTestTimeInput.Text = Format(txtTestTimeInput.Value, "##:##")
End With

Dim strMsge As String
Dim arrSplit

dteDate = 0
If Trim(Len(txtTestTimeInput)) = 0 Then Exit Sub 'Required to allow deletion of time

arrSplit = Split(txtTestTimeInput.Value, ":")

If UBound(arrSplit) <> 1 Then 'Does not have colon separator
strMsge = "Incorrect time format. Use colon as separator."
GoTo msgLabel
End If

If arrSplit(0) < 0 Or arrSplit(0) > 23 Then
strMsge = "Hours must be between 0 and 23."
GoTo msgLabel
End If

If arrSplit(1) < 0 Or arrSplit(1) > 59 Then
strMsge = "Minutes must be between 0 and 59."
GoTo msgLabel
End If

dteTime = TimeSerial(arrSplit(0), arrSplit(1), 0)

txtTestTimeInput.Value = Format(dteTime, "hh:mm") 'Re-write time in correct format.

Exit Sub 'If gets to here then time OK so exit here

msgLabel:
Cancel = True 'Cancels the update and cursor remains in the TextBox control
MsgBox strMsge & vbCrLf & "Re-enter time correctly in hhmm format."
txtTestTimeInput.Text = ""
End Sub
I found this above code on another forum and the only problem that I can see so far is if the user enters the time between midnight(00:00) and 1am(01:00). It will execute this part of the code. In other words when the user enters "00" for midnight the program will display the below message. What could I add to the current code to fix this problem. Thanks.

If arrSplit(0) < 0 Or arrSplit(0) > 23 Then
strMsge = "Hours must be between 0 and 23."
GoTo msgLabel
End If

GTO
04-08-2014, 06:07 PM
Change the top bit to:

With txtTestTimeInput
.Value = Format(.Value, "00:00")
End With


That said, it seems "easier" to me to prevent user entry errors using separate boxes for the hours and minutes, rather than guess which digit is missing when putting it all in one box. I would also coerce the numbers (read as text from Textbox.Value) to Longs before comparing them to limits.

FWIW and if you feel like it, try:

A label (special effect - sunken) as a background. A skinny (special effect - flat; no border) label with just a colon for the caption, and two textboxes, each limited to a length of 2 characters.

Mark

EDIT: ACK! The two textboxes would be flat of course.

SamT
04-08-2014, 07:19 PM
Tip: Put Marks 3 controls in a Frame and size everything to look like one textbox with a colon in it.
Trick: Gently encourage Users to use 2 digit hours with

Private Sub tbxHoursChange()
' Other checks here
If Len(tbxHours.Value) = 2 Then txbMinutes.SetFoucus
End Sub

Paul_Hossler
04-08-2014, 08:45 PM
Another way is to have 3 spin buttons and have the time displayed in a label or stored in a variable. That way there's no format issues or 'too large/small' problems



Option Explicit
Private Sub UserForm_Activate()
With Me
.sbHour = 0
.sbMinute = 0
.sbSecond = 0

Call pvtUpdateTime
End With
End Sub

Private Sub cbDone_Click()
Dim dt As Date

With Me
.Hide
dt = TimeSerial(.sbHour, .sbMinute, .sbSecond)
Unload Me
End With

MsgBox "Time entered was " & Format(dt, "hh:mm:ss")
End Sub
Private Sub sbHour_Change()
With Me.sbHour
If .Value = .Max Then
.Value = .Min
ElseIf .Value = .Min Then
.Value = .Max
End If
Call pvtUpdateTime
End With
End Sub
Private Sub sbMinute_Change()
With Me.sbMinute
If .Value = .Max Then
.Value = .Min
ElseIf .Value = .Min Then
.Value = .Max
End If
Call pvtUpdateTime
End With
End Sub
Private Sub sbSecond_Change()
With Me.sbSecond
If .Value = .Max Then
.Value = .Min
ElseIf .Value = .Min Then
.Value = .Max
End If
Call pvtUpdateTime
End With
End Sub

Private Sub pvtUpdateTime()
With Me
.labTIme = Format(.sbHour, "00") & ":" & Format(.sbMinute, "00") & ":" & Format(.sbSecond, "00")
End With
End Sub




Just another idea

BTW, the attachment code is out of date, but I could not figure out how to replace it:(

Paul