Userform & Dynamic Controls & Class Module

Have the following code:

Userform: UserForm1

Please Login or Register  to view this content.

Class: clsObjHandler

Please Login or Register  to view this content.

Have 5 lines of

Quantity Unit Price Item Price

Name of textbox fields on each line are:

txtBoxQuantity1 txtBoxUnitPrice1 txtBoxItemPrice1
txtBoxQuantity2 txtBoxUnitPrice2 txtBoxItemPrice2
txtBoxQuantity3 txtBoxUnitPrice3 txtBoxItemPrice3
txtBoxQuantity4 txtBoxUnitPrice4 txtBoxItemPrice4
txtBoxQuantity5 txtBoxUnitPrice5 txtBoxItemPrice5

Am using Keydown Event to determine when the user hits the Return key so that the Quantity value isn't used until the full value has been entered. Can't figure out how to get UnitPrice from the Control Collection so that I can then multiple the Quantity * Unit Price for the Row to calculate Item Price for the Row.

Thanks

Hello sarndt01,

When asking for help with complex VBA problems like this one, you should always post your work. Recreating it is not only time consuming but often inaccurate as well.

Please redact any sensitive or confidential information before you upload the workbook.

How To Post Your Workbook

1. At the bottom right of the Reply window, Click the button Go Advanced
2. At the top of the Your Message Window, in the first row of icons, Click the Paperclip icon.
3. Click the Add Files button at the top right in the dialog box. This displays the File Manager dialog.
4. Click the Select Files button at the bottom middle of the dialog.
5. In the new window Find the file you want to upload, select it, and Click Open.
6. You will now be back in the File Manager dialog. Click the bottom Middle button Upload File.
7. Wait until the file has completely uploaded before you exit the File Manager dialog.

You might want to add .LinkedQuantityBox .LinkedUnitBox and .LinkedItemBox properties to your class so you can keep track of which goes with who.

Another approach would be to add a .Index property to your class.
And, instead of putting all your run-time text boxes in the same collection, put the Quantity boxes in one collection, Unit boxes in another and Item a third collection.

collQuantiy(i) matches with collUnit(i) and collItem(i) and collItem(myCustomBox.Index) is the Item box that matches with myCustomBox

I think I found a better approach. Make the class a Trio of added text boxes.
I haven't looked at your sheet, but the attached creates 12 run-time text boxes, 4 Trios, and puts the product of two text boxes in the third of that row.
It could be adapted for your use.

Note that each clsAddedTextBoxTrio (the class module) has three text boxes, each with events.
The calculation is triggered by KeyDown and MouseDown events, but is not reliable. The developer should explicitly call the CalculateAll sub to force calculation of all trios.

In a class Module

Please Login or Register  to view this content.

In the user form's code module

Please Login or Register  to view this content.

I wouldn't use the class, but in the userform I would cycle through the AddedTrios collection, adding the .LinkedExtentionBox es.

If you wanted the code in the Class, I'd do something like

Please Login or Register  to view this content.

You might try something like the attached.
The AddRow button adds a row of text boxes with their check box.
Delete button deletes the selected rows.

Note that the code for the text box calculation is in the user form module, not in the class module.

Please Login or Register  to view this content.

It works for me.
I've changed the sub AddARow to make some properties explicit.

Please Login or Register  to view this content.

You also asked about tabbing between controls, there was code added to address that.

Edit: I just occurred to me that if your font size is 2, then the created text boxes have some unusual property settings. It would be prudent to specify all the properties at time of creation rather than relying on defaults.

The .TabStop properties are set at creation to tab from one row to the next.
Alternatly, you could look at the .Top property of a row and back calculate to find it's row.

.Top = 10 + (rowNumber * 32)
RowNumber = (.Top - 10)/32

The constants in those equations vary with the .Height of the text box so you should check your situation.

The way the collection is structured, AddedRows(I) is immediately above AddedRows(I +1).
The DressRows routine sets the .Top property of the custom class according to it's index within the collection.

Hi - Can you help me with one more issue...

I found code online that I added for Watching Userform events so that I can handle Enter/Exits in my dynamic fields. The code works if I put it in a separate class but fails if I combine it with my Row Control Class. It appears to fail using the Public WithEvents FormCtrl As UserForm1 statement when the corresponding events in the class are compiled/used. I get a circular dependencies between modules error. I've included an updated copy of the sample spreadsheet that I provided last week.

Thanks