VBA insert colon between numbers problem

paul w · Apr 6, 2011

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.

Michael M · Jun 25, 2011

IS that the only code running in your workbook ?
Make sure there is nothing else like an auto-open or other.
Might have been polite to start a new thread as well.

tryingcake · Jun 25, 2011

[FONT=&quot]I remember asking if I should have started a new thread. You're the first one who told me too - in a rather rude way as though I never referred to or asked to begin with. I've noticed when people do start new threads all they get is links back to old threads and semi-rude remarks telling them about searching and such.. We really can't do it correctly either way, huh? So the protocol is to start a new thread... get yelled at and given links... go to such thread and then where do I state that I tried this and it didn't work? On the old thread or the new thread they had started? What are the rules?[/FONT]

Here is a link to my new thread carrying on this request for help:

http://www.mrexcel.com/forum/showthread.php?p=2765968#post2765968

Michael M · Jun 25, 2011

Please accept my apologies if that was your perception. It was simply meant as a response to your original comment about starting a new thread. It certainly wasn't meant to be rude.
The rules are fairly simple and you can have a quick read if you wish by clicking on the first line of my tag. It's very difficult to convey a message in text and keep it in the right context, you are not being yelled at, and I would suggest that in the most part, the comments conveyed to you and others are not intended as "semi rude remarks"
I would suggest that it's a very, very, small minority that would try to be rude to other posters on this forum.
Again, the comment about starting a new thread was just that, a comment, no malice, or insult was intended.

VBA insert colon between numbers problem

paul w

Board Regular

Michael M

Well-known Member

Excel Facts

tryingcake

New Member

Michael M

Well-known Member

Similar threads

Forum statistics

Share this page

VBA insert colon between numbers problem

paul w

Board Regular

Michael M

Well-known Member

Excel Facts

tryingcake

New Member

Michael M

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock