Solved

Populate a listbox on a userform with a dynamic named range

Posted on 2007-04-07
7
854 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 81

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 81

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

Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

810 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