• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Add Data to Combo Box Drop Down List in Excel Form

You are here: Home / Excel VBA / Add Data to Combo Box Drop Down List in Excel Form
add data to combo box drop down list in excel form
September 4, 2015 by Philip Treacy

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.

Tables to populate drop down lists

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.

Table with blank top row

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

Drop down list with blank row

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.

Specifying the RowSource to populate a drop down list

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.

New client name in 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.

By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Get your sample workbook here

add data to combo box drop down list in excel form

More Excel VBA Posts

automating and emailing pivot table reports

Automating and Emailing Pivot Table Reports

Automate the creation and distribution of pivot table reports with some VBA. Send reports to multiple email recipients using Outlook.
Checking values in range objects with vba

Checking Values in Range Objects With VBA

Use built in tools or your own code to examine the values contained within Range objects in VBA. Sample code to download.
Static variables in VBA

Static Variables in VBA

Variables normally cease to exist once your Sub or Function has ended. But Static Variables allow you to preserve values after your code has finished.
save chart as image

Save Chart as Image

List all the charts in your workbook then select the ones you want to save as either PNG or JPG. Sample workbook and code to download
Clearing Downstream Dependent Data Validation Lists

Clear Downstream Dependent Data Validation Lists

Change one of your data validation lists and clear the values in the other data validation lists dependent on your first choice.
Excel Status Bar

Excel Status Bar

Use the Excel Status Bar to send messages to your users and to show a progress bar for your VBA code
Progress Bar for Excel VBA

Excel Progress Bar for VBA

Create your own progress bar for VBA in Excel. Use it to show that your code is still running, and how long before it finishes.
error handling in vba

Error Handling in VBA

Understand how Excel VBA generates errors, how to control what Excel does when an error occurs, and how to write your own error handling routines.
Finding File Metadata Using FileSystemObject

Finding File Meta Data Using FileSystemObject

Find file meta data like the creation date, last modified date and file size using Windows FileSystemObject in Excel VBA
Automatically Add Items to Data Validation List

Automatically Add Items to Data Validation List

Automatically Add Items to Data Validation List by typing in the new data. Then sort the source list for bonus points

More Userforms Posts

Display All Matches from Search in Userform ListBox

Display All Matches from Search in Userform ListBox

Search a range for all partial and full matches of a string, and display matching records (entire rows) in a userform listbox. Sample code and userform.
dynamic data validation lists in userforms

Dynamic Data Validation Lists in Userforms

Data validation lists using the same source that are dynamically modified to prevent the same choice being made in each list.
Excel Calendar (Date Picker) to Use in Worksheets and Userforms

Multi-Language Excel Calendar (Date Picker) for Worksheets and Userforms

Easy to use, highly customizable and multi-language. This date picker is implemented as a userform that is simple to integrate into your workbook.
search for data with userform

Searching for Data With a User Form

Search a list of records (like a table) using a user form, and then populate the fields of the search form when the record is found.
Hide, Delete, Sort Sheets with VBA in Excel

Hide, Delete and Sort Sheets With VBA Userform

Using a form and some VBA, you can quickly hide, unhide, delete and sort sheets in your Excel workbooks.
Excel forms, insert, update, delete data

Excel Forms – Insert, Update and Delete

Insert, update and delete data in an Excel table with a user form.
excel form data validation

Excel Form Data Validation

Thorough and effective Excel form data validation techniques. Clearly communicate errors to your users without annoying them.
excel date picker

Excel Date Picker

An Excel date picker (calendar) for all versions of Excel, both 32 and 64 bit. An easy to use VBA class, with examples to download in a workbook
excel userforms

Excel Forms

Learn how to create Excel forms for tasks like data entry. Use form controls to gather data, then enter this data into a table on your worksheet.
Excel Form Controls

Excel Form Controls

Add interactivity to your spreadsheets with user friendly form controls like option buttons, combo boxes, check boxes and scrollbars.

More Excel VBA Posts

Display All Matches from Search in Userform ListBox

Display All Matches from Search in Userform ListBox

Search a range for all partial and full matches of a string, and display matching records (entire rows) in a userform listbox. Sample code and userform.
animating excel charts

