Creation of dropdown from dynamic list using VBA

kmja500 Posts 2 Registration date Tuesday July 26, 2016 Status Member Last seen July 27, 2016 - Jul 27, 2016 at 04:46 PM
 Blocked Profile - Jul 27, 2016 at 05:34 PM
Hi! If anybody could help me figure this out I would much appreciate it!

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
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="= & MyList"
.IgnoreBlank = False
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With

I would like the data validation dropdown list to be in a separate sheet that where the dynamic list is at.


1 response

Blocked Profile
Jul 27, 2016 at 05:21 PM
ON sub Test, why are you initializing MyList again? You have established that with a workbook change!

Remove the first line and try what you have, as you already have a Mylist defined!

Blocked Profile
Jul 27, 2016 at 05:34 PM
I just thought of this one, too: Build the drop down manually, set the source as your NamedRange, and keep the event of workbook change in place, to re-evaluate the NamedRange. that should be all you need to do!