excel - 运行时错误 1004 : Macros Don't Work After Protecting Sheet

Question

我为我的团队和我所在领域的其他代理创建了一个工作表，以帮助跟踪一年中每个月的销售和销售预测。但是，每次我设置密码并保存文件时，都会收到一条错误消息:

Run-time error 1004 Application defined or object defined error

我的代码如下。请帮忙。我不想分享这个不 protected 文件。如何保护每个工作表并仍然启用宏，而无需有人输入代码或被允许取消保护然后保护，这将使他们能够访问我的代码。这是我的代码:

Public Sub ResetValues_Click()

Sheets("January").Range("D3:D3").Value = ""
Sheets("January").Range("D21:D21").Value = ""
Sheets("January").Range("H21:H21").Value = ""
Sheets("January").Range("L21:L21").Value = ""
Sheets("January").Range("P21:P21").Value = ""
Sheets("January").Range("T21:T21").Value = ""
Sheets("January").Range("X21:X21").Value = ""
Sheets("January").Range("D5:D5").Value = ""
Sheets("January").Range("G3:G3").Value = ""
Sheets("January").Range("G4:G4").Value = ""
Sheets("January").Range("C14:C18").Value = ""
Sheets("January").Range("G14:G18").Value = ""
Sheets("January").Range("D14:D18").Value = ""
Sheets("January").Range("E14:E18").Value = ""
Sheets("January").Range("G18:AP18").Value = ""
Sheets("January").Range("G17:AP17").Value = ""
Sheets("January").Range("G16:AP16").Value = ""
Sheets("January").Range("G15:AP15").Value = ""
Sheets("January").Range("G14:AP14").Value = ""
Sheets("January").Range("C27:C31").Value = ""
Sheets("January").Range("D27:D31").Value = ""
Sheets("January").Range("E27:E31").Value = ""
Sheets("January").Range("G27:G31").Value = ""
Sheets("January").Range("D34:D34").Value = ""
Sheets("January").Range("H34:H34").Value = ""
Sheets("January").Range("L34:L34").Value = ""
Sheets("January").Range("P34:P34").Value = ""
Sheets("January").Range("T34:T34").Value = ""
Sheets("January").Range("X34:X34").Value = ""
Sheets("January").Range("G27:AP27").Value = ""
Sheets("January").Range("G28:AP28").Value = ""
Sheets("January").Range("G29:AP29").Value = ""
Sheets("January").Range("G30:AP30").Value = ""
Sheets("January").Range("G31:AP31").Value = ""
Sheets("January").Range("C40:C44").Value = ""
Sheets("January").Range("D40:D44").Value = ""
Sheets("January").Range("E40:E44").Value = ""
Sheets("January").Range("D47:D47").Value = ""
Sheets("January").Range("G40:G44").Value = ""
Sheets("January").Range("G40:AP40").Value = ""
Sheets("January").Range("G41:AP41").Value = ""
Sheets("January").Range("G42:AP42").Value = ""
Sheets("January").Range("G43:AP43").Value = ""
Sheets("January").Range("G44:AP44").Value = ""
Sheets("January").Range("H47:H47").Value = ""
Sheets("January").Range("L47:L47").Value = ""
Sheets("January").Range("P47:P47").Value = ""
Sheets("January").Range("T47:T47").Value = ""
Sheets("January").Range("X47:X47").Value = ""
Sheets("January").Range("C53:C57").Value = ""
Sheets("January").Range("D53:D57").Value = ""
Sheets("January").Range("E53:E57").Value = ""
Sheets("January").Range("G53:G47").Value = ""
Sheets("January").Range("G53:AP53").Value = ""
Sheets("January").Range("G54:AP54").Value = ""
Sheets("January").Range("G55:AP55").Value = ""
Sheets("January").Range("G56:AP56").Value = ""
Sheets("January").Range("G57:AP57").Value = ""
Sheets("January").Range("D60:D60").Value = ""
Sheets("January").Range("H60:H60").Value = ""
Sheets("January").Range("L60:L60").Value = ""
Sheets("January").Range("P60:P60").Value = ""
Sheets("January").Range("T60:T60").Value = ""
Sheets("January").Range("X60:X60").Value = ""
Sheets("January").Range("C66:C70").Value = ""
Sheets("January").Range("D66:D70").Value = ""
Sheets("January").Range("E66:E70").Value = ""
Sheets("January").Range("G66:G70").Value = ""
Sheets("January").Range("D73:D73").Value = ""
Sheets("January").Range("G66:AP66").Value = ""
Sheets("January").Range("G67:AP67").Value = ""
Sheets("January").Range("G68:AP68").Value = ""
Sheets("January").Range("G69:AP69").Value = ""
Sheets("January").Range("G70:AP70").Value = ""
Sheets("January").Range("H73:H73").Value = ""
Sheets("January").Range("L73:L73").Value = ""
Sheets("January").Range("P73:P73").Value = ""
Sheets("January").Range("T73:T73").Value = ""
Sheets("January").Range("X73:X73").Value = ""

