Vba update dropdown list in Excel

For example, I maintain a list of Employees in a separate "Config" sheet in my WorkBook in Col A starting from Row 1. Then I use a dropdownlist (ActiveX combobox) in subsequent sheet "Report" in the WorkBook to support selection of an Employee.

How can I ensure that the ComboBox is always current (updated to reflect the current list in "Config" sheet).

To do it in Excel, here is the answer:

  1. Option Explicit
  2. Private Sub Worksheet_Activate()
  3. Dim i As Long
  4. ''
  5. 'Clear the "Employees" ComboBox before updating it.
  6. ActiveSheet.Employees.Clear
  7. ''
  8. 'List of "Employees" is available in Config Sheet in Column A starting from Row 1.
  9. i = 1
  10. ''
  11. 'Loop till the last Employee in list. Add each Employee to ComboBox.
  12. Do Until ThisWorkbook.Sheets("Config").Cells(i, 1) = ""
  13. ActiveSheet.Employees.AddItem ThisWorkbook.Sheets("Config").Cells(i, 1).Value
  14. i = i + 1
  15. Loop
  16. End Sub


a) Line 2 is executed whenever the WorkSheet with ComboBox "Employee" is selected thereby running the subsequent code.

b) The code then uses the current data in "Config" sheet to update the ComboBox list. Hence any time the ComboBox is available for use, it has most "current" data.


You can find similar Excel Questions and Answer hereunder

1) How can I convert Column numbers into Column names for use in range definition?

2) How can I save a WorkSheet as a new WorkBook using VBA?

3) Here an explanation about list box and how to control list boxes in Excel VBA

4) Vba length of an array in Excel

5) How to change desktop background in Excel

6) How can I set the Source Data of charts using VBA?

7) Tables in Excel VBA. How you can create table in VBA and work with them with macros

8) How can I create dependent drop down lists?

9) Here some explanations about the MSXML who stands for Microsoft XML core services

10) How to create charts in Excel VBA


Here the previous and next chapter