Create dropdown list in VBA

[JA3395]

Does anyone know if it is possible to generate a dynamic dropdown-list in a cell by calling a VBA function passing the contents of another cell

Something along the lines of:-

=GenerateList(C4)

Function GenerateList(Byval strClass As String) As [DropDownList]

Dim lbxDropDownList As DropDownList

     Select Case strClass
     Case "A"
          lbxDropDownList.Add "X"
     Case "B"
          lbxDropDownList.Add "Y"
     Case "C"
          lbxDropDownList.Add "Z"
     End Select

     set GenerateList = lbxDropDownList 

End Function

I haven't yet found a Reference that will give me an object something like {DropDownList} do I have to use a Collection and manipulate that?

Thanks in anticipation

J.

 

[SkipVought]

Hi,

You don't need code for dynamic list named ranges. You might also check out the Data>Validation - LIST feature.

The key element is using a Named Range in the ListFillRange of ControlSource.

How can I rename a table as it changes size faq68-1331

Skip,

_[red][/red]

 

[SkipVought]

...and this may also be helpful -- thread707-1372159

Skip,

_[red][/red]

 

[JA3395]

I've tried that solution, but it only works when the dynamic table is on the same sheet as the list.

The structure I have is that my data is on a hidden sheet (2 tables) and when an item is selected from the first table I want the relevant items from the second table to be selectable.

Now, I also want this dropdown list to be repeatable on all rows in a sheet.

This is why I want to generate the 2 lists in VBA so that each row

=GenerateList1()
=GenerateList2(A1)

I had fun yesterday reproducing an object version of a list, but this is not repeatable.

J.

 

[SkipVought]

"...when the dynamic table is on the same sheet as the list..."

au contre! Not if you use a Dynamic Named Range. Your range can be on anther sheet.

"...I also want this dropdown list to be repeatable on all rows in a sheet...?

For Data>Validation, copy the FORMAT to any other cell.


Skip,

_[red][/red]