How to export filtered listbox contents into an excel file?

Waimea

Active Member
Joined
Jun 30, 2018
Messages
465
Office Version
  1. 365
Platform
  1. Windows
I am using an userform with a listbox and I want to export the filtered listbox contents into an excel file.

Code:
myarray = Sheet61.ListObjects("Data").DataBodyRange.Value


With ListBox1
.List = myarray
.ColumnCount = 25
.ColumnHeads = False
End With

This code loads the DataBodyRange.Value into my listbox. I then filter it using the following code:

Code:
Private Sub TextBox1_Change()Application.ScreenUpdating = False
    
    Dim endarr(), ListEndRow As Long, lrows As Long, i As Long, j As Long, k As Long
    If TextBox1.Text = vbNullString Then ListBox1.List = myarray: Exit Sub
                
    ListEndRow = 1
    With Sheet61
        lrows = .ListObjects("Data").DataBodyRange.Rows.Count
        ReDim endarr(1 To lrows, 1 To 21)
        For i = 1 To UBound(myarray)
            If Left(LCase(myarray(i, 5)), LCase(Len(TextBox1.Text))) = LCase(TextBox1.Text) Then
                For j = 1 To 20
                    endarr(ListEndRow, j) = myarray(i, j)
                Next
                ListEndRow = ListEndRow + 1
            End If
        Next
    End With
    ListBox1.List = endarr
    
    Me.TextBox1.BackColor = RGB(231, 125, 0)
    Application.ScreenUpdating = True
End Sub

1. Where should I start to export the filtered listbox to an excel file?

2. How can I edit things in the array? Or should I edit the databodyrange and then reload the array?
 
To be more specific, I have 4 textboxes. I am trying to filter on TextBox1, TextBox2, TextBox3 and TextBox4.

I guess I should try some kind of if clause?
 
Upvote 0

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Before you changed post#8 it was talking about comboboxes, not textboxes, hence I used combos in my example.
Just change them for the relevant text boxes
 
Upvote 0
Hi Fluff,

thank you for your reply.

I have changed your code to textboxes but it seems the

Code:
Like
operator is where the code breaks.

It says Compile Error, Expected expression.
 
Upvote 0
There seems to be a stray comma after the word Like, remove it
 
Upvote 0
Hi Fluff,

thank you for your reply.

The code I have now is:

Code:
'If LCase(Myarray(i, 5)) Like LCase(TextBox1.Text) & "*" & Myarray(i, 6) = LCase(TextBox2.Text) & Myarray(i, 7) = LCase(TextBox3.Text) & Myarray(i, 4) = LCase(TextBox4.Text) Then


However,

when I enter something in the Textbox1, nothing shows up in the listbox.

I am trying to get it working but I am not sure how.

Code:
If LCase(Myarray(i, 5)) Like LCase(TextBox1.Text) & "*" & LCase(Myarray(i, 6)) = LCase(TextBox2.Text) Then

I think that this code will filter Textbox1 AND Textbox2?
 
Last edited:
Upvote 0
I obviously didn't have my brain in gear when I made post#9 it should be like
Code:
If LCase(Myarray(i, 5)) Like LCase(TextBox1.Text) & "*" and Myarray(i, 6) = LCase(TextBox2.Text) and Myarray(i, 7) = LCase(TextBox3.Text) and Myarray(i, 4) = LCase(TextBox4.Text) Then
 
Upvote 0
Hi Fluff,

I think your brain is great!

The code compiles but when I filter on TextBox1 nothing shows in the Listbox.

I have TextBox2 change etc. but I have commented it out for now.

Should this code filter on TextBox1 AND TextBox2 AND TextBox3 AND TextBox4?
 
Upvote 0
Yes it should & with the exception of textbox1 it's looking for an exact match, so if the value in col 6 of the array is uppercase it won't match the Lcase textbox2 value
 
Upvote 0
Hi Fluff,

I can't get it to work.

Is there another way to make 4 TextBoxes work together?

So that when I type in TextBox1 it shows all values that starts with "A".

I then type in TextBox2 and I write "My company" and it shows all values for My Company and All values that starts with "A".

I then type in TextBox3 and it shows "My company", values that starts with "A" and whatever I wrote in TextBox3.

And the same for TextBox4.

Assuming there are matches found in the listbox column.



As of now I am playing with TextBox2_Change

Code:
Private Sub TextBox2_Change
TextBox1_Change
End Sub


I am not sure if I am explaining it clearly?
 
Last edited:
Upvote 0
If you want to do it separately for each textbox, you will need to pull the array from the listbox, not from the sheet
 
Upvote 0

Forum statistics

Threads
1,213,558
Messages
6,114,297
Members
448,564
Latest member
ED38

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