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.
Excel Forms Create an Excel user form
Excel Date Picker (Calendar) Uses a VBA class to implement a date picker on the form.
Excel Form Data Validation Make sure the data entered in your form is valid, and let the user know if it isn't.
Excel Forms : Insert, Update and Delete Data Use the Excel user form to insert, update and delete records in a table.
Working code and examples provided in all of these.
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
Enter your email address below to download the sample workbook.
Get your sample workbook here
Hi there, thanks for breaking down the scenario above and for providing the data sheet from which to access your template.
One thing I am struggling to understand thought is where/how the data is being written to the two tables in the secondary sheet?
I can’t seem to locate the exact point at which the data is added to the background tables, would you be able to provide any further clarity?
No worries Ben.
New rows are added to the ‘Staff Names’ and ‘Clients’ lists on the Setup sheet by the code in the CheckForErrors function. Look for the section that starts with the comment ‘If a new entry has been typed in’
I have created a form and connected the tables and the form works magnificently! However, when I attempt to have it add a new entry in a table, it gets put on the worksheet but is not added as a new line to the table.
Here is my code:
Function CheckForErrors() As String
Dim Ctrl As MSForms.Control
For Each Ctrl In WorkstreamForm.Controls
Select Case TypeName(Ctrl)
‘ If a new entry has been typed in
If Ctrl.ListIndex = -1 Then
Ctrl.RowSource = “”
‘ Add to relevant table : Project
If Ctrl.Name = “Project” Then
.ListRows(Worksheets(“Tables”).ListObjects(“Project”).ListRows.Count).Range.Cells(1, 1) = Ctrl.Value
Ctrl.RowSource = “Project”
That code looks pretty much like what I wrote and I can’t see any errors. Can you please post a qs on the forum and include your workbook so I can test your code/form with your data.
I have 12 tables one for each month I want to create a drop that will select the table and the data in my vba from will be entered into the table for the month selected. Can you help with information that will help with this piece of my project.
I strongly encourage you to put all of your data into one sheet and add a column for the month. Spreading your data over different sheets will make it very difficult to work with using the built in Excel tools like formulas and PivotTables.
The ideal data layout is a Tabular format. You will only create headaches with your data spread over multiple sheets.
I pretty much understand how this new code works but it is not in the downloaded workbook (or I may be missing the obvious) and I wonder where you put this code – you don’t say where it goes. My guess would be that it gets added to the existing “Private Sub AddExpenses_Click() routine but I’d like confirmation or correction on that assumption. I’m enjoying this series.
All the code is in the workbook you can download from the bottom of this post. I’ll email you a copy in case something went awry with your download.
The new code is in the
function. Look towards the bottom in the
you’ll see a section that starts with the comment
I just followed the blog to complete my “homework” this afternoon and enjoyed a A1 day as Phil said. Visiting here you’ll get the most informative contents and great services as well.
Thanks Julian 🙂