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!
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,148
Members
448,552
Latest member
WORKINGWITHNOLEADER

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top