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