Insert Colon and Still Have Date in the Same Column

[jsnyder57]

Hi All I have a scheduler that contains a date and several rows containing time in the same column. I am currently using the following VBA code to insert a colon in the correct spot

Private Sub Worksheet_ChangeDate(ByVal Target As Range)
If Intersect(Target, Range("B:B")) Is Nothing Then Exit Sub
UserInput = Target.Value
If UserInput > 99
NewInput = Left(UserInput, Len(UserInput) - 2) & ":" & Right(UserInput, 2)
Application.EnableEvents = False
Target = NewInput
Application.EnableEvents = True
End If
End Sub

And the code inputs a colon in between the 20 and 13 for the year. I tried using the next code, but none of the code works (we tried using msgboxes to test the code and none of the boxes popped up)

Private Sub Worksheet_ChangeDate(ByVal Target As Range)
If Intersect(Target, Range("B:B")) Is Nothing Then Exit Sub
UserInput = Target.Value
If UserInput > 99 Then
NewInput = Left(UserInput, Len(UserInput) - 2) & ":" & Right(UserInput, 2)
ElseIf UserInput = 2013 Then
NewInput = Left(UserInput, Len(UserInput) - 2) & "/" & Mid(UserInput, 2) & "/" & Right(UserInput, 2)
Application.EnableEvents = False
Target = NewInput
Application.EnableEvents = True
End If
End Sub

Does anyone have any suggestions to make this code work?

Thanks

 

[jsnyder57]

Wow thank you so much Scott and Rick for the great code/info. I am just now learning VBA and I am trying to goto it as much as possible now (Less likely for my users to corrupt my formulas). MrExcel has been a great place for me to go find answers/solutions.