Add values to a data validation list [VBA]
In this tutorial I am going to show you how to create a drop down list (data validation) in cell C2.
When a value is added, changed or deleted from column A, the drop down list is instantly refreshed.
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
Download excel file
Add values to a data validation list.xlsm
Recommended posts
Use a drop down list to extract and concatenate unique distinct values
Question: Is there a way to have a unique list generated from a list? Meaning I have a sheet that […]
2 Responses to “Add values to a data validation list [VBA]”
Leave a Reply
How to add a formula to your comment:
<code>your formula</code>
Remember to convert less than and larger than signs to html character entities before you post your comment.
How to add VBA code to your comment:
[vb 1="vbnet" language=","]
VBA code
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org
Add picture link to comment.
I enjoy what you guys tend to be up too. Such clever work and
coverage! Keep up the very good works guys I've included you guys to blogroll.
Most customers want to feel like the company
they do business with understands their needs. Yoga is also a great medicine to
kill physical stress. His fifth novel, "Revolution 2020" talks about a group of friends from a small town, Varanasi, who
struggle to find success and love.