VBA insert colon between numbers problem

[paul w]

Im using this code to change numbers to a time format without having to insert a ":" every time,

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column < 39 Or Target.Column > 43 Then Exit Sub
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

the problem is it is checking every row and changing cells i dont want changing, i only need it to change rows 6 to 38, how do i modify this code to still check the same columns starting at row 6 and ending with row 38

im also getting a RunTime error13 type mismatch message if i right click and clear contents from those columns the code is checking, anyone any ideas.

 

[paul w]

ive sorted the Row problem out by inserting

If Target.Row < 6 Or Target.Row > 38 Then Exit Sub

seems to work ok,
just the runtime error "type mismatch problem and how to eliminate it,

anyone any ideas

 

[Norie]

Does that error happen when you've changed more than one cell?

If it does, that's the problem.

Try adding, before checking the column/range, something to check only 1 cell has been changed.

Something like this perhaps.

If Target.Cells.Count > 1 Then Exit Sub

 

[paul w]

thanks that worked like a dream, is there any way i can add data validation to those cells, so that a error message appears if any numbers outside of the 24hr clock range or too many or to few numbers are entered.

 

[Rasm]

Thanks Norie - you just inspired me - made me solve one of my many threads nobody answered - hehehe

 

[Scott Huish]

This should take care of that problem, and allow multiple entries:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range, d As Range
Set d = Intersect(Target, Range("AM6:AQ38"))
If d Is Nothing Then Exit Sub
Application.EnableEvents = False
For Each c In d
    If IsNumeric(c) And c <> "" Then c = TimeValue(Format(c, "00\:00"))
Next
Application.EnableEvents = True
End Sub

 

[tryingcake]

I'm going insane with this. And I wasn't sure if I should start a new thread or tag on to this one. Let me know. Here goes...

I had this working wonderfully. I needed to change my system clock to 24-hour format. That's the only change I made. Then all of sudden the formula started changing all my time entries to zeroes.

I had been entering time for instance as 324 and 3:24 would show. Yay! No problem. Now all of a sudden (as seen in the top red circle) it is wanting to add a date changing everything to zero. Even if I enter 1532 I still get zeroes.

I can't get the picture to load. Here is the link:

http://flic.kr/p/9VjNRz

Anyone? Please?? I need my sign in sheets back!!!!

 

[Scott Huish]

Can't really say what's causing your problem unless you post your formulas and/or code. The code I posted above will work with 24 hour time. It would just need a small adjustment to format it that way.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range, d As Range
Set d = Intersect(Target, Range("AM6:AQ38"))
If d Is Nothing Then Exit Sub
Application.EnableEvents = False
For Each c In d
    If IsNumeric(c) And c <> "" Then
        c = TimeValue(Format(c, "00\:00"))
        c.NumberFormat = "hh:mm"
    End If
Next
Application.EnableEvents = True
End Sub

 

[tryingcake]

That's the same macro I used with the exception of my range being C3:F13. I copy and pasted it, actually.

I gave up and just started a new worksheet. All is well now. I would love to know what happened in case it happens again.


Thanks!

 

[tryingcake]

OMG! I recreated it the other day as we discussed. It worked fine. I closed it and did not reopen it until today. It's back to giving me zeroes. I'm so disgruntled.

I copy and pasted the macro given here. What in the world can I be doing wrong? Every time I close the program and open it back up - this happens.