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

Question

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

运行时错误'-2147417848 (80010108)'： 方法“添加”对象的“验证”失败

我试过用

Me.unprotect
...
Me.protect

但这并不能正常工作。那么，当工作表在没有上述运行时错误的情况下，如何修改下面的代码来工作(即让代码修改未锁定的单元格的验证)？

更新

我的原著是Excel 2003。我在Excel2007中使用以下定义测试了@eJames解决方案

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

Answer 1

我不确定这是否是一个通用的解决方案，但是当我最近遇到这个错误时，我必须先做一个MywkSheet.Activate，然后再做Validation.Add。所以：

' 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 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

在我的例子中，ScreenUpdating已经关闭了，所以用户从未看到工作表来回切换。HTH。

Answer 2

问题是，要使VBA能够编辑受保护的工作表，必须使用"UserInterfaceOnly“参数设置为True来保护工作表。但是，保存工作簿时不会保存UserInterfaceOnly参数，因此，如果工作簿关闭并重新打开，将发生错误。

这里有一个解决方案：

1. 创建一个保护所有工作表的子表，并将UserInterfaceOnly参数设置为true。我将其拆分为ProtectSheet和ProtectAll subs：

_

Sub ProtectSheet(SheetName As String)
    Dim pswd As String
    pswd = "pass" ' YOU SHOULD GET THE PASSWORD FROM SOMEWHERE SECURE, NOT HARDCODE IT

    ThisWorkbook.Worksheets(SheetName).Protect Password:=pswd, DrawingObjects:=True, Contents:=True, Scenarios:=True, _
        UserInterfaceOnly:=True, AllowFormattingCells:=False, AllowFormattingColumns:=False, _
        AllowFormattingRows:=False, AllowInsertingColumns:=False, AllowInsertingRows:=False, _
        AllowInsertingHyperlinks:=False, AllowDeletingColumns:=False, AllowDeletingRows:=False, _
        AllowSorting:=False, AllowFiltering:=False, AllowUsingPivotTables:=False

End Sub


Sub ProtectAll()
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        ProtectSheet (ws.Name)
    Next ws
End Sub

1. 在ThisWorkbook中，当工作簿打开时，保护所有工作表：

Private Sub Workbook_Open()
    Call ProtectAll ' Protect all with UserInterfaceOnly set to true so VBA can edit
End Sub