FindMethod on VBA Userform specialCells property not working properly adding to listbox please help

[thegil17]

I have a find records function on my database. The first part works perfectly. It will check for the search string, find how many instances of it there are in my datarange, then if the user clicks Okay it will findall records and add them to a listbox. Here is my Code maybe one of you can see what is wrong where I cannot. Why SpecialCells is not working properly in the findall method. I will list the listbox method and find method as well. And in the form initialize I've already compensated for the 27 columns and their width in the listbox. The autofilter works 100% but cells besides the visible ones after autofilter are added to the listbox, can anyone tell me why? I will even add the form initialization code so you can see if it is something on that end. I used code tags and do not know why the keywords didn't change color. This is my first post. Apologies

[VB]
Private Sub FindBtn_Click()
Dim strFind
    
    Dim f As Integer
    
    
'    imgFolder = ThisWorkbook.Path & Application.PathSeparator & "images" & Application.PathSeparator
    strFind = Me.TextBox1.Value    'what to look for
    
    With MyData
    .Range("A3:AA3").AutoFilter
        Set c = .Find(strFind, LookIn:=xlValues)
        If Not c Is Nothing Then    'found it
              
              With Me    'load entry to form
                .TextBox1.Value = c.Offset(0, 0).Value
                .ComboBox1.Value = c.Offset(0, 1).Value
                .ComboBox2.Value = c.Offset(0, 2).Value
                .TextBox2.Value = c.Offset(0, 3).Value
                .TextBox3.Value = c.Offset(0, 4).Value
                .TextBox4.Value = c.Offset(0, 5).Value
                .TextBox5.Value = c.Offset(0, 6).Value
                .TextBox6.Value = c.Offset(0, 7).Value
                .ComboBox3.Value = c.Offset(0, 8).Value
                .ComboBox4.Value = c.Offset(0, 9).Value
                .TextBox7.Value = c.Offset(0, 10).Value
                .TextBox8.Value = c.Offset(0, 11).Value
                .TextBox9.Value = c.Offset(0, 12).Value
                .TextBox10.Value = c.Offset(0, 13).Value
                .TextBox11.Value = c.Offset(0, 14).Value
                .TextBox12.Value = c.Offset(0, 15).Value
                .TextBox13.Value = c.Offset(0, 16).Value
                .TextBox14.Value = c.Offset(0, 17).Value
                .TextBox15.Value = c.Offset(0, 18).Value
                .TextBox16.Value = c.Offset(0, 19).Value
                .TextBox17.Value = c.Offset(0, 20).Value
                .TextBox18.Value = c.Offset(0, 21).Value
                .TextBox19.Value = c.Offset(0, 22).Value
                .TextBox20.Value = c.Offset(0, 23).Value
                .TextBox21.Value = c.Offset(0, 24).Value
                .TextBox22.Value = c.Offset(0, 25).Value
                .TextBox23.Value = c.Offset(0, 26).Value
                .AmdBtn.Enabled = True     'allow amendment or
                .DelBtn.Enabled = True    'allow record deletion
                .NewBtn.Enabled = False      'don't want to duplicate record
            
                r = c.Row
                
                f = 0
            End With
            FirstAddress = c.Address
            Do
                f = f + 1    'count number of matching records
                Set c = .FindNext(c)
            Loop While Not c Is Nothing And c.Address <> FirstAddress
            If f > 1 Then
                Select Case MsgBox("There are " & f & " instances of " & strFind, vbOKCancel Or vbExclamation Or vbDefaultButton1, "Multiple entries")


                Case vbOK
                    FindAll
                    Me.ListBox1.Visible = True
                    Me.Width = 1182.75
                Case vbCancel
                    'do nothing
                End Select
                


            End If
        Else: MsgBox strFind & " not listed"    'search failed
        End If
    End With
End Sub


