ArryLists for Multi-Column

[UnloadMe]

Good Morning,

I'm trying to Frankenstein some code together to build a report-like userform.

It works for what I need aside from only listing the information on a single column. Hows it setup is that a User can select a list of buttons , depending on what is selected the buttons are assigned a value that is sent to an assigned cell. Based on the value of that cell, the userform will filter the data. The way it is now, works for what I want so multiple people can pick up reports at the same time.

If anyone knows a way to modify this allow the first column to be to be filtered but show the entire row. I'd truly appreciate it.

The code;

Dim i As Long 
Dim arrLists As Variant 
With Sheet8 

Me.ListBox1.Clear 

If.Range("A" & .Rows.Count).End(xlUp).Row > 1 And Trim(Sheet2.Range("AP6").Value) <> vbNullString Then 
   arrLists = .Range("A1:G25" & .Range("A" & .Rows.Count).End(xlUp).Row).Value

For i = LBound(arrLists) To UBound(arrLists)
   If InStr(1, arrLists(i, 1), Trim(Sheet2.Range("AP6").Value), vbTextCompare) Then 
       Me.ListBox1.AddItem arrLists(i, 3)
    End If 
  Next i
End If 

If Me. ListBox1.ListCount = 1 Then Me.ListBox1.Selected(0) = True 
End With 
End Sub

Please let me know if I can provide any further details. i will not be able to send a copy of the workbook or sheet.

[Norie]

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however you need to include code tags around your code.

Please take a moment to add the tags. Posting code between tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

[UnloadMe]

Apologies. I'll fix that now.

[torachan]

set column widths and number of columns in listbox
failing that upload your file with dummy data in it.
torachan

[UnloadMe]

Sir/Ma'am,

I have the Column Widths set on the ListBox1 properties and the Column Count also filled in.

Is this what you were referring to?

Thank you for the help.

[Norie]

Where is the value from Sheet2.Range("AP6").Value coming from?

[UnloadMe]

Norie-

How its setup is that when the User goes to a main page they can click on a button that pulls up a directory.

Each button in that directory has an integer value that changes another cell value. The Sheet2.Range("AP6").Value is a vlookup formula in a worksheet based on the button selected.

The Sheet2.Range("AP6").Value is based based on fixed user list so they can't input anything different and keeps the loop closed. When the code above is ran it will clear that cell value to be ready for the next person. I set it up this so when it's shared, multiple people can pull a filtered list without seeing information that isn't pertinent to them.

Thank you for the help

Edit:

This code was intended to use the TextBox.value inside the userform to filter the listbox. Instead of using the TextBox value, I moved it to a WorkSheet cell. Hopefully this helps

[torachan]

This line is only loading a single column from the array

Me.ListBox1.AddItem arrLists(i, 3)

[UnloadMe]

Correct.

I'm trying to get it to load multiple columns.

[torachan]

easier to answer - upload a desensitized workbook

[jindon]

UnloadMe,

My guess...

    Dim i As Long, ii As Long
    Dim arrLists As Variant
    With Sheet8
    
    Me.ListBox1.Clear
    Me.ListBox1.ColumnCount = 7
    If .Range("A" & .Rows.Count).End(xlUp).Row > 1 And Trim(Sheet2.Range("AP6").Value) <> vbNullString Then
       arrLists = .Range("A1:G" & .Range("A" & .Rows.Count).End(xlUp).Row).Value
    
    For i = LBound(arrLists) To UBound(arrLists)
       If InStr(1, arrLists(i, 1), Trim(Sheet2.Range("AP6").Value), vbTextCompare) Then
           Me.ListBox1.AddItem
           For ii = 1 To 7
               Me.ListBox1.List(Me.ListBox1.ListCount - 1, ii - 1) = arrLists(i, ii)
           Next
        End If
      Next i
    End If
    If Me.ListBox1.ListCount > 1 Then Me.ListBox1.Selected(0) = True
    End With