VBA - Fill ListBox with an Array

Dsewardj

Board Regular
Joined
Dec 30, 2008
Messages
155
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.
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
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.
 
Upvote 0
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?
 
Upvote 0
Get rid of myArray and obviously the lines which reference it. As I said, you don't need it.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top