VBA insert colon between numbers problem

P

paul w

Board Regular
Joined
Apr 25, 2010
Messages
195
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.
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Sort by date Sort by votes
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
 
Upvote 0
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.
Code: 
If Target.Cells.Count > 1 Then Exit Sub
 
Upvote 0
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.
 
Upvote 0
Thanks Norie - you just inspired me - made me solve one of my many threads nobody answered:biggrin: - hehehe
 
Upvote 0
This should take care of that problem, and allow multiple entries:

Code: 
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
 
Upvote 0
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!!!!:(
5854298067
 
Upvote 0
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.

Code: 
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
 
Upvote 0
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!
 
Upvote 0
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.:confused:
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
Copying Rows Based on Column Value
Replies
1
Views
160
jolivanes
jolivanes
Agnarr
  • Question Question
VBA problem with creating new sheets
Replies
0
Views
823
Agnarr
Agnarr
J
  • Solved
VBA code is not working
2
Replies
11
Views
493
smozgur
smozgur
Q
  • Solved
Excel existing code flaw
2
Replies
11
Views
237
MARK858
MARK858
R
  • Solved
VBA moving rows
2
Replies
14
Views
1K
Joe4
Joe4

Forum statistics

Threads
1,224,609
Messages
6,179,876
Members
452,949
Latest member
Dupuhini

Share this page

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