Private Sub ListBox1_Click()
With Me.ListBox1
        
        If .ListIndex = -1 Then    'not selected
            MsgBox " No selection made"
        ElseIf .ListIndex >= 1 Then    'User has selected
            r = Val(.List(.ListIndex, .ColumnCount - 1))
        End If
    End With


    With Me
        .TextBox1.Value = .ListBox1.List(.ListBox1.ListIndex, 0)
        .ComboBox1.Value = .ListBox1.List(.ListBox1.ListIndex, 1)
        .ComboBox2.Value = .ListBox1.List(.ListBox1.ListIndex, 2)
        .TextBox2.Value = .ListBox1.List(.ListBox1.ListIndex, 3)
        .TextBox3.Value = .ListBox1.List(.ListBox1.ListIndex, 4)
        .TextBox4.Value = .ListBox1.List(.ListBox1.ListIndex, 5)
        .TextBox5.Value = .ListBox1.List(.ListBox1.ListIndex, 6)
        .TextBox6.Value = .ListBox1.List(.ListBox1.ListIndex, 7)
        .ComboBox3.Value = .ListBox1.List(.ListBox1.ListIndex, 8)
        .ComboBox4.Value = .ListBox1.List(.ListBox1.ListIndex, 9)
        .TextBox7.Value = .ListBox1.List(.ListBox1.ListIndex, 10)
        .TextBox8.Value = .ListBox1.List(.ListBox1.ListIndex, 11)
        .TextBox9.Value = .ListBox1.List(.ListBox1.ListIndex, 12)
        .TextBox10.Value = .ListBox1.List(.ListBox1.ListIndex, 13)
        .TextBox11.Value = .ListBox1.List(.ListBox1.ListIndex, 14)
        .TextBox12.Value = .ListBox1.List(.ListBox1.ListIndex, 15)
        .TextBox13.Value = .ListBox1.List(.ListBox1.ListIndex, 16)
        .TextBox14.Value = .ListBox1.List(.ListBox1.ListIndex, 17)
        .TextBox15.Value = .ListBox1.List(.ListBox1.ListIndex, 18)
        .TextBox16.Value = .ListBox1.List(.ListBox1.ListIndex, 19)
        .TextBox17.Value = .ListBox1.List(.ListBox1.ListIndex, 20)
        .TextBox18.Value = .ListBox1.List(.ListBox1.ListIndex, 21)
        .TextBox19.Value = .ListBox1.List(.ListBox1.ListIndex, 22)
        .TextBox20.Value = .ListBox1.List(.ListBox1.ListIndex, 23)
        .TextBox21.Value = .ListBox1.List(.ListBox1.ListIndex, 24)
        .TextBox22.Value = .ListBox1.List(.ListBox1.ListIndex, 25)
        .TextBox23.Value = .ListBox1.List(.ListBox1.ListIndex, 26)
        .AmdBtn.Enabled = True      'allow amendment or
        .DelBtn.Enabled = True     'allow record deletion
        .NewBtn.Enabled = False       'don't want duplicate
        
    End With
End Sub

Sub FindAll()
    Dim wesTemp As Worksheet
    Dim strFind As String    'what to find
  
    
strFind = Me.TextBox1.Value
Dim rng1 As Range






    If Not ws.AutoFilterMode Then Range("A3:AA3").AutoFilter
    Set rng1 = ActiveSheet.UsedRange.Find(strFind, , xlValues, xlWhole)
    MyData.AutoFilter Field:=rng1.Column, Criteria1:=strFind
    Me.ListBox1.Clear
    Application.ScreenUpdating = False
    For Each c In MyData.Columns(1).SpecialCells(xlCellTypeVisible)
        With Me.ListBox1
            
            .AddItem c.Offset(2, 0).Value
            .List(.ListCount - 1, 1) = c.Offset(2, 1).Value
            .List(.ListCount - 1, 2) = c.Offset(2, 2).Value
            .List(.ListCount - 1, 3) = c.Offset(2, 3).Value
            .List(.ListCount - 1, 4) = c.Offset(2, 4).Value
            .List(.ListCount - 1, 5) = c.Offset(2, 5).Value
            .List(.ListCount - 1, 6) = c.Offset(2, 6).Value
            .List(.ListCount - 1, 7) = c.Offset(2, 7).Value
            .List(.ListCount - 1, 8) = c.Offset(2, 8).Value
            .List(.ListCount - 1, 9) = c.Offset(2, 9).Value
            .List(.ListCount - 1, 10) = c.Offset(2, 10).Value
            .List(.ListCount - 1, 11) = c.Offset(2, 11).Value
            .List(.ListCount - 1, 12) = c.Offset(2, 12).Value
            .List(.ListCount - 1, 13) = c.Offset(2, 13).Value
            .List(.ListCount - 1, 14) = c.Offset(2, 14).Value
            .List(.ListCount - 1, 15) = c.Offset(2, 15).Value
            .List(.ListCount - 1, 16) = c.Offset(2, 16).Value
            .List(.ListCount - 1, 17) = c.Offset(2, 17).Value
            .List(.ListCount - 1, 18) = c.Offset(2, 18).Value
            .List(.ListCount - 1, 19) = c.Offset(2, 19).Value
            .List(.ListCount - 1, 20) = c.Offset(2, 20).Value
            .List(.ListCount - 1, 21) = c.Offset(2, 21).Value
            .List(.ListCount - 1, 22) = c.Offset(2, 22).Value
            .List(.ListCount - 1, 23) = c.Offset(2, 23).Value
            .List(.ListCount - 1, 24) = c.Offset(2, 24).Value
            .List(.ListCount - 1, 25) = c.Offset(2, 25).Value
            .List(.ListCount - 1, 26) = c.Offset(2, 26).Value
        End With
    Next c
