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