Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Populate a listbox on a userform with a dynamic named range

Posted on 2007-04-07
7
Medium Priority
?
1,019 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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 1000 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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

604 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