Excel VBA - 检查报告切片器选择（如果全部被选中则跳过）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15981304/
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
Excel VBA - Check Report Slicer Selections (Skip if ALL are selected)
提问by user2276077
I need help with some VBA code. I have an AgeRange slicer and I have a working script that inserts a row, adds a timestamp, and then reports the slicer selections.
我需要一些 VBA 代码方面的帮助。我有一个 AgeRange 切片器，我有一个工作脚本，可以插入一行，添加一个时间戳，然后报告切片器的选择。
I'd like to add something to this that will SKIP the process if ALL the items in the slicer are selected (True).
我想添加一些东西，如果选择了切片器中的所有项目（真），它将跳过该过程。
Is there something that I can insert that says "If the slicer hasn't been touched (all items are true), then end sub".
有什么我可以插入的内容，上面写着“如果切片器没有被触摸（所有项目都是真的），那么结束子”。
Here's what I have for code so far:
到目前为止，这是我的代码：
Dim cache As Excel.SlicerCache
Set cache = ActiveWorkbook.SlicerCaches("Slicer_AgeRange")
Dim sItem As Excel.SlicerItem
For Each sItem In cache.SlicerItems
If sItem.Selected = True Then xAge = xAge & sItem.Name & ", "
Next sItem
Rows("1:1").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A1").Select
ActiveCell.FormulaR1C1 = Format(Now(), "MM-DD-YYYY HH:MM AM/PM")
Range("B1").Select
ActiveCell.FormulaR1C1 = xAge
Range("C1").Select
End Sub
Any help is greatly appreciated!
任何帮助是极大的赞赏！
回答by Dallas Frank
This is a bit more than you asked for, but I figured I would share since I just wrote this for my own use. It clears all slicers physically located on a worksheet only if they are filtered (not all selected). For your question, the good bit is the for each item loop. and the line right after it.
这比你要求的要多一些，但我想我会分享，因为我只是为了自己的使用而写的。仅当过滤器（并非全部选择）时，它才会清除物理上位于工作表上的所有切片器。对于您的问题，好的一点是 for each item 循环。以及紧随其后的那一行。
Sub clearWorksheetSlicers(ws As Worksheet)
'clears all slicers that have their shape on a specific worksheet
Dim slSlicer As Slicer
Dim slCache As SlicerCache
Dim item As SlicerItem
Dim hasUnSel As Boolean
For Each slCache In ThisWorkbook.SlicerCaches
For Each slSlicer In slCache.Slicers
If slSlicer.Shape.Parent Is ws Then
For Each item In slCache.SlicerItems
If item.Selected = False Then
hasUnSel = True
Exit For
End If
Next item
If hasUnSel = True Then slCache.ClearManualFilter
hasUnSel = False
End If
Next slSlicer
Next slCache
End Sub
回答by krissy
Nvm. I got it on my own. :)
净值。我自己拿到的。:)
Dim cache As Excel.SlicerCache
Dim sName As Slicers
Dim sItem As Excel.SlicerItem
Dim xSlice As String
Dim xName As String
For Each cache In ActiveWorkbook.SlicerCaches
xName = StrConv(Replace(cache.Name, "AgeRange", "Ages")
xCheck = 0
For Each sItem In cache.SlicerItems
If sItem.Selected = False Then
xCheck = xCheck + 1
Else
xCheck = xCheck
End If
Next sItem
If xCheck > 0 Then
For Each sItem In cache.SlicerItems
If sItem.Selected = True Then
xSlice = xSlice & sItem.Caption & ", "
End If
Next sItem
Rows("1:1").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("B1").Select
ActiveCell.FormulaR1C1 = xName & ": " & xSlice
xSlice = ""
End If
Next cache
Range("A1").Select
ActiveCell.FormulaR1C1 = Format(Now(), "MM-DD-YYYY HH:MM AM/PM")
End Sub
