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™
on
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
Comment
Watch Question

Do more with

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

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

Roy CoxGroup Finance Manager

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

Author

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
Roy CoxGroup Finance Manager

Commented:
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