无法在多个工作表上循环显示透视图字段按钮。

huangapple go评论72阅读模式
英文:

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.

huangapple
  • 本文由 发表于 2023年7月27日 19:07:03
  • 转载请务必保留本文链接:https://go.coder-hub.com/76779126.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定