英文:
Cannot loop Pivot Chart ShowAllFieldButtons on Multiple Sheets
问题
我有一个情景,需要隐藏数据透视图图表上的字段按钮。我需要遍历一系列工作表,并对每个工作表上的每个图表执行此操作。
我知道我可能遗漏了一些简单的东西 - 但我在循环中遇到了问题。
Sub DivisionMacro()
Dim ws As Worksheet
Dim ch As ChartObject
'创建部门工作表
ActiveSheet.PivotTables("PivotTable12").ShowPages PageField:= _
"User Custom Fields.Division2023"
'取消保护工作表
Cells.Select
ActiveSheet.Unprotect
'循环遍历
For Each ws In ActiveWorkbook.Worksheets
'隐藏顶行
ws.Rows(1).Hidden = True
'创建图表
ws.Shapes.AddChart2(216, xlBarClustered).Select
'隐藏图表字段按钮
ws.ChartObjects("Chart 1").Activate
ActiveChart.ShowAllFieldButtons = False
'保护工作表
'ws.Protect Password:=strPassword, DrawingObjects:=True, Contents:=True, Scenarios:=True, _
'UserInterfaceOnly:=True, AllowFormattingCells:=False, AllowFormattingColumns:=True, _
'AllowFormattingRows:=False, AllowInsertingColumns:=False, AllowInsertingRows:=False, _
'AllowInsertingHyperlinks:=False, AllowDeletingColumns:=False, AllowDeletingRows:=False, _
'AllowSorting:=False, AllowFiltering:=False, AllowUsingPivotTables:=True
Next ws
End Sub
希望这有助于你的工作。
英文:
I have a scenario where I need to hide the Field Buttons on a Pivot Chart. I need to loop through a series of sheets, and do this to each chart on each sheet.
I know I'm missing something simple - but I'm having trouble looping it through.
Sub DivisionMacro()
Dim ws As Worksheet
Dim ch As ChartObject
'Create Division Sheets
ActiveSheet.PivotTables("PivotTable12").ShowPages PageField:= _
"User Custom Fields.Division2023"
'UnProtect Sheet
Cells.Select
ActiveSheet.Unprotect
'Loop Through
For Each ws In ActiveWorkbook.Worksheets
'Hide Top Row
ws.Rows(1).Hidden = True
'Create Chart
ws.Shapes.AddChart2(216, xlBarClustered).Select
'Show Chart Field Buttons
ws.ChartObjects("Chart 1").Activate
ActiveChart.ShowAllFieldButtons = False
'Protect Sheet
'ws.Protect Password:=strPassword, DrawingObjects:=True, Contents:=True, Scenarios:=True, _
'UserInterfaceOnly:=True, AllowFormattingCells:=False, AllowFormattingColumns:=True, _
'AllowFormattingRows:=False, AllowInsertingColumns:=False, AllowInsertingRows:=False, _
'AllowInsertingHyperlinks:=False, AllowDeletingColumns:=False, AllowDeletingRows:=False, _
'AllowSorting:=False, AllowFiltering:=False, AllowUsingPivotTables:=True
Next ws
End Sub
答案1
得分: 0
首先,无需进行任何选择或激活操作。其次,在创建图表时,将对象分配给一个变量,然后使用该变量来设置您的设置。以下是一个示例...
Dim ws As Worksheet
Dim pt As PivotTable
Dim shp As Shape
For Each ws In ActiveWorkbook.Worksheets
On Error Resume Next
Set pt = ws.PivotTables(1)
On Error GoTo 0
If Not pt Is Nothing Then
Set shp = ws.Shapes.AddChart2(216, xlBarClustered)
With shp.Chart
.SetSourceData pt.TableRange2
.ShowAllFieldButtons = False
End With
Set pt = Nothing
End If
Next ws
请注意,它在循环遍历每个工作表时首先检查是否存在数据透视表。如果存在,则继续创建图表等操作。
英文:
First, there's no need to do any selecting or activating. Secondly, when creating the chart, assign the object to a variable, and then use that variable to set your settings. Here's an example...
Dim ws As Worksheet
Dim pt As PivotTable
Dim shp As Shape
For Each ws In ActiveWorkbook.Worksheets
On Error Resume Next
Set pt = ws.PivotTables(1)
On Error GoTo 0
If Not pt Is Nothing Then
Set shp = ws.Shapes.AddChart2(216, xlBarClustered)
With shp.Chart
.SetSourceData pt.TableRange2
.ShowAllFieldButtons = False
End With
Set pt = Nothing
End If
Next ws
Note that as it loops through each worksheet, it first checks whether a pivot table exists. If so, then it proceeds to create the chart, etc.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论