Add values to a regular drop-down list programmatically

1. Add values to a regular drop-down list programmatically

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

1.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

1.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

1.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

1.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

1.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

1.6. Explaining the event VBA code

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

Back to top

1.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

2. 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 located on a worksheet using VBA.

Cell B3 contains a regular drop down list which you will find on tab "Data" on the ribbon. The data source used in the drop down list is from column H. I am using an Excel defined Table that grows when new values are added.

Enter a value in cell E3 and press the "Add" button to add the value to the list in column H. You can also remove a value using the "Remove" button, note that this is case-sensitive.

The animated gif below demonstrates how to add or remove values from the list.

What you will learn in this article

• How to insert a regular drop down list.
• How to use an Excel Table as a data source for a drop down list.
• How to convert a data set to an Excel defined Table.
• How to add buttons to a worksheet.
• How to change button text.
• How to create a macro that adds a value to a list
• Name a macro
• Find last non-empty value in list
• Copy entered value to first empty cell in list
• How to create a macro that deletes a specific value in a list
• Iterate through a given column in an Excel defined Table
• Find a value in an Excel defined Table column
• Delete a cell in a list
• How to assign a macro to a button.
• Where the macros are located.

2.1 How to build a drop down list

1. Select the cell you want to use.
2. Go to tab "Data" on the ribbon.
3. Press with mouse on the "Data Validation" button.
4. Select "List".
5. Excel won't let you type a reference to an Excel Table, however, there is a workaround. See below.

2.2 How to use an Excel defined Table as a data source for a drop down list

Use the following formula in order to be able to reference an Excel Table:

2.3 How to create an Excel defined Table

1. Select any cell in the data set you want to convert.
2. Go to tab "Insert" on the ribbon.
3. Press with left mouse button on "Table" button.
   
4. Press with left mouse button on checkbox "My Table has headers".
5. Press with left mouse button on OK button.

2.4 How to add a value to a list using VBA

'Name macro
Sub AddValue()

'Declare variable and data type
Dim i As Single

'Save row number of the first empty cell in column H to variable i
i = Worksheets("Sheet1").Range("H" & Rows.Count).End(xlUp).Row + 1

'Save value entered in cell E3 to first empty cell in column H.
Worksheets("Sheet1").Range("H" & i) = Worksheets("Sheet1").Range("E3")

'Clear cell E3
Worksheets("Sheet1").Range("E3") = ""

End Sub

2.5 How to delete a specific value in a list using VBA

'Name macro
Sub RemoveValue()

'Dimension variables and declare data types
Dim i As Single
Dim Cell As Range

'Save row number of last non-empty cell in column H to variable i
i = Worksheets("Sheet1").Range("H" & Rows.Count).End(xlUp).Row

'Assign the first non-empty cell to object Cell
Set Cell = Worksheets("Sheet1").Range("H" & i)

'Repeat everything between Do and Loop until row of object Cell is equal to 2 
Do Until Cell.Row = 2

    'Check if object cell is equal to value in cell E3 and is not empty
    If Worksheets("Sheet1").Range("E3") = Cell And Cell <> "" Then

        'Delete cell 
        Cell.Delete Shift:=xlUp

        'Clear cell E3
        Worksheets("Sheet1").Range("E3") = ""

        'Stop macro
        Exit Sub
    End If

    'Save object reference 1 row below current object to variable Cell
    Set Cell = Cell.Offset(-1, 0)
Loop

'Show a message box telling the user that the value cant be found in the list
MsgBox "Can´t find value: " & Worksheets("Sheet1").Range("E3")

End Sub

2.6 Where to put the VBA code?

1. Press Alt + F11
2. Press with left mouse button on "Insert" on the top menu.
3. Press with left mouse button on "Module" to add a code module to your workbook.
4. Paste macro to code module.

2.7 Insert button and assign a macro

1. Go to tab "Developer" on the ribbon.
2. Press with left mouse button on "Insert" button.
3. Select "Button" (Form Control).
   
4. Press and hold with left mouse button on worksheet.
5. Drag and release the mouse button to create the button.
6. A dialog box appears, select the macro you want to assign to the button.
7. Press with left mouse button on OK button.

2.8 Change button text

1. Press with right mouse button on on a button.
2. A context menu appears, press with left mouse button on "Assign Macro...".
   
3. A dialog box shows up, select the macro you want to assign.
   
4. Press with left mouse button on OK button.

The macro starts every time the user press with left mouse button ons on the button. Create a new button and assign the second macro.

Move the buttons below cell E3.

Get the Excel file

Add-or-remove-a-value-in-a-drop-down-list1.xlsm

Macro category

Excel calendar
Table of Contents Excel monthly calendar - VBA  Calendar Drop down lists Headers Calculating dates (formula) Conditional formatting Today Dates […]

Save invoice data – VBA
Table of contents Save invoice data - VBA Invoice template with dependent drop down lists Select and view invoice - […]

Working with COMBO BOXES [Form Controls]
This blog post demonstrates how to create, populate and change comboboxes (form control) programmatically. Form controls are not as flexible […]

Excel categories

Comments (7)