Animating Excel Charts

Use animation correctly to enhance the story your data is telling. Don't animate your chart just for some eye candy. Sample code and workbook to download.
dynamic data validation lists in userforms

Dynamic Data Validation Lists in Userforms

Data validation lists using the same source that are dynamically modified to prevent the same choice being made in each list.
show report filter pages for power pivot pivottables

Show Report Filter Pages for Power Pivot PivotTables

PivotTables created from Power Pivot can't use the 'Show Report Filter Pages' option. But this piece of VBA allows you to do just that.
charting real time data in excel

Charting Real Time Data in Excel

Receive data in real time and chart the data as it arrives. Can be used to chart things like stock prices or sensor readings. Sample code and workbook
select multiple items from drop down data validation list

Select Multiple Items from Drop Down (Data Validation) List

Choose multiple items from a data validation (drop down) list and store them all in the same cell. Sample workbook with working VBA.
Excel Calendar (Date Picker) to Use in Worksheets and Userforms

Multi-Language Excel Calendar (Date Picker) for Worksheets and Userforms

Easy to use, highly customizable and multi-language. This date picker is implemented as a userform that is simple to integrate into your workbook.
automating and emailing pivot table reports

Automating and Emailing Pivot Table Reports

Automate the creation and distribution of pivot table reports with some VBA. Send reports to multiple email recipients using Outlook.
search for data with userform

Searching for Data With a User Form

Search a list of records (like a table) using a user form, and then populate the fields of the search form when the record is found.
Checking values in range objects with vba

Checking Values in Range Objects With VBA

Use built in tools or your own code to examine the values contained within Range objects in VBA. Sample code to download.


Category: Excel VBATag: Excel VBA, Userforms
Previous Post:Excel Flash FillExcel Flash Fill
Next Post:Excel End of Period DatesCalculate end of period dates in Excel

Reader Interactions

Comments

  1. Ben

    August 7, 2019 at 1:28 pm

    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?

    Thanks

    Ben

    Reply
    • Philip Treacy

      August 8, 2019 at 9:10 pm

      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’

      Regards

      Phil

      Reply
  2. Tom Borg

    July 18, 2019 at 4:38 am

    Phil,
    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)

    Case “ComboBox”
    ‘ 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

    With Worksheets(“Tables”).ListObjects(“Project”)

    .ListRows.Add

    .ListRows(Worksheets(“Tables”).ListObjects(“Project”).ListRows.Count).Range.Cells(1, 1) = Ctrl.Value

    Else

    End With

    Ctrl.RowSource = “Project”

    End If

    End If

    End Function

    Reply
    • Philip Treacy

      July 18, 2019 at 11:12 am

      Hi Tom,

      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.

      Thanks

      Phil

      Reply
  3. Wes

    April 8, 2017 at 3:00 am

    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.

    Reply
    • Mynda Treacy

      April 8, 2017 at 8:28 am

      Hi Wes,

      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.

      Mynda

      Reply
  4. Mike Hyne

    September 16, 2015 at 7:29 am

    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.

    Reply
    • Philip Treacy

      September 16, 2015 at 10:10 am

      Hi Mike,

      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

      CheckForErrors()

      function. Look towards the bottom in the

      Case "ComboBox"

      you’ll see a section that starts with the comment

      ' If a new entry has been typed in

      Cheers

      Phil

      Reply
  5. Julian Chen

    September 5, 2015 at 10:26 pm

    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.

    Reply
    • Philip Treacy

      September 6, 2015 at 9:43 am

      Thanks Julian 🙂

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Featured Content

  • 10 Common Excel Mistakes to Avoid
  • Top Excel Functions for Data Analysts
  • Secrets to Building Excel Dashboards in Less Than 15 Minutes
  • Pro Excel Formula Writing Tips
  • Hidden Excel Double-Click Shortcuts
  • Top 10 Intermediate Excel Functions
  • 5 Pro Excel Dashboard Design Tips
  • 5 Excel SUM Function Tricks
  • 239 Excel Keyboard Shortcuts

100 Excel Tips and Tricks eBook

Download Free Tips & Tricks

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.