vba - 当工作表在 MS-Excel 中受到保护时，如何避免运行时错误？

Question

下面的代码片段更改单元格的数据验证状态，并在 Excel-2003 工作表不 protected 时运行。但是，当我保护工作表时，宏不会运行并引发运行时错误

Run-time error '-2147417848 (80010108)':

Method 'Add' of object 'Validation' failed

我尝试用

包装代码

Me.unprotect
...
Me.protect

但这不能正常工作。那么，当工作表受到保护而不会出现上述运行时错误时，如何修改下面的代码才能工作(即让代码修改解锁单元格的验证)？

更新

我原来的工作簿是Excel 2003。我测试过@eJames Excel 2007 中的解决方案，具有以下 Workbook_Open 定义

Sub WorkBook_Open()
    Me.Worksheets("MainTable").Protect  contents:=True, userinterfaceonly:=True 
End Sub

当工作表受到保护时，代码仍然失败，并出现以下运行时错误

Run-time error '1004': Application-defined or object-defined error

谢谢，阿齐姆

<小时/>

代码片段

'cell to add drop down validation list'
dim myNamedRange as String
dim modifyCell as Range 
modifyCell = ActiveCell.Offset(0,1) 


' set list values based on some conditions not defined for brevitity'
If myCondition then
   myNamedRange = "range1"
Else
   myNamedRange = "range2"
End If

With modifyCell.Validation
   .Delete

   'Run time error occurs on the next line'
   .Add Type:=xlValidateList, AlertStyle:=xlValidAltertStop, _
        Operator:=xlBetween, Formula1:="=" & myNamedRange

   ... 
   ' skipping more property setting code '
   ...
End With

Answer 1

如果我正确理解了这个问题，你将是保护这张表的人。如果是这种情况，您可以使用以下 VBA:

myWorksheet.Protect contents:=True, userinterfaceonly:=True

这里的关键部分是“userinterfaceonly:=true”。当使用此标志集保护工作表时，仍然允许 VBA 宏进行更改。

将此代码放入 WorkBook_Activate 事件中，以自动保护工作簿并在激活时设置该标志。

编辑:感谢Lance Roberts他建议使用 Workbook_Activate 而不是 Workbook_Open。

编辑:由于上述方法似乎不起作用，您可能必须使用 unprotect/protect 命令来包装 VBA 代码的失败部分。如果您这样做，我还会用错误处理程序包装整个宏，以便在发生错误后工作表不会不 protected :

Sub MyMacro
    On Error Goto HandleError

    ...

    myWorksheet.unprotect
    With ModifyCell.Validation
        ...
    End With
    myWorksheet.protect contents:=True, userinterfaceonly:=True

    ...

Goto SkipErrorHandler
HandleError:
    myWorksheet.protect contents:=True, userinterfaceonly:=True
    ... some code to present the error message to the user
SkipErrorHandler:
End Sub

编辑:看看this thread在 PC 评论。他们经历了几乎相同的步骤，并得出了相同的结论。至少你并不孤单!