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 [โฆ]
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 [โฆ]
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 [โฆ]
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 calendar lets you schedule events on the data sheet. Select a cell (calendar date) and events on that day [โฆ]
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 [โฆ]
What's on this page Copy a file Copy and rename a file Rename a file List files in a folder [โฆ]
Auto resize columns as you type
Excel does not resize columns as you type by default as the image above demonstrates. You can easily resize all [โฆ]
2 Responses to โAdd values to a regular drop-down list programmatically [VBA]โ
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.
English
Spanish
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