In this tutorial, I am going to show you how to add values to a drop down list programmatically in cell C2.

1. How to insert a regular drop-down list

This is a regular drop-down list (not form control or active-x) easily created by press with left mouse button oning on the "Data Validation" button on tab "Data" and then on "Data Validation...".

A dialog box appears, select "List" and then press with left mouse button on "OK" button. You don't need to specify a source range, the macro takes care of that.

When a value is added, changed or deleted in column A, the drop-down list is instantly refreshed based on event code and a macro.

Back to top

2. Add values to drop-down list - VBA macro

'Name macro
Sub AddData()

'Dimension variables and declare data types
Dim Lrow As Single
Dim AStr As String
Dim Value As Variant

'Find last non-empty cell in column A and save row number to variable Lrow
Lrow = Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row

'Iterate through cells in column A until last non-empty cell specified in variable Lrow
For Each Value In Range("A1:A" & Lrow)

    'Save value in each cell to string variable AStr and use a comma as a delimiting character
    AStr = AStr & "," & Value

'Continue with next value in column A
Next Value

'Remove last character in string variable AStr
AStr = Right(AStr, Len(AStr) - 1)

'Apply data validation to cell C2 in worksheet Sheet1
With Worksheets("Sheet1").Range("C2").Validation

    'Remove old drop-down list
    .Delete

    'Add a new drop down list and populate with values from string variable Astr
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:=AStr

    'Ignore blank values
    .IgnoreBlank = True

    'Enable drop-down
    .InCellDropdown = True

    'No input title (blank)
    .InputTitle = ""

    'No error title (blank)
    .ErrorTitle = ""

    'No input message (blank)
    .InputMessage = ""

    'No error message (blank)
    .ErrorMessage = ""
    
    .ShowInput = True
    .ShowError = True
End With

End Sub

Back to top

3. Explaining the VBA code

1. Find the row number of the last cell value in column A.
   
2. Concatenate all values in cell range into a string.
3. Add string to a drop down list in cell C2.
   

Back to top

4. Where to put a regular VBA macro ?

1. Copy code above.
2. Press Alt+F11.
3. Insert a module.
4. Paste code into the code window.
5. Return to Excel.

Back to top

5. Event VBA code

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("$A:$A")) Is Nothing Then
    Call AddData
End If

End Sub

Back to top

6. Explaining the event VBA code

This code runs the subroutine AddData if a cell in column A is changed.

Back to top

7. Where to put the event code?

1. Copy the event code above located in section 5.
2. Press Alt+F11 to open the Visual Basic Editor (VBE).
3. Double press with left mouse button on Sheet1 in project explorer, see the image above.
4. Paste code into the code window, see the image above.
5. Exit VBE and return to Excel.

Back to top

Get the Excel file

Add-values-to-a-data-validation-list.xlsm

Back to top

Macro category

Add or remove a value in a drop down list programmatically
This article demonstrates how to add or remove a value in a regular drop down list based on a list […]

Add values to a two-dimensional table based on conditions [VBA]
This article demonstrates how to place values automatically to a table based on two conditions using a short macro. Cell […]

Add values to worksheets based on a condition [VBA]
This tutorial shows you how to add a record to a particular worksheet based on a condition, the image above […]

Excel categories

Comments (2)