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

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

运行时错误'-2147417848(80010108)':

对象'验证'的方法'添加'失败

我试过包装代码

Me.unprotect
...
Me.protect

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

更新

我的原始工作簿是Excel 2003.我在Excel 2007中测试了@ eJames解决方案,其中包含Workbook_Open的以下定义

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

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

运行时错误'1004':应用程序定义的错误或对象定义的错误

谢谢,阿齐姆

代码片段

'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

e.James.. 12

如果我正确理解了这个问题,你就会成为保护这张纸的人.如果是这种情况,您可以使用以下VBA:

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

这里的关键部分是"userinterfaceonly:= true".使用此标志设置保护工作表时,仍允许VBA宏进行更改.

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

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

编辑:由于上面似乎不起作用,您可能必须使用unprotect/protect命令包装VBA代码的失败部分.如果你这样做,我也会用一个错误处理程序包装整个宏,这样在发生错误后表格不会不受保护:

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

编辑:看看PCreview 上的这个主题.他们经历了相同的步骤,得出了同样的结论.至少你并不孤单!

如果我正确理解了这个问题,你就会成为保护这张纸的人.如果是这种情况,您可以使用以下VBA:

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

这里的关键部分是"userinterfaceonly:= true".使用此标志设置保护工作表时,仍允许VBA宏进行更改.

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

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

编辑:由于上面似乎不起作用,您可能必须使用unprotect/protect命令包装VBA代码的失败部分.如果你这样做,我也会用一个错误处理程序包装整个宏,这样在发生错误后表格不会不受保护:

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

编辑:看看PCreview 上的这个主题.他们经历了相同的步骤,得出了同样的结论.至少你并不孤单!