Creation of dropdown from dynamic list in VBA
Solved/Closed
Related:
- Creation of dropdown from dynamic list in VBA
- The joy of creation download - Download - Horror
- My contacts list names - Guide
- Vba case like - Guide
- Number to words in excel without vba - Guide
- Excel vba check if value exists in list - Guide
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
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:
This code will change the range of MyList whenever a change is made in column A.
Best regards,
Trowa
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
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
"= & 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
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
