Creation of dropdown from dynamic list in VBA

Solved/Closed
Kasper - Oct 6, 2011 at 05:52 AM
 Kasper - Oct 7, 2011 at 01:12 AM
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

Related:

2 responses

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
Oct 6, 2011 at 09:59 AM
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
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Oct 6, 2011 at 08:33 PM
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