How to export filtered listbox contents into an excel file?

[Waimea]

I am using an userform with a listbox and I want to export the filtered listbox contents into an excel file.

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:

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?

 

[Waimea]

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?

 

[Fluff]

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

 

[Waimea]

Hi Fluff,

thank you for your reply.

I have changed your code to textboxes but it seems the

Like

operator is where the code breaks.

It says Compile Error, Expected expression.

 

[Fluff]

There seems to be a stray comma after the word Like, remove it

 

[Waimea]

Hi Fluff,

thank you for your reply.

The code I have now is:

'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.

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?

 

[Fluff]

I obviously didn't have my brain in gear when I made post#9 it should be like

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

 

[Waimea]

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?

 

[Fluff]

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

 

[Waimea]

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

Private Sub TextBox2_Change
TextBox1_Change
End Sub

I am not sure if I am explaining it clearly?

 

[Fluff]

If you want to do it separately for each textbox, you will need to pull the array from the listbox, not from the sheet