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
This post describes how to copy values between sheets. I am using the invoice template sheet. This macro copies rows […]
Open Excel files in a folder [VBA]
This tutorial shows you how to list excel files in a specified 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 […]
Change chart data range using a Drop Down List [VBA]
This blog post demonstrates how to quickly change chart data range. I have created a drop down list (form control) […]
This calendar lets you schedule events on the data sheet. Select a cell (calendar date) and events on that day […]
I found a basic interactive chart on the chitika website and my first thought was if I could do this […]
What's on this page Copy a file Copy and rename a file Rename a file List files in a folder […]
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 multiple […]
Rahul asks: i want to know that when we create a vlookup sheet, and in the name column we enter […]
2 Responses to “Add values to a data validation list [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
Use the img tag, like this: <img src="Insert pic link here">
Welcome! I am 
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.