<div>
Why use VBA, I would use a Custom Number Format. I'll take a look
</div>


Executive Managing Member

Bill Golden

<div>
The range is hard coded in the VBA, so it needs amending. I have started to create a Named Range &nbsp;called ACCOUNTNUM, but I'm not sure what the complete range for account &nbsp;numbers is.<br />
<a href="https://filedb.experts-exchange.com/incoming/2018/09_w39/1387046/ABC---Last-National-Bank---1801-Ban.xlsm" target="_blank" class="file-inline" title="ABC---Last-National-Bank---1801-Ban.xlsm (106 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>ABC---Last-National-Bank---1801-Ban.xlsm</a>
</div>


ABC---Last-National-Bank---1801-Ban.xlsm

<div>
Norie, thank you very much. That solved the immediate problem.<br />
<br />
Roy, that is a great idea. Would I be entering the range values somewhere within the worksheet itself?<br />
<br />
Bill
</div>


<div>
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.
</div>


<div>
<div class="content wysiwyg-content">
I had a GREAT spreadsheet until I added some lines and columns. &nbsp;<br />
<br />
In the account number fields (found throughout) when you entered 1234<br />
you got 1234-00-00<br />
When you entered 12<br />
you got 1200-00-00<br />
When you entered 123456<br />
you got 1234-56-00<br />
<br />
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.<br />
<br />
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.<br />
<a href="https://filedb.experts-exchange.com/incoming/2018/09_w39/1387036/ABC---Last-National-Bank---1801-Ban.xlsm" target="_blank" class="file-inline" title="Sample Bank Rec presenting errors (106 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>ABC---Last-National-Bank---1801-Ban.xlsm</a>
</div>
</div>


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.

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

Microsoft Office is an integrated suite of applications that includes Outlook, Word, Excel, Access, PowerPoint, Visio and InfoPath, along with a number of tools to assist in making the individual components work together. Coding within and between the projects is done in Visual Basic for Applications, known as VBA.

Microsoft Office

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

Microsoft Excel

Visual Basic is Microsoft’s event-driven programming language and integrated development environment (IDE) for its Component Object Model (COM) programming model. It is relatively easy to learn and use because of its graphical development features and BASIC heritage. It has been replaced with VB.NET, and is very similar to VBA (Visual Basic for Applications), the programming language for the Microsoft Office product line.