I can't get the VBA codes for data search to work.

[Aretradeser]

I can't get the VBA codes for data search to work.
In an Excel workbook, I use a UserForm to perform data searches in a sheet of the same; but the codes that I use to perform these searches always give me an error. I don't know what the problem is. I would like you to help me. I upload the example workbook.
Thank you.
Example_CE

 

[dmt32]

Hi,
I have only found time to have a quick play with one of your codes & seemed to return results to listbox ok

Sub campos()
    'filtra por campos
    Dim sh(1 To 2)      As Worksheet
    Dim FilterRange     As Range
    Dim FilterCount     As Long
    
    With ThisWorkbook
        Set sh(1) = .Worksheets("BDATOS")
        sh(1).Unprotect ("123")
        
        Set sh(2) = .Worksheets("CONSULTAS")
    End With
    
    With sh(2)
        .Unprotect ("123")
        .Cells.Clear
    End With
    
    With sh(1)
        .AutoFilterMode = False
        With .UsedRange
            .Cells(1, 1).AutoFilter
            If Me.CheckBox2 Then .AutoFilter Field:=5, Criteria1:=Me.ComboBox1 'Country
            If Me.CheckBox3 Then .AutoFilter Field:=4, Criteria1:=Me.ComboBox2 'Destination
            If Me.CheckBox4 Then .AutoFilter Field:=2, Criteria1:=Me.ComboBox3 'Name
        End With
        
        Set FilterRange = .AutoFilter.Range
        
    End With
    
    FilterCount = FilterRange.Columns(1).SpecialCells(xlCellTypeVisible).Count - 1
    
    If FilterCount > 0 Then
        
        FilterRange.SpecialCells(xlCellTypeVisible).Copy sh(2).Range("A1")
        
        sh(2).UsedRange.Columns.AutoFit
    
        With Me.ListBox1
            .ColumnCount = FilterRange.Columns.Count
            .RowSource = sh(2).Name & "!" & _
                         sh(2).UsedRange.Offset(1).Resize(sh(2).UsedRange.Rows.Count - 1).Address
        End With
    End If
    
    FilterRange.AutoFilter
    Set FilterRange = Nothing
    
End Sub

I have only looked at the filter part of code & removed anything else you were doing.
If update works for you then will need to adapt idea across your project

Dave

 

[Aretradeser]

Hi,
I have only found time to have a quick play with one of your codes & seemed to return results to listbox ok

Sub campos()
    'filtra por campos
    Dim sh(1 To 2)      As Worksheet
    Dim FilterRange     As Range
    Dim FilterCount     As Long
   
    With ThisWorkbook
        Set sh(1) = .Worksheets("BDATOS")
        sh(1).Unprotect ("123")
       
        Set sh(2) = .Worksheets("CONSULTAS")
    End With
   
    With sh(2)
        .Unprotect ("123")
        .Cells.Clear
    End With
   
    With sh(1)
        .AutoFilterMode = False
        With .UsedRange
            .Cells(1, 1).AutoFilter
            If Me.CheckBox2 Then .AutoFilter Field:=5, Criteria1:=Me.ComboBox1 'Country
            If Me.CheckBox3 Then .AutoFilter Field:=4, Criteria1:=Me.ComboBox2 'Destination
            If Me.CheckBox4 Then .AutoFilter Field:=2, Criteria1:=Me.ComboBox3 'Name
        End With
       
        Set FilterRange = .AutoFilter.Range
       
    End With
   
    FilterCount = FilterRange.Columns(1).SpecialCells(xlCellTypeVisible).Count - 1
   
    If FilterCount > 0 Then
       
        FilterRange.SpecialCells(xlCellTypeVisible).Copy sh(2).Range("A1")
       
        sh(2).UsedRange.Columns.AutoFit
   
        With Me.ListBox1
            .ColumnCount = FilterRange.Columns.Count
            .RowSource = sh(2).Name & "!" & _
                         sh(2).UsedRange.Offset(1).Resize(sh(2).UsedRange.Rows.Count - 1).Address
        End With
    End If
   
    FilterRange.AutoFilter
    Set FilterRange = Nothing
   
End Sub

I have only looked at the filter part of code & removed anything else you were doing.
If update works for you then will need to adapt idea across your project

Dave

Dave, regarding the modification of the VBA code (fields) you have made, I have to say that the search result is displayed in the ListBox; but only if the search option By country of destination is chosen independently. It does not work if it is chosen together with the other two fields: By destination city and By name, which are dependent.
It wouldn't matter if the "By name" field was not dependent; but I need the "destination city" field to be dependent on the "country of destination" field. Otherwise, I would have to search for each of these fields individually.

 

[Aretradeser]

Dave, regarding the modification of the VBA code (fields) you have made, I have to say that the search result is displayed in the ListBox; but only if the search option By country of destination is chosen independently. It does not work if it is chosen together with the other two fields: By destination city and By name, which are dependent.
It wouldn't matter if the "By name" field was not dependent; but I need the "destination city" field to be dependent on the "country of destination" field. Otherwise, I would have to search for each of these fields individually.

This is now solved, there was a mistake in the choice of columns. And I have also removed the dependency on the ComboBox3.
Now I'm going to try to adapt the rest of the code corresponding to: "Search by date" and "Search by any data".

 

[dmt32]

This is now solved, there was a mistake in the choice of columns. And I have also removed the dependency on the ComboBox3.
Now I'm going to try to adapt the rest of the code corresponding to: "Search by date" and "Search by any data".

Hi,
glad suggestion goes in right direction & gives you something to progress your project. Appreciate the feedback

Dave

 

[Aretradeser]

Hola,
la sugerencia alegre va en la dirección correcta y le da algo para progresar en su proyecto. Agradezco los comentarios

dave

Thank you, Dave, for your kind reply. I take it that the issue raised has been resolved.
It's a pleasure to have people like you, always willing to help.