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

Bill Golden
Bill Golden used Ask the Experts™
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.
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Roy CoxGroup Finance Manager

Why use VBA, I would use a Custom Number Format. I'll take a look
Analyst Assistant
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

Roy CoxGroup Finance Manager

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.
Bill GoldenExecutive Managing Member


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?

Roy CoxGroup Finance Manager

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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial