Solved

Populate a listbox on a userform with a dynamic named range

Posted on 2007-04-07
7
829 Views
Last Modified: 2013-12-25
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
Comment
Question by:gevans2000
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 27

Expert Comment

by:MikeToole
ID: 18871848
Assign your Name to the rowsource instead:

ListBox2.RowSource = "Manu"
0
 
LVL 45

Expert Comment

by:patrickab
ID: 18871885
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
 
LVL 45

Expert Comment

by:patrickab
ID: 18872079
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 80

Expert Comment

by:byundt
ID: 18873402
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
 

Author Comment

by:gevans2000
ID: 18876212
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
 
LVL 80

Accepted Solution

by:
byundt earned 250 total points
ID: 18876512
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
 

Author Comment

by:gevans2000
ID: 18880045
Thanks Brad! Your idea works nicely :)
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now