Creation of dropdown from dynamic list in VBA

[Solved/Closed]
Report
-
 Kasper -
Hello,


I have been trying to make a Macro that can create a dropdown list from a dynamic list in column A. So far I've come up with the following code:

Sub Test()
Range("A1", Range("A65536").End(xlUp)).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
End Sub
However calling MyList doesn't work.

I would appreciate very much if someone can explain to me what i am doing worng.

Thanks in advance


Regards,
Kasper

2 replies

Posts
2805
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 14, 2021
482
Hi Kasper,

Why don't you try this:
Goto data validation, choose list and type =MyList
Now right click on the sheets tab and select view code and paste the following 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

This code will change the range of MyList whenever a change is made in column A.

Best regards,
Trowa
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Just a quick look at the code showed me this questionable line

"= & MyList"
I think you meant
"=" & MyList
Thanks for spotting an error in the code, however changing it didn't solve the problem.

However TrowaD came up with a great solution:

Goto data validation, choose list and type =MyList
Now right click on the sheets tab and select view code and paste the following 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


This code will change the range of MyList whenever a change is made in column A.

A great thanks to TrowaD