Issue in summing textboxes on individual pages on userform

[lateniteNC1]

I have a five page userform (BudgetEntryform) where users enter budget figures into textboxes; each textboxes has its own unique name (not Textbox1, Textbox2, etc). I need to add rows and columns. This is done through a series of VBA statements that look like this:

Please Login or Register  to view this content.

When finished adding all the rows/columns, and thanks for an answer from this forum, i loop through all the textboxes and format for U.S. Currency. The VBA code for this is (in the same sub):

Please Login or Register  to view this content.

The same basic procedures are coded into command buttons on subsequent pages of the userform.

This works fine. My problem and question is:

(1) if the user has to edit the data entered into the textboxes, after the revised number is entered and the subroutine executed by pushing the compute button, the changes are made to row/column totals, but the currency format disappears for all cells except the edited cell(s).

(2) From a VBA standpoint, is there a more efficient way of adding the rows and columns?

[LJMetzger]

Hi Richard,

Great question. You have an excellent physical Spreadsheet Design, but without knowing a few secrets you created a lot of extra work for yourself. Your application lends itself to one set of TextBoxes for each of the 11 'Code Numbers'. The TextBoxes would be named in such a way that they can be easily recognized:
For Example 'TB501R01C02' would be for 'Code Number '501' Row 01 Column 02.

I don't usually deal with MultiPage UserForms, and I liked your design so much, I fell in love with your application, and went a little overboard.

Your data structure is very well behaved, and lends itself to generalization.

This naming convention is useful because UserForm Controls DO NOT have to be accessed explicitly like you have done. Variable Names can be used:

Please Login or Register  to view this content.

That solves problem number 1.

-----------------------------------------

It is time consuming and mistake prone to rename all your TextBoxes. I deleted all your TextBoxes and created them Dynamically at runtime. For example for 'Code Number' 501 in Ordinary Code Module ModBudgetFormDynamicTextBoxes:

Please Login or Register  to view this content.

This solves Problem #2.

-------------------------------

By knowing a few essential items for Each Code Number (Number of Rows, Number of Columns, Starting Row Number on the Spreadsheet) we can create general code for each task such as putting Data In the UserForm, Copying Data From the UserForm to the Spreadsheet, Computing Totals. For example to put Data in the UserForm:

Please Login or Register  to view this content.

That solves Problem #3.

---------------------------------------

The next item wasn't required, but I added it anyway. To handle TextBox Events (Change, Double Click, Enter the TextBox) you either have to explicitly create an Event Handler Routine in the UserForm Code module for each TextBox for Each Event or a 'Class Event' Handler can be used. The 'Class Event Handler' uses 3 or 4 routines to Handle all TextBox Events. It sounds complicated, but you don't have to understand the details, it is cookbook.

See the attached file. Please let me know if there is anything you don't understand, or any changes you want made. I'm probably stuck in the house in NJ for the next 60 days or more.

Lewis

[lateniteNC1]

Blown away by your level of attention to my question. As you can see, some of my technique is due to lack of experience with VBA, thus i take the long way to get to a solution. I knew there were more efficient methods, i simply do not have the expertise at this stage of the VBA learning curve.

In the course of this project i have already learned your insights about naming the textboxes; I will not make that mistake on the next project!

I believe i know enough to actually understand your code but will take some time for me to digest. Look for follow-up in a day or so.

Once again, thank you for your efforts.