Advanced FIlter Conundrum

Rfriend

Board Regular
Joined
May 10, 2021
Messages
73
Office Version
  1. 2010
Platform
  1. Windows
Can someone help me with an advanced filter (AF) issue. I have 2 AF on one page pulling from a different page sources. I create the macros on the page where the data is to be returned, I get an error of "No Match Found" when I run the filter macros in the userform unless I am physically on the extract range page of the workbook. If I click off to another tab, such as the source worksheet, and call the userform from the VBA Editor, I get the "No Match Found" error box. When I click back onto the tab that the data is returned to (Sheet10), and call the userform, I get sorted data in my form?

Here is the AF code I am running. Criteria and extract is the same page for both filters, returning data from different worksheets in my workbook, to one worksheet in different tables. I am on the extract range page when recording my macros to a different location. This is likely a newb question, but I do not know what I need to do to make this work.


Adv1ListBox1
Table735 Source = Sheet7.Range("Table735[#All]")
Table1 Criteria = Sheet10.Range("E4:E5")
Table1 Extract Range = Sheet20.Range("A7:E7")

Adv2ListBox2
Table18 Source = Sheet20.Range("Table18[#All]")
Table2 Criteria = Sheet10.Range("I4:I5")
Table2 Extract Range = Sheet20.Range("G7:J7")

VBA Code:
Sub AdvFilterLB1()
        Sheets("Staff_Data").Range("Table735[#All]").AdvancedFilter Action:= _
        xlFilterCopy, CriteriaRange:=Range("Sheet2!Criteria"), CopyToRange:=Range( _
        "Sheet2!Extract"), Unique:=False
End Sub

Sub AdvFilterLB2()
        Sheet20.Range("Table18[#All]").AdvancedFilter Action:= _
        xlFilterCopy, CriteriaRange:=Sheet10.Range("I4:I5"), CopyToRange:=Range("G7:J7"), Unique:=False
End Sub
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Can someone help me with an advanced filter (AF) issue.
  • I have 3 tables on one page. The data from tables 1 & 2 combine into table3. Then table3 data is moved, via command button, to another worksheet, Sheet8("Data"). The process allows you to search through all active employees in the employee database using an advanced filter, return the results to listbox1, then multi-select as many students as required who completed training.
  • A second listbox2 allows you to search for training from a training library, via a separate command button, and select one class to assign to all the students selected in listbox1.
  • A third criteria requires a mandatory completion date in a textbox.
  • A third commandbuttom combines all the students, assigns each the training, and each a completion date for record in listbox3 (Table3 of the worksheet).
PROBLEM
If the user calls the "Add Training" userform with the commanbutton on the user interface tab, the values returned through the search to the user are null for both listbox 1&2.​
If the user goes to the page that hosts tables 1-3, and then calls the userform using the VBA editor, the results returned to the user through the userform are whatever the user asked for for both listboxes 1 & 2.​
The advanced filters were created using the macro recorder as shown in the code below, and the recording was initiated from the page that would receive the search data and host the 3 tables, Sheet10("sheet2")​
Q - Can anyone tell me why the advanced filter only works if you are on the page that the search data is returned to rather than when called from the interface page?​
Q - Should the search criteria be on the page with the original data rather than the results page?​
AdvFilterListBox1​
List Range = Sheet7.Range("Table735[#All]")​
Criteria Range = Sheet10.Range("E4:E5")​
Extract Range = Sheet10.Range("A7:E7")​
AdvFilterListBox2​
List Range = Sheet20("Training_List").Range("Table18#All]")​
Criteria Range = Sheet10.Range("I4:I5")​
Extract Range = Sheet10.Range("G7:J7")​
VBA Code:
Sub AdvFilterLB1()
        Sheets("Staff_Data").Range("Table735[#All]").AdvancedFilter Action:= _
        xlFilterCopy, CriteriaRange:=Range("Sheet2!Criteria"), CopyToRange:=Range( _
        "Sheet2!Extract"), Unique:=False
End Sub

Sub AdvFilterLB2()
        Sheet20.Range("Table18[#All]").AdvancedFilter Action:= _
        xlFilterCopy, CriteriaRange:=Sheet10.Range("I4:I5"), CopyToRange:=Range("G7:J7"), Unique:=False
 

Attachments

  • Tables.GIF
    Tables.GIF
    65.6 KB · Views: 5
  • Userform.GIF
    Userform.GIF
    26.9 KB · Views: 5
Upvote 0
Try explicitly defining the sheet in your results address
eg changing this:
VBA Code:
CopyToRange:=Range("G7:J7"),

To this:
VBA Code:
CopyToRange:=Sheet10.Range("G7:J7"),
 
Upvote 0
Try explicitly defining the sheet in your results address
eg changing this:
VBA Code:
CopyToRange:=Range("G7:J7"),

