Added lines and columns and now custom 0000-00-00 routine does not work. Now, all numeric entries are getting an error.

I had a GREAT spreadsheet until I added some lines and columns.  

In the account number fields (found throughout) when you entered 1234
you got 1234-00-00
When you entered 12
you got 1200-00-00
When you entered 123456
you got 1234-56-00

Suddenly, not only is that routing not working, once active content is enable, any entry to a custom or numeric field gets a Debug error. Sample spreadsheet is attached.

I know the problem is in VBA, but even when I update the routine with the appropriate cell numbers, ranges, I still get the error.
ABC---Last-National-Bank---1801-Ban.xlsm
LVL 1
Bill GoldenExecutive Managing MemberAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Roy CoxGroup Finance ManagerCommented:
Why use VBA, I would use a Custom Number Format. I'll take a look
0
NorieAnalyst Assistant Commented:
There's a typo in your code, you had 037 instead of O37.

Try this.
Private Sub Worksheet_Change(ByVal Target As Range)

    With Target
        If .Cells.Count > 1 Then Exit Sub
        If Not IsNumeric(.Value) Then Exit Sub
        'modify the range H120:H169 to match your input cells
        If Not Intersect(Target, Range("H6:I7,O37:P53,O69:P120")) Is Nothing Then
            Application.EnableEvents = False
            .Value = Replace(Left(.Value & Space(8), 8), " ", "0")
            Application.EnableEvents = True
        End If
    End With
    
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Roy CoxGroup Finance ManagerCommented:
The range is hard coded in the VBA, so it needs amending. I have started to create a Named Range  called ACCOUNTNUM, but I'm not sure what the complete range for account  numbers is.
ABC---Last-National-Bank---1801-Ban.xlsm
0
Bill GoldenExecutive Managing MemberAuthor Commented:
Norie, thank you very much. That solved the immediate problem.

Roy, that is a great idea. Would I be entering the range values somewhere within the worksheet itself?

Bill
0
Roy CoxGroup Finance ManagerCommented:
To create the Named Range, select all the cells that you want to be affected by the code. Then in the Name Box (top left below the Ribbon) type the name for the range.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.