在多张纸上运行代码

发布于 2024-12-16 22:59:10 字数 271 浏览 2 评论 0原文

我有这个代码。

Const pw   As String = "password"    '<-change password here
ActiveSheet.Unprotect pw
Range("B7").QueryTable.Refresh BackgroundQuery:=False
ActiveSheet.Protect pw

它取消保护、刷新数据并重新保护。目前，它通过活动工作表上的按钮运行。我希望按钮对两张不同的工作表执行相同的操作。

原文

I have this code.

Const pw   As String = "password"    '<-change password here
ActiveSheet.Unprotect pw
Range("B7").QueryTable.Refresh BackgroundQuery:=False
ActiveSheet.Protect pw

It unprotects, refreshes data and reprotects. Currently it runs from a button on the active sheet. I want the button to do the same thing for two different sheets.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

谜泪 2024-12-23 22:59:10

下面是一个示例，说明如何设置要浏览的工作表数组以及如何使用 With 语句来获得更简洁的代码。如果您想对所有工作表执行此操作，您可以简单地说“对于工作表中的每个工作表”，无需声明工作表数组。 :)

Sub Test()

Dim pw As String
pw = "password"
Dim sheet As Variant
Dim refreshSheets(1 To 2) As Worksheet

Set refreshSheets(1) = sheets(1)
Set refreshSheets(2) = sheets(2)

For Each sheet In refreshSheets
    With sheet
        .Unprotect pw
        .Range("B7").QueryTables.Refresh BackgroundQuery:=False
        .Protect pw
    End With
Next

End Sub

Here's an example of how you can set up an array of sheets to go through and using the With statement for cleaner code. If you want to do this for all sheets, you can simple say "For Each sheet in Worksheets" with no need to declare an array of sheets. :)

Sub Test()

Dim pw As String
pw = "password"
Dim sheet As Variant
Dim refreshSheets(1 To 2) As Worksheet

Set refreshSheets(1) = sheets(1)
Set refreshSheets(2) = sheets(2)

For Each sheet In refreshSheets
    With sheet
        .Unprotect pw
        .Range("B7").QueryTables.Refresh BackgroundQuery:=False
        .Protect pw
    End With
Next

End Sub

回复收藏 0 原文