Add values to a regular drop-down list programmatically [VBA]
In this tutorial I am going to show you how to add values to drop down list in cell C2. This is a regular drop-down list (not form control or active-x) easily created by clicking on the "Data Validation" button on tab "Data" and then on "Data Validation...".
A dialog box appears, select "List" and then click "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.
Explaining the vba code below
- Find the row number of the last cell value in column A.
- Concatenate all values in cell range into a string.
- Add string to drop down list
VBA code
Where to copy the code?
- Copy code below
- Press Alt+F11
- Insert a module
- Paste code into code window
- Return to excel
Sub AddData()
Dim Lrow As Single
Dim AStr As String
Dim Value As Variant
Lrow = Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
For Each Value In Range("A1:A" & Lrow)
AStr = AStr & "," & Value
Next Value
AStr = Right(AStr, Len(AStr) - 1)
With Worksheets("Sheet1").Range("C2").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=AStr
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End Sub
Explaining the vba code below
This code runs subroutine AddData whenever a cell in column A is changed.
Where to copy the code?
- Copy code below
- Press Alt+F11
- Double click sheet1 in project explorer
- Paste code into code window
- Return to excel
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("$A:$A")) Is Nothing Then
Call AddData
End If
End Sub
Weekly Blog EMAIL
Welcome! I am Oscar and here to help you out.Feel free to comment and ask Excel questions.
Make sure you subscribe to my newsletter so you don't miss new blog articles.
Related articles
This article demonstrates a macro that copies values between sheets. I am using the invoice template workbook. This macro copies […]
Open Excel files in a folder [VBA]
This tutorial shows you how to list excel files in a specific folder and create adjacent checkboxes, using VBA. The […]
Split data across multiple sheets [VBA]
In this post I am going to show how to create a new sheet for each airplane using vba. The […]
Identify missing numbers in a column
The image above shows an array formula in cell D6 that extracts missing numbers i cell range B3:B7, the lower […]
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 […]
Change chart data range using a Drop Down List [VBA]
In this article I will demonstrate how to quickly change chart data range utilizing a combobox (drop-down list). The above […]
This workbook contains two worksheets, one worksheet shows a calendar and the other worksheet is used to store events. The […]
Run a Macro from a Drop Down list [VBA]
This article demonstrates how to execute a VBA macro using a Drop Down list. The Drop Down list contains two […]
What's on this page Copy a file Copy and rename a file Rename a file List files in a folder […]
How to create an interactive Excel chart [VBA]
This article describes how to create an interactive chart, the user may click on a button or multiple buttons and […]
2 Responses to “Add values to a regular drop-down list programmatically [VBA]”
Leave a Reply to Gorjan
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.
Sub AddData() Dim Lrow As Single Dim AStr As String Dim Value As Variant Lrow = Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row With Worksheets("Sheet1").Range("C2").Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="=A1:A" & Lrow .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .ShowInput = True .ShowError = True End With End Sub