Application.ScreenUpdating = True
End Sub


 MsgBox ("When using the find records feature, type what you would like to search for, no matter what it is, if it is the record date, or associate who entered it, or school address or city, type it  in the [FIRST] TextBox on the form, then click FindRecords"), vbCritical, "IMPORTANT!"
 Me.ListBox1.Visible = False
  Set ws = Sheet16
    Set MyData = ws.Range("a4").CurrentRegion   'database
    cntRecords = MyData.Rows.Count
    With Me
        .RcrCnt.Caption = (Me.ScrollBar1.Value - 4) & " of " & MyData.Rows.Count
        .AmdBtn.Enabled = False
        .DelBtn.Enabled = False
        .NewBtn.Enabled = True
        .ScrollBar1.Max = MyData.Rows.Count
        .ScrollBar1.Min = 4
        .Height = 555
        .Width = 505.5
        .FindBtn.ControlTipText = "Enter date you wish to lookup all records entered on that date in (first) textbox."
      End With
Dim lbtarget As MSForms.ListBox
    Dim rngSource As Range
    
    'Set reference to the range of data to be filled
    Set rngSource = ws.Range("A3:AA3")
    
    'Fill the listbox
    Set lbtarget = Me.ListBox1
    With lbtarget
        'Determine number of columns
        .ColumnCount = 27
        'Set column widths
        .ColumnWidths = "45;80;90;90;65;60;60;80;80;80;80;80;80;80;90;60;80;90;95;80;80;80;80;85;80;80;70"
        'Insert the range of data supplied
        .List = rngSource.Cells.Value
    End With

[/VB]

 

[Jerry Sullivan]

Hi and Welcome to MrExcel,

The technique of looping though each row with .AddItem plus .List won't work to populate more than 10 columns of a multi-column ListBox

You can test this using the code below on a simple UserForm that has just one ListBox. Make sure the Active Sheet has data in cells A1:Z10.

This code should run and populate 10 columns of the ListBox using data from the Active Sheet...

Private Sub UserForm_Initialize()
 Dim wks As Worksheet
 Dim lCol As Long, lRow As Long

 Set wks = ActiveSheet
 
 With Me.ListBox1
   .ColumnCount = 12
   For lRow = 1 To 5
      '--populates first column (index 0)
      .AddItem wks.Cells(lRow, 1).Value
      '--populates 2-10th columns (indicies 1-9)
      For lCol = 1 To 9
         .List(.ListCount - 1, lCol) = wks.Cells(lRow, lCol + 1).Value
      Next lCol
   Next lRow
 End With
End Sub

Next, modify this part to try to populate 11 columns....

      '--try to populate 2-11th columns (indicies 1-10)
      For lCol = 1 To [B][COLOR="#0000CD"]10[/COLOR][/B]
         .List(.ListCount - 1, lCol) = wks.Cells(lRow, lCol + 1).Value
      Next lCol

This should raise a Run-time Error.

For contiguous ranges, you can use .List to populate all the values...

Private Sub UserForm_Initialize()
 Dim wks As Worksheet
 Dim lCol As Long, lRow As Long

 Set wks = ActiveSheet
 
 With Me.ListBox1
   .ColumnCount = 12
   .List = wks.Range("A1:K20").Value
 End With
End Sub

For non-contiguous ranges (like the Visible Cells in your Autofiltered Range), you won't be able to simply add SpecialCells(xlCellTypeVisible).
Instead you'll need a work around like:
1. Temporarily Copying the Visible Cells to a Contiguous Range (a bit clunky)

2. Writing the Values of the Visible Cells into an Array. Then populate the ListBox by passing the Array to .List property of the ListBox.

This thread has example code...
http://www.mrexcel.com/forum/excel-...rching-database-sheet-returning-list-box.html

 

[thegil17]

I am actually aware of that Jerry. I am not getting a runtime error. If you look at my coding during the initialization of the Userform i've compensated for the column count of my 27 columns there instead of in the findall function for the listbox's ability to only host 10 columns. It doesn't matter if I do it there, or in any of the find subs or listbox subs. Actually i've discovered my code works perfectly if in the findall sub I do not offset c by 2 rows and instead start at start at 0. The thing is my column headers start at the third row, so I need to offset the rows to the third row to avoid incorporating instructional text above my column headers which I want included in the listbox. If someone could tell me why that is, then I will be as good as gold.

Thanks for the advice and quick reply though!

 

[Jerry Sullivan]

Actually i've discovered my code works perfectly if in the findall sub I do not offset c by 2 rows and instead start at start at 0. The thing is my column headers start at the third row, so I need to offset the rows to the third row to avoid incorporating instructional text above my column headers which I want included in the listbox.

Hmmm... that's surprising to me. What version of Excel are you using?

Could you provide an example workbook with any sensitive data removed? That will save the effort of trying to mockup your UserForm.

You could either upload it to a hosting site like Box.com and post a link or send me a PM and we'll exchange email addresses.

 

[thegil17]

I will do so later today Jerry. Thank you. I am at work at the moment. I appreciate your replies and help in the matter. I am a c# programmer at heart so I find VBA frustrating I will send you a pm with my email address so I can upload the file to you.