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.
Table of Contents
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.
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
3. Explaining the VBA code
- Find the row number of the last cell value in column A.
- Concatenate all values in cell range into a string.
- Add string to a drop down list in cell C2.
4. Where to put a regular VBA macro ?
- Copy code above.
- Press Alt+F11.
- Insert a module.
- Paste code into the code window.
- Return to Excel.
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
6. Explaining the event VBA code
This code runs the subroutine AddData if a cell in column A is changed.
7. Where to put the event code?
- Copy the event code above located in section 5.
- Press Alt+F11 to open the Visual Basic Editor (VBE).
- Double press with left mouse button on Sheet1 in project explorer, see the image above.
- Paste code into the code window, see the image above.
- Exit VBE and return to Excel.
Â
Macro category
This article demonstrates how to add or remove a value in a regular drop down list based on a list […]
This article demonstrates how to place values automatically to a table based on two conditions using a short macro. Cell […]
This tutorial shows you how to add a record to a particular worksheet based on a condition, the image above […]
Excel categories
2 Responses to “Add values to a regular drop-down list programmatically”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
this solution "Add values to a regular drop-down list programmatically [VBA]" is not functioning in Excel 365, when the string variable is longer then 255 char's???
There is a limit to 255 characters but if a cell range is used then the limit is 32,767 items.