代码之家 › 专栏 › 技术社区 › Azim J

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

excel-vba vba excel

10

Azim J · 技术社区 · 16 年前

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

对象“验证”的方法“添加”失败

Me.unprotect
...
Me.protect

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

使现代化

我的原著是一本Excel2003。我测试了@ eJames Excel 2007中具有以下工作簿定义的解决方案\u打开

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

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

应用程序定义或对象定义错误

谢谢,阿齐姆

代码片段

'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

2 回复 | 直到 8 年前

1

12

Community CDub 8 年前

如果我理解正确,你就是保护床单的人。如果是这种情况,可以使用以下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

编辑: 看看 this thread 在PCreview。他们经历了几乎相同的步骤,得出了相同的结论。至少你不是一个人!

2

Peter 15 年前

' 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

在我的例子中,屏幕更新已经关闭,所以用户永远不会看到页面来回切换。嗯。

3

1

Dallin Romney 3 年前

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

这里有一个解决方案:

创建保护所有工作表的子项,并将UserInterfaceOnly参数设置为true。我将其拆分为ProtectSheet和ProtectAll子组件:

_

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

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