Solved

Populate a listbox on a userform with a dynamic named range

Posted on 2007-04-07
7
874 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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
how to remove error in database 6 38
Need help with a query 14 40
Excel Total at footer of invoice 5 23
vb.net dbnull syntax 1 15
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.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

696 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