vba 在 MS-Excel 中保护工作表时，如何避免运行时错误？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/445519/
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
How do I avoid run-time error when a worksheet is protected in MS-Excel?
提问by Azim
The code snippet below changes the data validation state of a cell and runs when the Excel-2003 worksheet is unprotected. However, when I protect the work sheet the macro doesn't run and raises a run-time error
下面的代码片段更改单元格的数据验证状态，并在 Excel-2003 工作表不受保护时运行。但是，当我保护工作表时，宏不会运行并引发运行时错误
Run-time error '-2147417848 (80010108)':
Method 'Add' of object 'Validation' failed
运行时错误“-2147417848 (80010108)”：
对象“验证”的方法“添加”失败
I have tried wrapping the code with
我试过用
Me.unprotect
...
Me.protect
But this does not work properly. So, how can I modify the code below to work (ie have the code modify the unlocked cell's validation) when the sheet is protected without the above run-time error?
但这不能正常工作。那么，当工作表受到保护而没有上述运行时错误时，如何修改下面的代码以使其工作（即让代码修改解锁单元格的验证）？
Update
更新
My original work book is an Excel 2003. I tested @eJamessolution in Excel 2007 with the following definition for Workbook_Open
我的原始工作簿是 Excel 2003。我在 Excel 2007 中使用以下 Workbook_Open 定义测试了@eJames解决方案
Sub WorkBook_Open()
Me.Worksheets("MainTable").Protect contents:=True, userinterfaceonly:=True
End Sub
The code still fails with the following run-time error when the worksheet is protected
当工作表受到保护时，代码仍然失败并出现以下运行时错误
Run-time error '1004': Application-defined or object-defined error
运行时错误“1004”：应用程序定义或对象定义错误
Thanks, Azim
谢谢，阿齐姆
Code Snippet
代码片段
'cell to add drop down validation list'
dim myNamedRange as String
dim modi as Range
modi = 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 modi.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
回答by e.James
If I understand the question correctly, you will be the one protecting the sheet. If that is the case, you can use the following VBA:
如果我正确理解了这个问题，那么您将成为保护工作表的人。如果是这种情况，您可以使用以下 VBA：
myWorksheet.Protect contents:=True, userinterfaceonly:=True
The key part here is "userinterfaceonly:=true". When a worksheet is protected with this flag set, VBA macros are still allowed to make changes.
这里的关键部分是“userinterfaceonly:=true”。当使用此标志集保护工作表时，仍允许 VBA 宏进行更改。
Place this code into the WorkBook_Activateevent to automatically protect the workbook and set the flag whenever it is activated.
将此代码放入WorkBook_Activate事件中以自动保护工作簿并在激活时设置标志。
Edit:Thanks to Lance Robertsfor his recommendation to use Workbook_Activateinstead of Workbook_Open.
编辑：感谢兰斯·罗伯茨，他建议使用Workbook_Activate来代替Workbook_Open。
Edit:Since the above didn't seem to work, you may have to wrap the failing portion of your VBA code with unprotect/protect commands. If you do that, I would also wrap the entire macro with an error handler, so that the sheet is not left unprotected after an error:
编辑：由于上述内容似乎不起作用，您可能必须使用 unprotect/protect 命令包装 VBA 代码的失败部分。如果你这样做，我也会用一个错误处理程序包装整个宏，这样工作表就不会在发生错误后不受保护：
Sub MyMacro
On Error Goto HandleError
...
myWorksheet.unprotect
With Modi.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
Edit:Have a look at this threadat PCreview. They went through much the same steps, and came to the same conclusion. At least you're not alone!
编辑：在 PCreview 上查看此线程。他们经历了几乎相同的步骤，并得出了相同的结论。至少你不是一个人！
回答by Peter
I'm not sure if this is a universal solution, but when I've had this error recently, I just had to do a MywkSheet.Activate right before I did the Validation.Add. So:
我不确定这是否是通用解决方案，但是当我最近遇到此错误时，我只需要在执行 Validation.Add 之前执行 MywkSheet.Activate。所以：
' set list values based on some conditions not defined for brevitity'
If myCondition then
myNamedRange = "range1"
Else
myNamedRange = "range2"
End If
''--------------------------------------------------
Sheets("mysheet").Activate
''--------------------------------------------------
With modi.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
in my case, ScreenUpdating was off already, so the user never sees the sheets switching back and forth. HTH.
就我而言，ScreenUpdating 已经关闭，因此用户永远不会看到工作表来回切换。哈。
