Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1202
  • Last Modified:

Populate a listbox on a userform with a dynamic named range

Using Excel 2003 I have created a userform with a couple of listboxes.

These listboxes are populated with lists whose lengths vary depending on other variables.

When I first created the the wolrkbook I was using named ranges (input by insert->names within excel, not vba). Now I want to modify the workbook (for efficiency and general tidyness) to use dynamic named ranges.

I have now removed my name definitions from within excel and I have begun to declare them in the Workbook_Open() sub using code such as:

ActiveWorkbook.Names.Add Name:="Manu", RefersTo:= _
       "=OFFSET('Master Data'!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)"

When I want to update the listbox contents with the data in the dynamic named range "Manu", I use the following code:

ListBox2.List = Range("Manu")

This all worked fine when I used static named ranges, but now I have redefined them as dynamic I get the following error when the userform is created:

Run-time error '381'
Could not set the list property. Invalid property array index.

I'd be grateful if anyone could point me in the right direction for populating a listbox within a useform with a dynamic named range.

Cheers!

0
gevans2000
Asked:
gevans2000
  • 2
  • 2
  • 2
  • +1
1 Solution
 
MikeTooleCommented:
Assign your Name to the rowsource instead:

ListBox2.RowSource = "Manu"
0
 
patrickabCommented:
gevans2000,

The other easier way is to use dynamic ranges from within VBA to define the number of elements to be placed in the dropdown in a UserForm - like this:

Sub dropdown()
    Dim NumElements As Integer
    Dim i As Integer
   
    Sheet1.Activate
   
    Cells.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    Sheet2.[A1].Select
   
        'The next line establishes the number of elements for the dropdown
    NumElements = Sheet1.[A65536].End(xlUp).Row

    For i = 0 To NumElements - 1
        UserForm1.ComboBox1.AddItem Cells(i + 1, 1).Value
    Next i
       
    With UserForm1
              'Combo box values are ListIndex values
        .ComboBox1.BoundColumn = 0
              'Set combo box to first entry
        .ComboBox1.ListIndex = 0
        .ComboBox1.Style = fmStyleDropDownList
        .Show
    End With

End Sub

It's much easier to do it this way as you do not need to name ranges nor make named dynamic ranges - which all take time to set up.

Hope that helps

Patrick
0
 
patrickabCommented:
gevans2000,

Let's try that again:

Sub dropdown()
    Dim NumElements As Integer
    Dim i As Integer
   
    Sheet1.Activate
    Sheet1.[A1].Select
   
        'The next line establishes the number of elements for the dropdown
    NumElements = Sheet1.[A65536].End(xlUp).Row

    For i = 0 To NumElements - 1
        UserForm1.ComboBox1.AddItem Cells(i + 1, 1).Value
    Next i
       
    With UserForm1
              'Combo box values are ListIndex values
        .ComboBox1.BoundColumn = 0
              'Set combo box to first entry
        .ComboBox1.ListIndex = 0
        .ComboBox1.Style = fmStyleDropDownList
        .Show
    End With

End Sub

It's much easier to do it this way as you do not need to name ranges nor make named dynamic ranges - which all take time to set up.

Hope that helps

Patrick
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
byundtCommented:
Another way to do it is to assign the .Value property of the dynamic named range to your listbox:
ActiveSheet.ListBox2.List = Range("Manu").Value

This statement fails without the .Value in Excel 2003. It succeeds with .Value, both in a userform and in an ActiveX control on the worksheet.
0
 
gevans2000Author Commented:
Hi,

Thanks for all your comments and tips. It seems (as is unfortunately often the case!) that I had made a silly error in the code below:

ActiveWorkbook.Names.Add Name:="Manu", RefersTo:= _
       "=OFFSET('Master Data'!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)"

It should have read:

ActiveWorkbook.Names.Add Name:="Manu", RefersTo:= _
       "=OFFSET('Master Data'!$A$1,0,0,COUNTA('Master Data'!$A:$A),1)"

So that solved one problem and the code that byundt suggested works fine....

...unless the number of items in the dynamic range "Manu" is one. If there is only one item to populate the listbox (i.e. in Master Data'!$A$1, but all cells below are blank) then I get the "Run-time error '381'" again.

I'd be grateful if anyone can explain what's happening here.

Thanks.
0
 
byundtCommented:
The List property is expecting a two-dimensional array--but .Value of a single cell does not produce an array, hence the error message. To add a single item, you need to use the .AddItem method. If there is more than one item, .AddItem won't work because it requires a single dimension array--and .Value creates a two dimension array. Both methods fail if there are no items in your list, so there needs to be a test for that possibility as well. Bottom line: you need to use both the .AddItem and .List approaches to populate your listbox, depending on the number of items in the list.  

The following code tolerates 0, 1 or more than 1 items in column A. Note that you must not have any blank cells in your list.

Private Sub UserForm_Initialize()
If [COUNTA('Master Data'!$A:$A)] > 0 Then
    ActiveWorkbook.Names.Add Name:="Manu", RefersTo:= _
           "=OFFSET('Master Data'!$A$1,0,0,COUNTA('Master Data'!$A:$A),1)"
    Select Case [Manu].Cells.Count
    Case 1
        ListBox2.AddItem Range("Manu").Value
    Case Is > 1
        ListBox2.List = Range("Manu").Value
    End Select
End If
End Sub


Brad
0
 
gevans2000Author Commented:
Thanks Brad! Your idea works nicely :)
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 2
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now