Problem with showing more than 10 columns on listbox

[sofas]

Hello, I have this code to search in several specific Excel sheets and show the results to the listbox. But it does not work when the number of columns exceeds 10. Is there a way to show 12 or 13 columns on the listbox? Here's a look at what I'm trying to find in the following code

ivate Sub CommandButton1_Click()
     Dim Found As Range
    Dim Search As Variant
    Dim firstaddress As String, msg As String
    
  Me.ListBox1.Clear
    
    Search = Me.TextBox1.Value
      Do
    If Me.TextBox1 = "" Then Exit Sub
    Loop Until Search <> ""
  Dim sh As Worksheet
For Each sh In Sheets(Array("Sheet1", "Sheet2", "Sheet5", "Sheet8"))
        With sh.UsedRange
            Set Found = sh.Range("a2:j1000").Find(Search, LookIn:=xlValues, lookat:=xlWhole)
            If Not Found Is Nothing Then
                firstaddress = Found.Address
                Do
                 With ListBox1
                 .AddItem
                .List(.ListCount - 1, 0) = sh.Cells(Found.Row, 1).Value
                .List(.ListCount - 1, 1) = sh.Cells(Found.Row, 2).Value
                .List(.ListCount - 1, 2) = sh.Cells(Found.Row, 3).Value
                .List(.ListCount - 1, 3) = sh.Cells(Found.Row, 4).Value
                .List(.ListCount - 1, 4) = sh.Cells(Found.Row, 5).Value
                .List(.ListCount - 1, 5) = sh.Cells(Found.Row, 6).Value
                .List(.ListCount - 1, 6) = sh.Cells(Found.Row, 7).Value
                .List(.ListCount - 1, 7) = sh.Cells(Found.Row, 8).Value
                .List(.ListCount - 1, 8) = sh.Cells(Found.Row, 9).Value
                .List(.ListCount - 1, 9) = sh.Cells(Found.Row, 10).Value
               ' .List(.ListCount - 1, 10) = Found.Address
               '.List(.ListCount - 1, 11) = Sh.Name
            End With
                
                    Set Found = .FindNext(Found)
                Loop While Not Found Is Nothing And Found.Address <> firstaddress

            Else
          MsgBox "Nothing found."
            End If
        End With
        Set Found = Nothing
    Next
End Sub

 

[jolivanes]

Maximum number of columns in listbox

Hi everyone, I was wondering what the maximum number of columns is that one is allowed to use in a listbox. I was trying to put 16 columns in a listbox by using the property column count on the listbox. 7 of these 16 I wanted to have hidden by setting the columnwidth to 0. The reason for this...

www.mrexcel.com

Have a read here.

 

[sofas]

Maximum number of columns in listbox

Hi everyone, I was wondering what the maximum number of columns is that one is allowed to use in a listbox. I was trying to put 16 columns in a listbox by using the property column count on the listbox. 7 of these 16 I wanted to have hidden by setting the columnwidth to 0. The reason for this...

www.mrexcel.com

Have a read here.

Thank you, ...but it does not have the feature of searching using textbox, in addition to searching in several different Sheets

 

[sofas]

Is there any other way that I can search through multiple sheets and show more than 10 columns on listbox

 

[Jaafar Tribak]

Is there any other way that I can search through multiple sheets and show more than 10 columns on listbox

Is that listbox on a userform or on a worksheet ?

One workaround I can think of is to add a second listbox for the extra columns and join it together with the first listbox. Then you could tweak some of the listboxes Properties like giving them a flat visual effect / adjust their sizes/ remove the vertical scrollbar of the left-most listbox etc to make them look like it is one single listbox.

As far as code/behaviour is concerned, you can easily synchronise the two listboxes via their rich set of events.

 

[RoryA]

The point is that you have to use either an array or a range to populate the listbox if you want more than 10 columns; you cannot use AddItem.

You can resize an array as you go and add data to it, or add new rows to a range you use as the source for your listbox. (If you want to use column headers, you will need to use a range).

 

[sofas]

The point is that you have to use either an array or a range to populate the listbox if you want more than 10 columns; you cannot use AddItem.

You can resize an array as you go and add data to it, or add new rows to a range you use as the source for your listbox. (If you want to use column headers, you will need to use a range).

user

MediaFire is a simple to use free service that lets you put all your photos, documents, music, and video in a single place so you can access them anywhere and share them everywhere.

www.mediafire.com

Welcome. Here is a copy of the file. I do not use column headers. I just want to search for a value and show its row data

 

[abdelfattah]

@sofa
if you want to populate 11 or 12 ... columns Why your file doesn't contain that?

' .List(.ListCount - 1, 10) = Found.Address

