问题描述
我试图看看是否可以通过编程方式捕获AutoFilter排序事件,获取排序条件,然后将相同的排序条件应用于第二个工作表中的自动过滤器。
到目前为止,好像我必须触发Worksheet_Calculate()事件。这样我做了。那么我必须检查AutoFilter排序条件是否被更改。如果没有,退出子。如果是,请收集标准并通过单独的子文件运行它,它在单独的工作表中对AutoFilter执行完全相同的排序。
一般的想法是,只要这两个AutoFilter中的一个被排序,另一张表中的AutoFilter应该按照完全相同的方式进行排序。
我试图做这样的事情(我必须添加一个Excel公式来实际计算事件触发器):
Private Sub Worksheet_Calculate()
Dim wbBook as Workbook
Dim wsSheet as Worksheet
Dim rnData as Range
设置wbBook = ThisWorkbook
设置wsSheet = wbBook.Worksheets(Sheet1)
带有wsSheet
设置dnData = .UsedRange
结束
End Sub
但我似乎无法设法收集标准,我已经尝试了几件事情,并添加了一个手表到dnData甚至不显示任何AutoFilter属性。有人可以在这方面发光吗?
这是一种获取 autofilter
标准:
Sub test()
Dim Header As Range
Dim sMainCrit As String ,sANDCrit As String,sORCrit As String
Set Header = Range(A2:C2)
With Header.Parent.AutoFilter
With .Filters(Header.Column - .Range.Column + 1)
如果不是.On然后
MsgBox(no criteria)
退出Sub
结束If
sMainCrit = .Criteria1
如果.Operator = xlAnd然后
sANDCrit = .Criteria2
ElseIf .Operator = xlOr然后
sORCrit = .Criteria2
如果
结束
结束
MsgBox(Main criteria:& sMainCrit& Chr(13)&AND Criteria:& sANDCrit& Chr(13)&OR Criteria& sORCrit)
End Sub
改编自 ozgrid
I'm trying to see if I can programatically trap an AutoFilter sort event, get the sort criteria and then apply that same sort criteria to an AutoFilter in a second worksheet.
So far it seems as though I have to trigger the Worksheet_Calculate() event. And this I've done. Then I have to check if the AutoFilter sort criteria was changed. If it wasn't, exit sub. If it was, collect the criteria and run it through a separate sub, which does the exact same sorting on an AutoFilter in a separate worksheet.
The general idea is that whenever one of these two AutoFilters are sorted, the AutoFilter in the other sheet should be sorted the exact same way.
I've tried to do something like this (I had to add an Excel formula to actually make the calculate event trigger):
Private Sub Worksheet_Calculate()
Dim wbBook as Workbook
Dim wsSheet as Worksheet
Dim rnData as Range
Set wbBook = ThisWorkbook
Set wsSheet = wbBook.Worksheets("Sheet1")
With wsSheet
Set dnData = .UsedRange
End With
End Sub
But I can't seem to manage to collect the criteria, I've tried several things and adding a watch to the dnData doesn't even reveal any AutoFilter property. Can someone shed any light on this?
Here is a way to get the autofilter
criteria:
Sub test()
Dim Header As Range
Dim sMainCrit As String, sANDCrit As String, sORCrit As String
Set Header = Range("A2:C2")
With Header.Parent.AutoFilter
With .Filters(Header.Column - .Range.Column + 1)
If Not .On Then
MsgBox ("no criteria")
Exit Sub
End If
sMainCrit = .Criteria1
If .Operator = xlAnd Then
sANDCrit = .Criteria2
ElseIf .Operator = xlOr Then
sORCrit = .Criteria2
End If
End With
End With
MsgBox ("Main criteria: " & sMainCrit & Chr(13) & "AND Criteria:" & sANDCrit & Chr(13) & "OR Criteria" & sORCrit)
End Sub
Adapted from ozgrid
这篇关于获取AutoFilter排序条件并应用于第二张表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!