Creation of dropdown from dynamic list in VBA

Closed
kmja500
Posts
2
Registration date
Tuesday July 26, 2016
Status
Member
Last seen
July 27, 2016
- Jul 27, 2016 at 04:18 PM
I have been trying to make a Macro that can create a dropdown list from a dynamic list in Rows 1-100. So far I've come up with the following code:

This code is entered in the corresponding sheets code:

Private Sub Worksheet_Change(ByVal Target As range)
If Intersect(Target, Columns("A:A")) Is Nothing Then Exit Sub
Dim lRow As Integer
lRow = range("A" & Rows.Count).End(xlUp).Row
range("A1:A" & lRow).name = "MyList"
End Sub

While this code is entered in a module:

Sub Test()
Range("A1", "A" & ).Name = "MyList"
Cells(1, 3).Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="= & MyList"
.IgnoreBlank = False
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With