Results 1 to 7 of 7

VBA - Fill ListBox with an Array

This is a discussion on VBA - Fill ListBox with an Array within the Excel Questions forums, part of the Question Forums category; I am creating a quick procedure which allows users to select a range of cells in a worksheet. When they ...

  1. #1
    Board Regular
    Join Date
    Dec 2008
    Posts
    155

    Default VBA - Fill ListBox with an Array

    I am creating a quick procedure which allows users to select a range of cells in a worksheet. When they are done selecting the range of cells the values are displayed in a listbox located in a userform. (This macro will help update some metrics reports and the criteria are constantly changing, so I am allowing the user to select the criteria, confirm their selection in the listbox, and then move on from there.) This works great when multiple cells are selected, but if only one cell is selected it throws a mismatch error. Below is my code:

    Code:
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        Dim myArray() As Variant
        Dim Lrow As Long
        Dim Lcol As Long
        Dim x As Long
     
    '   Public variable that populates textbox showing selected range
        TargetRng = Target.Address
        UserForm1.TextBox1.Value = TargetRng
     
    '   Determine number of rows and columns
        Lcol = Target.Columns.Count
        Lrow = Target.Rows.Count
     
    '   Determine if only one cell was selected
        If Lcol + Lrow = 2 Then
            Exit Sub
        Else
    '       Fill array with selected cells
            myArray = Target
            For x = LBound(myArray) To UBound(myArray)
            Next
     
    '       Adjust field columns accordingly
            UserForm1.ListBox1.ColumnCount = Lcol
    '       Fill listbox with selected cells
            UserForm1.ListBox1.List = myArray
        End If
    End Sub
    Now I can determine when the user has only selected one cell, but I do not know how to pass this information into the listbox. Any help would be appreciated.

  2. #2
    Board Regular
    Join Date
    Oct 2007
    Posts
    3,073

    Default Re: VBA - Fill ListBox with an Array

    You don't need an array. Instead of:

    UserForm1.ListBox1.List = myArray

    Use:

    UserForm1.ListBox1.RowSource = Target.Address

    which works for multiple cells and single cells.

  3. #3
    Board Regular
    Join Date
    Dec 2008
    Posts
    155

    Default Re: VBA - Fill ListBox with an Array

    Ok, I adjusted my code but I keep getting an error message Object Required. This is what my code looks like now:

    Code:
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        Dim myArray() As Variant
        Dim Lrow As Long
        Dim Lcol As Long
        Dim x As Long
        
    '   Public variable that populates textbox showing selected range
        TargetRng = Target.Address
        UserForm1.TextBox1.Value = TargetRng
            
    '   Determine number of rows and columns
        Lcol = Target.Columns.Count
        Lrow = Target.Rows.Count
        
    '   Fill array with selected cells
        myArray = Target
        For x = LBound(myArray) To UBound(myArray)
        Next
        
    '   Adjust field columns accordingly
        UserForm1.ListBox1.ColumnCount = Lcol
    '   Fill listbox with selected cells
        UserForm1.ListBox1.List.RowSource = Target.Address
    End Sub
    It should be working?

  4. #4
    Board Regular
    Join Date
    Oct 2007
    Posts
    3,073

    Default Re: VBA - Fill ListBox with an Array

    Get rid of myArray and obviously the lines which reference it. As I said, you don't need it.

  5. #5
    Board Regular
    Join Date
    Dec 2008
    Posts
    155

    Default Re: VBA - Fill ListBox with an Array

    I have kept the array in my code because I want to be able to manipulate the data later. It has no affect on the listbox1.list.rowsource line.

  6. #6
    Board Regular
    Join Date
    Dec 2008
    Posts
    155

    Default Re: VBA - Fill ListBox with an Array

    I have found the solution. I changed the code from:

    Code:
    UserForm1.ListBox1.List.RowSource = Target.Address
    to
    Code:
    UserForm1.ListBox1.RowSource = Target.Address
    and it works fine.

    I wanted to keep the array so I could manipulate the data later, but if only one cell is selected it throws an error. In either case one problem is solved. Thanks for your help.

  7. #7
    Board Regular
    Join Date
    Oct 2007
    Posts
    3,073

    Default Re: VBA - Fill ListBox with an Array

    Ah, okay I didn't spot your error in making the change I suggested.

    To use the array method (notice how myArray is declared) for the selected cell(s), try:

    Code:
    Public TargetRng As String
    
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        Dim myArray As Variant
        Dim Lrow As Long
        Dim Lcol As Long
        Dim r As Long, c As Long
        
    '   Public variable that populates textbox showing selected range
        TargetRng = Target.Address
        UserForm1.TextBox1.Value = TargetRng
            
    '   Determine number of rows and columns
        Lcol = Target.Columns.Count
        Lrow = Target.Rows.Count
        
        If Lcol = 1 And Lrow = 1 Then
        
            'Create single dimension array containing selected cell
            myArray = Array(Target.Value)
            For r = LBound(myArray) To UBound(myArray)
                Debug.Print myArray(r)
            Next
            
            UserForm1.ListBox1.RowSource = ""
            UserForm1.ListBox1.ColumnCount = 1
            UserForm1.ListBox1.AddItem myArray(0)
            
        Else
        
        '   Fill 2-dimensional array with selected cells
            myArray = Target
            For r = LBound(myArray) To UBound(myArray)
                For c = 1 To Lcol
                    Debug.Print myArray(r, c)
                Next
            Next
            
        '   Adjust field columns accordingly
            UserForm1.ListBox1.ColumnCount = Lcol
        '   Fill listbox with selected cells
            UserForm1.ListBox1.RowSource = Target.Address
        End If
        
    End Sub
    
    Private Sub CommandButton1_Click()
        UserForm1.Show vbModeless
    End Sub

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com