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:
- Option Explicit
- Private Sub Worksheet_Activate()
- Dim i As Long
- ''
- 'Clear the "Employees" ComboBox before updating it.
- ActiveSheet.Employees.Clear
- ''
- 'List of "Employees" is available in Config Sheet in Column A starting from Row 1.
- i = 1
- ''
- 'Loop till the last Employee in list. Add each Employee to ComboBox.
- Do Until ThisWorkbook.Sheets("Config").Cells(i, 1) = ""
- ActiveSheet.Employees.AddItem ThisWorkbook.Sheets("Config").Cells(i, 1).Value
- i = i + 1
- Loop
- End Sub
Description:
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.