03856me
Active Member
- Joined
- Apr 4, 2008
- Messages
- 297
I have a userform that populates the textboxes by looking up the record based on the combobox value. There are a couple of textboxes that are dates and the formatting is wrong - 02/16/15 in the table imports into the textbox as 42086, which I understand. I tried setting the focus on the box after importing trying to fire the change event but that is now working. HELP me please - how can I present it correctly?
Also, the time in the spreadsheet is formatted as HH:MM which was added by another userform but when I import it the time is wrong - it is military time but 12:00 imports as 12:05 AM into the textbox, why? any ideas?
Here is my code for the DateIn textbox and also for my TimeIn textbox, your help is greatly appreciated.
Also, the time in the spreadsheet is formatted as HH:MM which was added by another userform but when I import it the time is wrong - it is military time but 12:00 imports as 12:05 AM into the textbox, why? any ideas?
Here is my code for the DateIn textbox and also for my TimeIn textbox, your help is greatly appreciated.
Code:
'########## DATE IN
Private Sub txt_dateIn_Exit(ByVal Cancel As MSForms.ReturnBoolean)
With Me.txt_dateIn
If Len(.Value) = 0 Then
.SetFocus
End If
.SelStart = 0
.SelLength = Len(.Text)
End With
If txt_dateIn = vbNullString Then
Exit Sub
If IsDate(txt_dateIn) Then
txt_dateIn = Format(txt_dateIn, "Short Date")
Else
MsgBox "Non valid date"
Cancel = True
End If
End If
txt_dateIn.BackColor = 16777215
End Sub
Private Sub txt_dateIn_Change()
Dim Char As String
Dim x As Date
Dim y As Date
Char = Right(txt_dateIn.Text, 1)
Select Case Len(txt_dateIn.Text)
Case 1 To 2, 4 To 5, 7 To 8
If Char Like "#" Then
If Len(txt_dateIn) = 8 Then
On Error Resume Next
x = DateValue(txt_dateIn.Text)
y = DateSerial(Right(txt_dateIn, 2), Mid(txt_dateIn, 4, 2), Left(txt_dateIn, 2))
If Err = 0 And x = y Then
On Error GoTo 0
Exit Sub
Else
Err.Clear
On Error GoTo 0
txt_dateIn.SelStart = 0
txt_dateIn.SelLength = Len(txt_dateIn.Text)
'MsgBox "Please enter a valid date in the form dd/mm/yy", vbCritical + vbOKOnly, "Error"
Exit Sub
End If
Else
Exit Sub
End If
End If
Case 3, 6
If Char Like "/" Then Exit Sub
End Select
Beep
On Error Resume Next
txt_dateIn.Text = Left(txt_dateIn.Text, Len(txt_dateIn.Text) - 1)
txt_dateIn.SelStart = Len(txt_dateIn.Text)
End Sub
Private Sub txt_dateIn_Enter()
txt_dateIn.BackColor = 13434879
End Sub
'########## TIME IN
Private Sub txt_timeIn_Exit(ByVal Cancel As MSForms.ReturnBoolean)
txt_timeIn.BackColor = 16777215
If IsDate(Me.txt_timeIn.Value) Then
Me.txt_timeIn.Value = Format(Me.txt_timeIn.Value, "HH:MM AM/PM")
Else
MsgBox "Input time as HH:MM military"
Me.txt_timeIn.Value = ""
Cancel = True
End If
End Sub
Private Sub txt_timeIn_Change()
Me.txt_timeIn.Value = Format(Me.txt_timeIn.Value, "HH:MM AM/PM")
End Sub
Private Sub txt_timeIn_Enter()
txt_timeIn.BackColor = 13434879
End Sub
