Error with code searching database sheet returning to list box

[StartingOut]

Hi, I have this code to search my "Membership Sales" worksheet. but it will not run and highlights .List(.ListCount - 1, 11) = c.Offset(0, (x + 10)).Value and returns the error "could not set the list property" it seams that if I comment that line out it simply fails on the next line as if the rows starting in the double digits are not right.

I'm just not seeing the problem.

"With Me.ListBox1
.AddItem c.Value
.List(.ListCount - 1, 0) = c.Offset(0, x).Address 'record number
.List(.ListCount - 1, 1) = c.Offset(0, x).Value 'surname
.List(.ListCount - 1, 2) = c.Offset(0, (x + 1)).Value 'first name
.List(.ListCount - 1, 3) = c.Offset(0, (x + 2)).Value 'DOB
.List(.ListCount - 1, 4) = c.Offset(0, (x + 3)).Value 'address
.List(.ListCount - 1, 5) = c.Offset(0, (x + 4)).Value 'date spoken to
.List(.ListCount - 1, 6) = c.Offset(0, (x + 5)).Value 'notes
.List(.ListCount - 1, 7) = c.Offset(0, (x + 6)).Value
.List(.ListCount - 1, 8) = c.Offset(0, (x + 7)).Value
.List(.ListCount - 1, 9) = c.Offset(0, (x + 8)).Value
.List(.ListCount - 1, 10) = c.Offset(0, (x + 9)).Value
.List(.ListCount - 1, 11) = c.Offset(0, (x + 10)).Value
.List(.ListCount - 1, 12) = c.Offset(0, (x + 11)).Value
.List(.ListCount - 1, 13) = c.Offset(0, (x + 12)).Value
.List(.ListCount - 1, 14) = c.Offset(0, (x + 13)).Value
.List(.ListCount - 1, 15) = c.Offset(0, (x + 14)).Value
.List(.ListCount - 1, 16) = c.Offset(0, (x + 15)).Value
.List(.ListCount - 1, 17) = c.Offset(0, (x + 16)).Value
.List(.ListCount - 1, 18) = c.Offset(0, (x + 17)).Value
.List(.ListCount - 1, 19) = c.Offset(0, (x + 18)).Value
.List(.ListCount - 1, 20) = c.Offset(0, (x + 19)).Value
.List(.ListCount - 1, 21) = c.Offset(0, (x + 20)).Value


End With"

 

[Jerry Sullivan]

Hi StartingOut,

Listboxes have a limit of 10 "unbound" columns.
If you need to have more, you can use the ListFillRange Property of the Listbox to "bind" the list to a range.

 

[StartingOut]

Hi Jerry, this line item ListFillRange is not listed in my properties list for the list box. My research indicates that the RowSource property could be used but when I try it with any of these is doesn't work I listed my code here as I am sure it will help.
A2:V21
(A2:V21)
("A2:V21")

Private Sub CommandButton1_Click()
'SEARCH
    Dim c As Variant
    Dim Col As Variant
    Dim Data As Variant
    Dim FirstAddx As String
    Dim FoundIt As Range
    Dim I As Integer
    Dim R As Long
    Dim Rng As Range
    Dim RngEnd As Range
    Dim SrcWks As Worksheet
    Dim strFind As String    'what to find
    Dim x As Long


With Worksheets("Membership Sales")
.Unprotect Password:="taylor97"
End With


    Set SrcWks = Worksheets("Membership Sales")
    Set MyData = SrcWks.Range("A2:V20").CurrentRegion






    With Frame1.Controls
        For I = 0 To .Count - 1
            If .Item(I).Value = True Then
                BtnName = .Item(I).Name
                Exit For
            End If
        Next I
    End With


    Select Case BtnName
    Case "OptionButton1"
        strFind = TextBox1
        x = 0 - 4
        Col = 5: Data = TextBox1: GoSub DataSearch
        'Col = 3: Data = TextBox2: GoSub DataSearch
    Case "OptionButton2"
        strFind = TextBox1
        x = 0 - 5
        Col = 6: Data = TextBox1: GoSub DataSearch
    Case "OptionButton3"
        strFind = TextBox1
        'x = 0 - 5
        Col = 1: Data = TextBox1: GoSub DataSearch
    Case "OptionButton4"
        strFind = TextBox1
        x = 0 - 19
        Col = 20: Data = TextBox1: GoSub DataSearch
    End Select


    Exit Sub


