Error in Enableevents removing colon in military time

Ron Abraham

New Member
Joined
Dec 3, 2011
Messages
15
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!
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

skywriter

Well-known Member
Joined
Feb 15, 2014
Messages
1,642
Here is a little bit of information that you may or may not be aware of. I know I wasn't.

I was not familiar with the SelectionChange event, so I did an experiment to find out when it fired and what it does.

The event fires when you move from one cell to another. If I am in Cell A1 and I move to Cell A2 the event fires. The value returned from the target is not from Cell A1, but from Cell A2 if there is a value in Cell A2.

So if Cell A1 value is 1 and Cell A2 vlaue is 2 and I move A1 to A2 the SelectionChange event fires and the value returned with Target.Value is 2 from Cell A2.

I bring this up because I am assuming the user is entering a value in a cell and hitting enter and you might be expecting that value to be returned via Target.Value and this may be where you issue is. If not I'm always looking to learn something.

The Change event fires when you type a number into a cell and hit enter and the value you entered in the cell is passed to Target.Value.
 

Ron Abraham

New Member
Joined
Dec 3, 2011
Messages
15
My error message is Run Time Error '13'

I was entering 1245 and hoping it would provide 12:45

It returns 0.00 - then if I move to the cell below and back up to the cell - it changes the entry to 12:45 and then I get the error message

I appreciate your help -Ron A.
 

Ron Abraham

New Member
Joined
Dec 3, 2011
Messages
15

ADVERTISEMENT

Thanks for the valuable info - Ron A.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,334
Office Version
  1. 2010
Platform
  1. Windows
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 = CDate(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!
See if adding the part I show in red makes a difference.
 

Ron Abraham

New Member
Joined
Dec 3, 2011
Messages
15
I am still getting an error. It is: Run-time eror 13:
and below that is Type mismatch

But the cells that is highlighted in yellow is: If UserInput > 1 Then

Appreciate your help. Ron A.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,334
Office Version
  1. 2010
Platform
  1. Windows
I am still getting an error. It is: Run-time eror 13:
and below that is Type mismatch

But the cells that is highlighted in yellow is: If UserInput > 1 Then
I'll go back to the question I asked you in Message #3... exactly what is in UserInput at the time this error is generated?
 

Watch MrExcel Video

Forum statistics

Threads
1,109,461
Messages
5,528,922
Members
409,847
Latest member
Foster034
Top