VBA到Autofilter数据透视表通过组合框进行更改 [英] VBA to Autofilter Pivot Tables on change via combobox

问题描述

您好，

我已经录制了一个宏来过滤数据透视表但是我无法让它工作在我的用户表单上更改事件？  我有三个用于过滤报告的透视字段。 

谢谢。

解决方案

< p lang ="x-none"style ="margin：0in; font-family：Calibri; font-size：11.0pt">设置了组合框的行源后，您可以尝试以下代码：

 Private Sub ComboBox1_Change（）
 Set pvt = ActiveSheet.PivotTables（" PivotTable1"）
 Set fld = pvt.PivotFields（" a"） 
 fld.ClearAllFilters 
 For each itm in fld.PivotItems 
 if itm<> ComboBox1.Value然后
 itm.Visible = False 
 Else 
 itm.Visible = True 
 End if 
 Next itm 
 End Sub 

如果您有任何问题，请告诉我。

问候，

Celeste

Hello,

I have recorded a macro to filter the pivot table however I am unable unable to get it to work with the change event on my user form?  I have three pivot fields that filter the report. 

Thanks.

解决方案

Hi,

After setting the rowsource of the combobox, you could try the code like:

Private Sub ComboBox1_Change()
Set pvt = ActiveSheet.PivotTables("PivotTable1")
Set fld = pvt.PivotFields("a")
fld.ClearAllFilters
For Each itm In fld.PivotItems
If itm <> ComboBox1.Value Then
itm.Visible = False
Else
itm.Visible = True
End If
Next itm
End Sub

If you have any issue, please let me know.

Regards,

Celeste

这篇关于VBA到Autofilter数据透视表通过组合框进行更改的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！