MsgBox "Values Successfully Reset!"

End Sub

Answer 1

假设表January是 12 张纸中的一张，并且所有这些纸都具有完全相同的布局，并且都需要类似的“清理”宏，我会按如下方式进行。

首先，如果您还没有将标准模块添加到您的 VBA 项目中。给它一个有意义的名字，例如CleanUpMacro .

该模块看起来像这样 - 它有点忙，因为在清除的内容中似乎没有任何明确的模式。此外，您的代码多次清除多个单元格；可以简化几个范围。

'@Folder("Macros")
Option Explicit

Public Sub CleanUpFields(ByVal monthSheet As Worksheet)
    With monthSheet
        Dim toClear As Range
        Set toClear = .Range("D3,D5,G3,G4,D21,H21,L21,P21,T21,X21")
        Set toClear = Union(toClear, .Range("C14:E18,G14:AP18,C27:E31,G27:G31"))
        Set toClear = Union(toClear, .Range("D34,H34,L34,P34,T34,X34"))
        '...
        toClear.ClearContents

        MsgBox "'" & .Name & "' sheet cleared!"
    End With
End Sub

请注意 ,逗号正在合并范围；您想要多个 Union 的原因是使列表有条理，地址字符串在 255 个字符以下。

如果范围对应于特定部分，请为其命名。然后 .Range("theGivenName")将引用这些命名范围，并且我相信它们可以是不连续的，因此您可能只需要 monthSheet.Range("AllEditableFields").ClearContents , 如果每个月表都定义了一个工作表范围的 AllEditableFields命名范围 - 这样做的好处是它抽象出实际的单元格地址，这意味着如果有人在某处插入行或列，无论出于何种原因，您的代码都不会中断。与引用一堆单个特定单元格的 200 行 VBA 宏相比，维护一堆命名范围也容易得多。

现在您需要 12 个调用此过程的宏，每个宏都传递一个不同的工作表。

因此，一月工作表的代码隐藏可能如下所示:

'@Folder("MonthlyWorkheets")
Option Explicit

'@Ignore ProcedureNotUsed
Public Sub CleanUpFields()
    CleanUpMacro.CleanUpFields Me
End Sub

二月工作表的代码隐藏可能如下所示:

'@Folder("MonthlyWorkheets")
Option Explicit

'@Ignore ProcedureNotUsed
Public Sub CleanUpFields()
    CleanUpMacro.CleanUpFields Me
End Sub

而 March 表的代码隐藏可能如下所示:

'@Folder("MonthlyWorkheets")
Option Explicit

'@Ignore ProcedureNotUsed
Public Sub CleanUpFields()
    CleanUpMacro.CleanUpFields Me
End Sub

而且......好吧，你明白了:)

那时剩下要做的就是连接一些 Shape在工作表上运行工作表的 CleanUpFields单击宏时，我们就完成了。

如果您使用 ActiveX CommandButton ，那么您的工作表的代码隐藏可能如下所示:

'@Folder("MonthlyWorkheets")
Option Explicit

'@Ignore ProcedureNotUsed
Private Sub CleanUpButton_Click()
    CleanUpMacro.CleanUpFields Me
End Sub