Error in Enableevents removing colon in military time

[Ron Abraham]

I have entered the following code in Excel 2013

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
UserInput = Target.Value
If UserInput > 1 Then
NewInput = Left(UserInput, Len(UserInput) - 2) & ":" & Right(UserInput, 2)
Application.EnableEvents = False
Target = NewInput
Application.EnableEvents = True
End If

I am getting an error in "IF USERINPUT > 1 THEN". Can anyone help? Thanks!

 

[Ron Abraham]

I entered 1245 and pressed enter
The cell shows 0:00
I then up arrowed to the cell and it changed to 12:45
And then I got the error.

 

[skywriter]

I entered 1245 and pressed enter
The cell shows 0:00
I then up arrowed to the cell and it changed to 12:45
And then I got the error.

It sounds like part of your problem is what I stated in my post #4.
You are using the SelectionChange event.

If you are in Cell A1 and you type something, then the box around Cell A1 moves to the next cell and if you start typing that next cell will now accept input.

The SelectionChange event doesn't take the value you typed before hitting enter and pass it to the Target.Value, it takes the value of the cell that the box moves into after you press enter.

I think you want the Change event. The change event takes the value that you typed into the original cell and passes it to the Target.Value.

I think this is probably at least part of your problem, but I'll let Rick be the final judge on that.

 

[skywriter]

When I run this code in the Change event and I type something like 300 in a cell it immediately returns 3:00 to the same cell I typed 300 in with no errors.

Private Sub Worksheet_Change(ByVal Target As Range)
UserInput = Target.Value
If UserInput > 1 Then
NewInput = Left(UserInput, Len(UserInput) - 2) & ":" & Right(UserInput, 2)
Application.EnableEvents = False
Target = NewInput
Application.EnableEvents = True
End If
End Sub

 

[tusharm]

As Skywriter has pointed out, you are using the wrong event. Use Change not SelectionChange.

You also need to validate the data a lot more and also ensure that *no matter what* you execute the EnableEvents=True statement.

You also have to allow for integers with less than 4 digits as well as integers that are not times.

Try the lightly tested

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub
    Dim Val: Val = Target.Value
    If Not Application.WorksheetFunction.IsNumber(Val) Then Exit Sub
    If Val < 0 Then Exit Sub
    If Val \ 1 <> Val Then Exit Sub
    If Val \ 100 > 23 Then Exit Sub
    If Val Mod 100 > 59 Then Exit Sub
    On Error Resume Next
    Application.EnableEvents = False
    Target.Value = Val \ 100 & ":" & Val Mod 100
    Application.EnableEvents = True
    End Sub

I have entered the following code in Excel 2013

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
UserInput = Target.Value
If UserInput > 1 Then
NewInput = Left(UserInput, Len(UserInput) - 2) & ":" & Right(UserInput, 2)
Application.EnableEvents = False
Target = NewInput
Application.EnableEvents = True
End If

I am getting an error in "IF USERINPUT > 1 THEN". Can anyone help? Thanks!