is important this line ?
actualy I don't think listbox will show cells adresses with this way ?
so I suppose showing like this $c$2 & $F$2.....
I think you need function to do that , but sorry I havE no idea how do that?
anyway I will just try to do that except column to show cells addresses.

 

[sofas]

@sofa
if you want to populate 11 or 12 ... columns Why your file doesn't contain that?

is important this line ?
actualy I don't think listbox will show cells adresses with this way ?
so I suppose showing like this $c$2 & $F$2.....
I think you need function to do that , but sorry I havE no idea how do that?
anyway I will just try to do that except column to show cells addresses.

Welcome . The file includes only 10 columns, column number 11 is the sheet name, number 12 is the cell address

 

[dmt32]

Hi,
As already pointed out, to exceed the 10 column limit of AddItem property of the control you need to use either the List property added from contents of an array (can be Range.Value) or the Rowsource property connected to a Range (only option if you want to display column headers)

For your requirement, a 2D array should do what you want but as you are searching multiple sheets, will need to correctly size the array by counting the total number of matches across all sheets first before populating it.

Not had a lot of time to spend on this & only tested on your sample data – my vain attempts to update your code may therefore need some adjustment / re-thinking but see if this will do what you want

Private Sub CommandButton1_Click()
    Dim sh                  As Worksheet
    Dim firstaddress        As String, SearchAddress    As String
    Dim Found               As Range, wsRangeArr()      As Range
    Dim CountAllMatches     As Long, CountMatch         As Long
    Dim i                   As Long, r                  As Long, c As Long
    Dim Search              As Variant, SearchRange     As Variant
    Dim SearchSheetsArr     As Variant, CopyArr()       As Variant
    
    '----------------------------------------------------------------------------------------------------------
    '                                           SETTINGS
    '----------------------------------------------------------------------------------------------------------
    Const ColCount  As Long = 12
    
    SearchAddress = "A:J"
    SearchSheetsArr = Array("Sheet1", "Sheet2", "Sheet5", "Sheet8")
    '----------------------------------------------------------------------------------------------------------
    
    Search = Me.TextBox1.Value
    If Len(Search) = 0 Then Exit Sub
    
    'loop all sheets in array
    For Each sh In ThisWorkbook.Worksheets(SearchSheetsArr)
        CountMatch = Application.CountIf(sh.Range(SearchAddress), Search)
        If CountMatch > 0 Then
            i = i + 1: ReDim Preserve wsRangeArr(1 To i): Set wsRangeArr(i) = sh.Range(SearchAddress)
            'total count of all matches in ranges
            CountAllMatches = CountAllMatches + CountMatch
        End If
        CountMatch = 0
    Next sh
    
    If CountAllMatches > 0 Then
        'size copy array
        ReDim CopyArr(1 To CountAllMatches, 1 To ColCount)
        
        'search sheets / ranges with matches
        r = 0
        For Each SearchRange In wsRangeArr
            'search range
            Set Found = SearchRange.Find(Search, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
            
                firstaddress = Found.Address
                
                Do
                    'populate array elements
                    r = r + 1
                    For c = 1 To UBound(CopyArr, xlColumns) - 2
                        CopyArr(r, c) = SearchRange.Cells(Found.Row, c).Text
                    Next c
                    
                    CopyArr(r, c) = Found.Address
                    CopyArr(r, c + 1) = SearchRange.Parent.Name
                    
                    Set Found = SearchRange.FindNext(Found)
                    
                Loop While Found.Address <> firstaddress
                
            'clear object variable
            Set Found = Nothing
            
        Next SearchRange
        
    End If
    
    'populate listbox or report no matches
    With Me.ListBox1
        .ColumnCount = IIf(CountAllMatches > 0, ColCount, 1)
        .List = IIf(CountAllMatches > 0, CopyArr, Array("No Match Found"))
        .Font.Size = IIf(CountAllMatches > 0, 9, 24)
        .TextAlign = IIf(CountAllMatches > 0, fmTextAlignLeft, fmTextAlignCenter)
    End With
    
End Sub

Private Sub TextBox1_Change()
    If Len(Me.TextBox1) = 0 Then Me.ListBox1.Clear
End Sub

The updated code first scans all the worksheets in the SearchsheetsArr array & creates a total count of all matches which is used to size the CopyArr. In addition, a second array wsRangeArr stores the sheet(s) range(s) which is used to search only those ranges with matches in the For Next part of your original code.

You will note that I added the “No Match Found” to display in the Listbox but you can change this if you prefer to show a MsgBox.


if all goes well, you should get 12 columns of matching search data from each sheet

Hope Helpful

Dave