Add Data to Combo Box Drop Down List in Excel Form

If you've been following the previous posts in this series you'll know that by now we have a user form that allows us to enter data into a table, and allows us to update and delete existing records.

If you haven't read the other posts about Excel Forms, here are the links.

Two of the pieces of data that we enter via the form are a Client Name and a Staff Name, and these are presented as drop down lists (combo box form controls), populated from two tables.

Now let's say we are entering data with the form but we have just visited a new customer, the A1A Car Wash. Their business name isn't in the list available to us so we need to add it.

We don't want to have to go and add the new client to the Clients table by hand. It would be better if we could do this from the form itself.

You could select any Client Name from the list and then type over it, but it's probably clearer to someone using the form if you have a blank entry in the list and type over that.

To achieve this we just add a row to the top of the Clients table, remembering we are using this table to populate the Client Name drop down list.

Now when we click on the arrow to open the list in our form, the first value is blank.

The Combo Box ListIndex

Now's a good time to mention the Listindex. The ListIndex is a numerical value that indicates what has been chosen from the drop down list. If we choose Wayne Enterprises from our new list with the blank first row, the ListIndex is 1, as the first entry in the list is index 0. Stark Industries is 2 and so on

If we choose the blank entry, our ListIndex is 0, however when we type over it, the ListIndex becomes -1. This is because whatever we are typing is not a member of the Clients table.

In our VBA code we use the ListIndex to check if a new entry has been typed in.

If Ctrl.ListIndex = -1 Then
                    
    Ctrl.RowSource = ""
                        
    ' Add to relevant table : Staff or Clients
    If Ctrl.Name = "ClientName" Then
                        
         With Worksheets("Setup").ListObjects("Clients")
                            
             .ListRows.Add
             .ListRows(Worksheets("Setup").ListObjects("Clients").ListRows.Count).Range.Cells(1, 1) = Ctrl.Value
                                
         End With
                            
         Ctrl.RowSource = "Clients"
                            
    Else
                        
         With Worksheets("Setup").ListObjects("Staff")
                            
             .ListRows.Add
             .ListRows(Worksheets("Setup").ListObjects("Staff").ListRows.Count).Range.Cells(1, 1) = Ctrl.Value
                                
         End With
                            
         Ctrl.RowSource = "Staff"
                        
    End If
                        
End If

Excel Throws a Wobbly

It's at this point that Excel decides to crash (at least it crashed for me), unless you prevent the crash before it happens.

We are using tables to populate both of our drop down lists on the form, and we specify this by entering the name of the table in the RowSource of the control : the Staff table for the Staff Name list, and the Clients table for the Client Name list.

If you're not sure how to use a table to populate a drop down list on a form watch the video on my Excel Forms post. At 15m 40s into the video I explain how to do this.

Excel doesn't like it if you try to alter a table after you've specified that table as the RowSource for the combo box control, it crashes.

To avoid this, I'm using the command Ctrl.RowSource = "" to disconnect the table from the control. I can then add a new record into the table and reconnect the table to the control using Ctrl.RowSource = "Clients", or Ctrl.RowSource = "Staff" for the Staff Names list.

Adding a New Client

Select the empty row from the Client Name drop down. Type in your Client Name which is A1A Car Wash for me. Complete the rest of the form and click on Add.

You should now have a new record in your Expense table and if you check the Clients table, the new Client Name you typed in should be at the bottom of the table.

The process for adding new Staff Names is exactly the same.

We can now add records to our Expense table, update or delete those records, and add new Client and Staff Names all from the same form.

Download the Code