DataSearch:
    With SrcWks
        Set Rng = .Cells(2, Col)
        Set RngEnd = .Cells(Rows.Count, Col).End(xlUp)
        Set RngEnd = IIf(RngEnd.Row < Rng.Row, Rng, RngEnd)
        Set Rng = .Range(Rng, RngEnd)
    End With


    Data = Trim(Data)
    Set FoundIt = Rng.Find(What:=Data, After:=Rng.Cells(1, 1), _
                           LookIn:=xlFormulas, LookAt:=CheckBox2.Value + 2, _
                           SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                           MatchCase:=CheckBox1.Value)
    If Not FoundIt Is Nothing Then
        FirstAddx = FoundIt.Address
        If CheckBox3.Value = False Then Exit Sub
        Set FoundIt = Rng.FindNext(FoundIt)
        R = R + 1






        With SrcWks
            If Not .AutoFilterMode Then MyData.AutoFilter
            MyData.AutoFilter Field:=Col, Criteria1:=strFind
            Set Rng = MyData.Columns(Col).Cells.SpecialCells(xlCellTypeVisible)
            Me.ListBox1.Clear
            For Each c In Rng
                With Me.ListBox1
                    .AddItem c.Value
                    .List(.ListCount - 1, 0) = c.Offset(0, x).Address    'record number
                    .List(.ListCount - 1, 1) = c.Offset(0, x).Value  'surname
                    .List(.ListCount - 1, 2) = c.Offset(0, (x + 1)).Value  'first name
                    .List(.ListCount - 1, 3) = c.Offset(0, (x + 2)).Value    'DOB
                    .List(.ListCount - 1, 4) = c.Offset(0, (x + 3)).Value    'address
                    .List(.ListCount - 1, 5) = c.Offset(0, (x + 4)).Value    'date spoken to
                    .List(.ListCount - 1, 6) = c.Offset(0, (x + 5)).Value    'notes
                    .List(.ListCount - 1, 7) = c.Offset(0, (x + 6)).Value
                    .List(.ListCount - 1, 8) = c.Offset(0, (x + 7)).Value
                    .List(.ListCount - 1, 9) = c.Offset(0, (x + 8)).Value
                    '.List(.ListCount - 1, 10) = c.Offset(0, (x + 9)).Value
                    '.List(.ListCount - 1, 11) = c.Offset(0, (x + 10)).Value
                    '.List(.ListCount - 1, 12) = c.Offset(0, (x + 11)).Value
                    '.List(.ListCount - 1, 13) = c.Offset(0, (x + 12)).Value
                    '.List(.ListCount - 1, 14) = c.Offset(0, (x + 13)).Value
                    '.List(.ListCount - 1, 15) = c.Offset(0, (x + 14)).Value
                    '.List(.ListCount - 1, 16) = c.Offset(0, (x + 15)).Value
                    '.List(.ListCount - 1, 17) = c.Offset(0, (x + 16)).Value
                    '.List(.ListCount - 1, 18) = c.Offset(0, (x + 17)).Value
                    '.List(.ListCount - 1, 19) = c.Offset(0, (x + 18)).Value
                    '.List(.ListCount - 1, 20) = c.Offset(0, (x + 19)).Value
                    '.List(.ListCount - 1, 21) = c.Offset(0, (x + 20)).Value


                End With
            Next c
        End With
        '    Me.EnableEvents = True










        '                    Loop While FoundIt.Address <> FirstAddx And Not FoundIt Is Nothing
        '        Me.ListBox1.RowSource = DstWks.UsedRange.Address(external:=True)
    Else
        MsgBox "No Match was found for '" & Data & " '", vbExclamation
    End If
    SrcWks.AutoFilterMode = False
    
    With Worksheets("Membership Sales")
    If Not .AutoFilterMode Then
    .Range("A1").AutoFilter
  End If
    End With


With Worksheets("Membership Sales")
.Protect Password:="taylor97"
End With


End Sub

 

[Jerry Sullivan]

If your ListBox is an ActiveX object, it should have ListFillRange in the Properties list.

Here's syntax that should work in VBA:

    With Me.ListBox1
        .ListFillRange = "A2:V21"
    End With

 

[StartingOut]

Jerry would you be able to pinpoint in my code where I would place this part of code, I have tried a couple without success

 

[Jerry Sullivan]

Jerry would you be able to pinpoint in my code where I would place this part of code, I have tried a couple without success

Sure, I can do that. But first try to run the code separately using a temporary CommandButton to make sure it works by itself.

Private Sub CommandButton2_Click()

    With Me.ListBox1
        .ListFillRange = "A2:V21"
    End With

End Sub

 

[StartingOut]

I just tried and get the error "method or data member not found" and this portion of the code get highlighted
.ListFillRange =

 

[Jerry Sullivan]

I just tried and get the error "method or data member not found" and this portion of the code get highlighted
.ListFillRange =

What version of Excel are you using?
Windows or Mac?

Is it an ActiveX ListBox?
(To check: Developer Tab> Design Mode> Right-Click on Object
The menu items should include "ListBox Object"
If that isn't there and you have an item "Assign Macro" then you are using a Forms Object).

 

[StartingOut]

Yes Jerry it is a forms object, and its 2007,

 

[Jerry Sullivan]

I've been assuming this is a ListBox embedded in a Worksheet.

Is the Listbox in a UserForm instead?