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
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