vba update dropdown list in Excel

Q163. In Excel, how do I update my DropDown list whenever the sheet is activated?

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

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.

 

You can find similar Excel Questions and Answer hereunder

1) How do I enter a formula in a cell using VBA (using Absolute Reference)?

2) How can I get row count of filtered data?

3) Line break in vba message box in Excel

4) Vba code to password protect workbook in Excel

5) How can I get users to select a file for processing using my macro?

6) How do I get the rank of a number in a list of numbers?

7) How can I hide a sheet completely from users (the sheet should not even appear in Unhide dialog box)?

8) How can I avoid updates to cell values during macro execution?

9) How can I filter and copy only filtered data using VBA?

10) Vba clear the contents of an entire sheet in Excel

11) Import txt file in Excel

12) How can I add a Timestamp after macro execution?

13) How can I set FreezePanes in a certain range using VBA?

14) How can I hide all comments in my WorkSheet using VBA?

15) Vba delete entire row if contains certain text in Excel