vba 用按钮刷新受保护的数据透视表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21444104/
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
Refresh protected pivot-table with a button?
提问by user1721230
I have this macro for a button to refresh all the pivot-tables in a worksheet:
我有一个用于刷新工作表中所有数据透视表的按钮的宏：
Sub Button3_Click()
ThisWorkbook.RefreshAll
End Sub
And I wanted to add the functionality to refresh all the pivot tables even if they are on a protected sheet. I protected the pivot-table sheet with the password MyPwd and used the below code, but it won't work:
我想添加刷新所有数据透视表的功能，即使它们在受保护的工作表上。我使用密码 MyPwd 保护数据透视表并使用以下代码，但它不起作用：
Sub Button3_Click()
Unprotect Password:="MyPwd"
ThisWorkbook.RefreshAll
Protect Password:="MyPwd", _
DrawingObjects:=True, Contents:=True, _
Scenarios:=True, AllowUsingPivotTables:=True
End With
End Sub
Visual Basic is all new to me. What am I doing wrong?
Visual Basic 对我来说是全新的。我究竟做错了什么？
回答by Jerome Montino
The Unprotectyou want is a worksheet method. You should qualify it.
在Unprotect你想要的是一个工作方法。你应该有资格。
Sub ProtRef()
Dim TargetSht As Worksheet
Set TargetSht = ThisWorkbook.Sheets("Sheet1") 'Modify as needed.
With TargetSht
.Unprotect MyPwd
ThisWorkbook.RefreshAll
.Protect MyPwd
End With
End Sub
Note TargetShtand the With-End With. Let us know if this helps.
注意TargetSht和With-End With. 如果这有帮助，请告诉我们。
EDIT:
编辑：
Sub ProtRef()
Dim WB As Workbook, WS As Worksheet
For Each WS In WB.Worksheets
If WS.Name <> "Sheet1" Then
WS.Unprotect MyPwd
End If
End With
ThisWorkbook.RefreshAll
For Each WS In WB.Worksheets
If WS.Name <> "Sheet1" Then
WS.Protect MyPwd
End If
End With
End Sub
Paste in a regular module, like below:
粘贴到常规模块中，如下所示：
Let us know if this helps.
如果这有帮助，请告诉我们。
回答by user1721230
Thank you @BK201 for having a crack at it, you definitely pointed me in the right direction. I used this code in the button and it seemed to do the trick:
谢谢@BK201 破解它，你绝对给我指明了正确的方向。我在按钮中使用了此代码，它似乎可以解决问题：
Sub Button1_Click()
Dim Sht As Worksheet
For Each Sht In ThisWorkbook.Worksheets
Sht.Unprotect Password:="MyPwd"
ThisWorkbook.RefreshAll
Next
For Each Sht In ThisWorkbook.Worksheets
Sht.Protect Password:="MyPwd"
Next
End Sub