To this:
VBA Code:
CopyToRange:=Sheet10.Range("G7:J7"),
I changed both filters as shown but still can only return data if the userform is called from the VBA Editor, and I am physically on the page the search results are sent to, sheet10.

VBA Code:
Sub AdvFilterLB1()
        Sheet7.Range("Table735[#All]").AdvancedFilter Action:= _
        xlFilterCopy, CriteriaRange:=Sheet10.Range("E4:E5"), CopyToRange:=Sheet10.Range("A7:E7"), Unique:=False
End Sub

Sub AdvFilterLB2()
        Sheet20.Range("Table18[#All]").AdvancedFilter Action:= _
        xlFilterCopy, CriteriaRange:=Sheet10.Range("I4:I5"), CopyToRange:=Sheet10.Range("G7:J7"), Unique:=False
End Sub
 
Upvote 0
Your code shows the applying of the filter and creating the filtered output and I am pretty sure that part is doing what it needs to be doing.

What I can't see is the UserForm code calling and applying that code ?

Looking just at the List "AdvFilterLB1"
you need something like either or both of the below:
(the key to calling the RowSource from another sheet is the addition of (External:=True)

VBA Code:
Private Sub UserForm_Initialize()
    Call AdvFilterLB1
    With Sheet10
        AdvFilterLB1.RowSource = .Range("A7:E" & .Cells(Rows.Count, 1).End(xlUp).Row).Address(External:=True)
    End With
End Sub

Private Sub AdvFilterLB1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    Call AdvFilterLB1
    With Sheet10
        AdvFilterLB1.RowSource = .Range("A7:E" & .Cells(Rows.Count, 1).End(xlUp).Row).Address(External:=True)
    End With
End Sub
 
Upvote 0
Solution
Your code shows the applying of the filter and creating the filtered output and I am pretty sure that part is doing what it needs to be doing.

What I can't see is the UserForm code calling and applying that code ?

Looking just at the List "AdvFilterLB1"
you need something like either or both of the below:
(the key to calling the RowSource from another sheet is the addition of (External:=True)

VBA Code:
[/QUOTE]
[QUOTE="Alex Blakenburg, post: 5893875, member: 473943"]

Private Sub UserForm_Initialize()
    Call AdvFilterLB1
    With Sheet10
        AdvFilterLB1.RowSource = .Range("A7:E" & .Cells(Rows.Count, 1).End(xlUp).Row).Address(External:=True)
    End With
End Sub

Private Sub AdvFilterLB1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    Call AdvFilterLB1
    With Sheet10
        AdvFilterLB1.RowSource = .Range("A7:E" & .Cells(Rows.Count, 1).End(xlUp).Row).Address(External:=True)
    End With
End Sub
Alex,

Here is the code that calls the advfilters. These are the two command buttons that search the return for sheet7 and sheet 20 to sheet10.

VBA Code:
Private Sub Userform_Initialize()
    Dim table1 As ListObject
    Dim table3 As ListObject
        Me.Trng9 = Format(Me.Trng9, "mm/dd/yyyy")
            Clear
        Set table3 = Worksheets("Sheet2").ListObjects("Table3")
    If Not table3.DataBodyRange Is Nothing Then
        table3.DataBodyRange.Delete
    End If
End Sub

Private Sub cmdListBox1_Click()
' Search for employee
    Dim Sheet2SH As Worksheet
    Dim Staff_DataSH As Worksheet
    On Error GoTo errHandler:
        Set Staff_DataSH = Sheet7
        Set Sheet2SH = Sheet10
            Sheet2SH.Range("E4").Value = cboSelect.Value
            Sheet2SH.Range("E5").Value = txtSearchTrng.Text
        AdvFilterLB1
            SortitTrng1
            Listbox1.RowSource = Sheet10.Range("Name").Address(external:=True)
    Exit Sub
errHandler:
    MsgBox "No match found for " & txtSearchTrng.Text
        On Error GoTo 0
    Exit Sub
End Sub

Private Sub cmdListBox2_Click()
' Search for training to assign
    Dim Sheet2SH As Worksheet
    Dim Training_ListSH As Worksheet
    On Error GoTo errHandler:
        Set Training_ListSH = Sheet20
        Set Sheet2SH = Sheet10
            Sheet2SH.Range("I4").Value = Me.cboSelect2.Value
            Sheet2SH.Range("I5").Value = txtSearchTrng2.Text
        AdvFilterLB2
          SortitTrng2
            Listbox2.RowSource = Sheet10.Range("Trng").Address(external:=True)
        Exit Sub
errHandler:
    MsgBox "No match found for " & txtSearchTrng2.Text
        On Error GoTo 0
    Exit Sub
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,485
Messages
6,113,931
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top