Populate an array with items in listbox

[Eclektics]

Hi All,

I have a user form with 2 list boxes, the left box is populated and the right is blank, users will select values on the left and move them to the right list box.

When they hit the command button I want all the selected items in the right list box (ListBox2) to populate an array that I can then use to filter the data in the workbook.

Here is my code so far, it errors on the line 'ListBox2.List = LArray'. Any help would be greatly appreciated.

Private Sub cmdSelect_Click()
Dim LArray As Long
Dim lrow, LRowII As Long
ListBox2.List = LArray
Dim ds, ws As Worksheet
Set ws = Sheet2
Set ds = Sheets("Excluded Data")
lrow = ws.Cells(ws.Rows.Count, "A:A").End(xlUp).Row
LRowII = ds.Cells(ds.Rows.Count, "A:A").End(xlUp).Row
With ds
    .Range("$A:$HH").AutoFilter field:=1, Criteria1:=LArray
    .Range("$A$2:$HH$" & LRowII).SpecialCells(xlCellTypeVisible).Copy Destination:=ws.Range("A" & lrow + 1)
    .Range("$A$2:$HH$" & LRowII).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    .ShowAllData
End With
End Sub

 

[Norie]

If you wanted to populate LArray with the items from ListBox2 it would look like this.

LArray = ListBox2.List

 

[Eclektics]

Hi Norie,

Thanks for replying so quick unfortunately I tried that prior to submitting the thread. When I had the code that way round I got a type mis-match error.

 

[Norie]

Where did you get the type mismatch error?

 

[Fluff]

This

Dim LArray As Long

should be

Dim LArray As Variant

 

[Eclektics]

Thanks for the help peeps, unfortunately still sturggiling. To answer your question Norie it errors on the line

ListBox2.List = LArray

Thanks for your response Fluff but I still got an error, a bit more googling and it advised change the .list to .value but that's not working either, I'm assuming this is because it takes all individual selections as one string. When chaining to variant I go the error ' could not set the list property'. Thanks for the help so far, really appreciate it ​

​

 

[Norie]

If you have changed the declaration of LArray as Fluff suggested then this should work.

LArray = ListBox2.List

If you use .Value then you would only return the selected value in the listbox.

 

[Eclektics]

Thanks Norie, I'd missed changing that part around so I had it as 'Listbox2.list = LArray'

Swapped that around and now it gets passed that part but throws up a new error of 'type-mismatch' on this line

.Range("$A:$HH").AutoFilter field:=1, Criteria1:=LArray

Sorry for adding to the original question but I've spent so much time on this now that every error I see makes me want to give up lol​

_{^{<strike>
</strike>}}​

 

[Norie]

As far as I'm aware when filtering using an array of values you need a 1-dimensional array.

When you populate an array from a listbox you get a 2-dimensional array.

You could try transposing that array but I'm not sure that will work either - filtering by an array of values in code can sometimes be tricky.

.Range("$A:$HH").AutoFilter field:=1, Criteria1:=Application.Transpose(LArray)

 

[Eclektics]

Thank you, I tried that amendment but still the same type mismatch error. It's suprising it's difficult as I thought most Excel users at some point would try to implement something like this.