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 552
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
0
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
0
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
0