Visual Basic Editor - Time and Date Entry Continued

[Tammy]

Thanks to everyone earlier who responded to my Time and Date Entry question.
I'm trying to get these two strings of code to work together in the Worksheet
Change area, but I'm not understanding why they won't.

I'm trying to enter a date in column A (A1:A10000) and times in columns B
(B1:B1000) and P (P110000). Once the entry is complete, we'll be running a
calculation of time in the ER department.

I've received the separate codes for date and time entry without hyphens,
slashes, and colons, but I am not able to put them together in the same
worksheet change area... I'm assuming that I'm doing something wrong for
them not to work together, but I don't know how to fix it since this is my
FIRST time working with Visual Basic. I can get the date to work on one
worksheet, and the time to work on another worksheet, but I can't get them to
work correctly in the same worksheet. Any help would be greatly appreciated.
Thanks in advance!

 

[Bob Phillips]

Post what you have so far, we are not all familiar with the previous posts.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Tammy]

Thanks Bob! Here's what I have so far. I have the two codes posted into two
separate worksheets because by copying and pasting the information one right
after another in the VBA Editor, it creates an error. I tried taking out the
Private Sub Worksheet_Change(ByVal Target As Range) and the End Sub between
the two but that didn't fix the error either. So....I'm stuck. Any help
would be appreciated. I'm thinking I need to take a quick course in Visual
Basic to understand it. My high school Basic computer course...back in the
80s...uh...doesn't help out so much now! LOL Thanks again in advance!

For the date code:


Private Sub Worksheet_Change(ByVal Target As Range)
Dim DateStr As String

On Error GoTo EndMacro
If Application.Intersect(Target, Range("A1:A10000")) Is Nothing Then
Exit Sub
End If
If Target.Cells.Count > 1 Then
Exit Sub
End If
If Target.Value = "" Then
Exit Sub
End If

Application.EnableEvents = False
With Target
If .HasFormula = False Then
Select Case Len(.Formula)
Case 4 ' e.g., 9298 = 2-Sep-1998
DateStr = Left(.Formula, 1) & "/" & _
Mid(.Formula, 2, 1) & "/" & Right(.Formula, 2)
Case 5 ' e.g., 11298 = 12-Jan-1998 NOT 2-Nov-1998
DateStr = Left(.Formula, 1) & "/" & _
Mid(.Formula, 2, 2) & "/" & Right(.Formula, 2)
Case 6 ' e.g., 090298 = 2-Sep-1998
DateStr = Left(.Formula, 2) & "/" & _
Mid(.Formula, 3, 2) & "/" & Right(.Formula, 2)
Case 7 ' e.g., 1231998 = 23-Jan-1998 NOT 3-Dec-1998
DateStr = Left(.Formula, 1) & "/" & _
Mid(.Formula, 2, 2) & "/" & Right(.Formula, 4)
Case 8 ' e.g., 09021998 = 2-Sep-1998
DateStr = Left(.Formula, 2) & "/" & _
Mid(.Formula, 3, 2) & "/" & Right(.Formula, 4)
Case Else
Err.Raise 0
End Select
.Formula = DateValue(DateStr)
End If

End With
Application.EnableEvents = True
Exit Sub
EndMacro:
MsgBox "You did not enter a valid date."
Application.EnableEvents = True

End Sub


And for the Time Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim TimeStr As String

On Error GoTo EndMacro
If Application.Intersect(Target, Range("b1:b10000")) Is Nothing Then
Exit Sub
End If
If Target.Cells.Count > 1 Then
Exit Sub
End If
If Target.Value = "" Then
Exit Sub
End If

Application.EnableEvents = False
With Target
If .HasFormula = False Then
Select Case Len(.Value)
Case 1 ' e.g., 1 = 00:01 AM
TimeStr = "00:0" & .Value
Case 2 ' e.g., 12 = 00:12 AM
TimeStr = "00:" & .Value
Case 3 ' e.g., 735 = 7:35 AM
TimeStr = Left(.Value, 1) & ":" & _
Right(.Value, 2)
Case 4 ' e.g., 1234 = 12:34
TimeStr = Left(.Value, 2) & ":" & _
Right(.Value, 2)
Case 5 ' e.g., 12345 = 1:23:45 NOT 12:03:45
TimeStr = Left(.Value, 1) & ":" & _
Mid(.Value, 2, 2) & ":" & Right(.Value, 2)
Case 6 ' e.g., 123456 = 12:34:56
TimeStr = Left(.Value, 2) & ":" & _
Mid(.Value, 3, 2) & ":" & Right(.Value, 2)
Case Else
Err.Raise 0
End Select
.Value = TimeValue(TimeStr)
End If
End With
Application.EnableEvents = True
Exit Sub
EndMacro:
MsgBox "You did not enter a valid time"
Application.EnableEvents = True

End Sub