How to add list boxes and text boxes in Excel VBA Userforms dynamically as needed?

Question

I do not know if this is appropriate to place in stackoverflow despite being VBA related--particularly in the UserForms area, however, I cannot visualize at all how i'm going to code this.

My Excel worksheet is shown below:

The UserForm I created to input the data is as shown below:

However, what I want to achieve is similar to how QuickBooks does it where the Amount Due is automatically distributed among the Expense Accounts without having to input them 1 by 1 as shown in the first image (My excel worksheet). Also, when there are more expense accounts than usual (e.g. 10 expense accounts, Quickbooks will automatically add new rows for that purpose). A sample is shown below:

My main issue is that I do not know how to let UserForms dynamically add more rows if I need to do so. It can be automatically add rows when all previous rows are filled or something as shown in the image below:

Let n be the number of expense accounts

So from having to input n*2 (or in my case 4 values):

Telephone/Insurance Expense: 40,000

Cash in Bank 40,000

Water Expense: 40,000

Cash in Bank: 40,000

I can to simplify it to n+1 inputs (or in my case 3 inputs):

Amount Due: 80,000

Telephone/Insurance Expense: 40,000

Water Expense 40,000

FunThomas · Accepted Answer · 2018-05-23 07:22:19Z

1

A lot of examples and tutorials how to add controls dynamically can be found, for example

how-to-create-controls-dynamically-at-runtime
adding-controls-to-a-frame-in-an-excel-userform-with-vba
vba-userform-basics-add-controls-dynamically-at-run-time

However, depending on the maximum number of lines, maybe it is a better solution to create all the controls at design time, set the Visible-property to false and change it to true when needed.

answered May 23, 2018 at 7:22

FunThomas

31.3k4 gold badges24 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Rik Sportel · Accepted Answer · 2018-05-23 17:51:13Z

You can add controls based on the label click in the UserForm code. You'll have to maintain a count of rows in order to utilize that to position them neatly and to move this label from your example down (otherwise it'll be hidden behind the textboxes you add).

Sample code for the UserForm:

Private rowCount As Integer 'To keep track of rows.
Private Sub UserForm_Initialize()
    rowCount = 1
End Sub

Private Sub LabelAddRow_Click() 'Event handler: Add row on click of label.
    Dim tb As Control
    rowCount = rowCount + 1
    Set tb = Me.Controls.Add("Forms.TextBox.1", "textBox" & rowCount & "left", True) 'Add left TextBox.
    With tb 'Position and size it:
        .Top = 25 + (rowCount * 25) + 10
        .Left = 25
        .Height = 25
        .Width = 100
    End With
    Set tb = Me.Controls.Add("Forms.TextBox.1", "textBox" & rowCount & "right", True) 'Same for the right one:
    With tb
        .Top = 25 + (rowCount * 25) + 10
        .Left = 150
        .Height = 25
        .Width = 100
    End With
    LabelAddRow.Top = LabelAddRow.Top + 25 'Move the label down.
End Sub

You might want to add something to the height of the UserForm too, to make it grow with the added controls. Also you might want to reposition other controls that are below the TextBoxes.

If you want to automatically add rows when the last box has been filled out, you're going to need to add some events to the newly added controls, however that's not the main issue listed in the question.

Edit: The above is obviously based on your last image, since that's the "quick win"; dealing with the events of dynamically added TextBoxes will add additional complexity. If you decide to take that route, I suggest to have a go at this first and post new questions as they come along.

Collectives™ on Stack Overflow

How to add list boxes and text boxes in Excel VBA Userforms dynamically as needed?

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related