vba 当单元格中的值发生变化时，数据透视表上的过滤器会发生变化
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10340434/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
When the value in a cell changes change the filter on a pivot table
提问by asjohnson
I have been reading around for a bit and reached the ask SO point. I have a drop down selector in excel that lets me change names and when the name is changed, the value in "C3" is changed (c3 references another cell on another sheet if that matters) and when the value in "C3" changes I want a pivot table on another sheet (sheet6 for now) to update its filter on territory id to reflect the new value of "C3".
我已经阅读了一些并达到了询问 SO 点。我在 excel 中有一个下拉选择器，可以让我更改名称，当名称更改时，“C3”中的值会更改（如果重要，c3 会引用另一个工作表上的另一个单元格），并且当“C3”中的值更改时我想要另一个工作表（现在是工作表 6）上的数据透视表更新其对区域 id 的过滤器以反映“C3”的新值。
It sounds like an event to me, so I went digging around and found out VBA has events (yay!), but I have been unable to get my event to actually do anything. The code I have there works when I run it as a macro, but I would really like it to automatically run everytime the value in cell "C3" changes.
对我来说这听起来像是一个事件，所以我四处挖掘并发现 VBA 有事件（是的！），但我一直无法让我的事件真正做任何事情。当我将它作为宏运行时，我拥有的代码有效，但我真的希望它在每次单元格“C3”中的值发生变化时自动运行。
Here is what I am trying so far:
这是我目前正在尝试的：
Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Sheets("Current Status").Range("C3")) Is Nothing Then
Sheets("Sheet6").PivotTables("PivotTable5").PivotFields("territory_id"). _
ClearAllFilters
Sheets("Sheet6").PivotTables("PivotTable5").PivotFields("territory_id").CurrentPage _
= Sheets("Current Status").Range("C3").Value
End If
End Sub
Update: I have found that if I put the above code in the sheet section (sheet 2) instead of in a new module I can get it to run if I physically enter the code and then hit enter. Now I am wondering if there is a way to make it do it without me having to manually enter the value and hit enter. I just want to be able to use my drop down menu to select a new name and when the value in c3 changes due to the drop down selector update the pivot table.
更新：我发现如果我将上面的代码放在工作表部分（工作表 2）而不是在新模块中，如果我实际输入代码然后按回车键，我可以让它运行。现在我想知道是否有一种方法可以做到这一点，而不必手动输入值并按回车键。我只想能够使用我的下拉菜单来选择一个新名称，并且当 c3 中的值由于下拉选择器而更改时更新数据透视表。
Thank you as always SO.
一如既往地谢谢你。
回答by Scott Holtzman
The problem is that C3 is not actually changing, because it's just formula reference that is updating. Is the "drop down selector" on a form or based on data validation and in a cell?
问题是 C3 实际上并没有改变，因为它只是正在更新的公式引用。“下拉选择器”是在表单上还是基于数据验证和单元格？
If it's based on a cell, set your target to be the target cell, not C3 -> because C3 is just a formula reference, and your drop down cell is the one actually changing.
如果它基于单元格，请将目标设置为目标单元格，而不是 C3 -> 因为 C3 只是一个公式引用，而您的下拉单元格是实际更改的单元格。
If it's based on a form, but code in the on_change event of the form control.
如果是基于表单，但是在表单控件的 on_change 事件中编码。
