userinterfaceonly：= true似乎不允许VBA更改为条件格式化？ [英] userinterfaceonly:=true doesn't seem to allow VBA changes to conditional formatting?

问题描述

我正在通过Worksheet_Change事件运行一段代码，并且我在事件开始时使用一个调用unprotect子，并在事件结束时匹配一个保护子对象。这是按预期工作的。

我正在设法保护用户界面：= true在工作簿打开事件中，以消除每次不需要保护和重新保护的需要更改事件触发（更多的是探索功能比任何其他）。问题是，如果工作表受到保护（即使使用UserInterfaceOnly = True），在锁定范围内更改条件格式的代码也将不可避免地出现错误（即使使用UserInterfaceOnly = True），尽管它可以正常工作，如果工作表手动不受保护或通过VBA取消保护该行之前的工作表并且在该行之后重新保护。

我已经玩了一点，似乎在与锁定的单元格上更改条件格式化有关的任何错误。我想知道如果在UserInterfaceOnly的范围内不允许在页面受保护的情况下更改锁定范围的条件格式？如果有人知道这是否是一个限制，如果这个论点有其他的限制，那将是非常棒的。

在我的这个工作簿模块中，我有：

  Private Sub Workbook_open（）
 
 ActiveSheet.Protect userinterfaceOnly：= True 
 
 End Sub 
  

在我的工作表模块中，我有以下 - FormatConditions.Delete行错误运行时错误1004：当案例解析为true时，应用程序或对象定义错误。

  Private Sub Worksheet_Change（ByVal Target As Range ）
如果Target.Address =$ D $ 6然后
 Application.ScreenUpdating = False 
选择案例范围（D6）
案例选择函数
范围（F6）。值=（）
范围（H4：I4）。选择
 Selection.FormatConditions.Delete<<<<错误
 Selection.ClearC ontents 
调用DeleteButtons 
调用HideAll 
范围（D6）。选择
案例商品In& 
 Range（F6）Value =（EXPLANATORY TEXT）
调用DeleteButtons 
范围（D10：F10）。ClearContents 
调用UnHideAll 
 Call HideCollection 
调用FillDelivery 
调用GIRButtons 
范围（D10）。选择
案例Collection& 
范围（F6）Value =（EXPLANATORY TEXT）
调用DeleteButtons 
调用UnHideAll 
调用HideGoodsIn 
调用ClearDelivery 
调用CRButtons 
范围（H4）。选择
案例仅交付
范围（F6）。值=（EXPLANATORY TEXT）
调用DeleteButtons 
调用UnHideAll 
调用HideGoodsInCollection 
调用ClearDelivery 
调用DelButtons 
范围（H4）。选择
结束选择
 Application.ScreenUpdating = True 
结束如果
结束Sub 
  

感谢您的任何见解。

解决方案

与 .Protect UserInterfaceOnly：= True 一起使用 AllowFormattingCells： = True 。这允许条件格式化g修改受保护的单元格。

I'm running a piece of code via the Worksheet_Change Event and I have it working with a call to an unprotect sub at the beginning of the event and a matching call to a protect sub at the end of the event. This works as expected.

I'm trying to work with setting the protection to userinterfaceonly:=true in the workbook open event to negate the need to unprotect and reprotect each time the change event fires (more just to explore the functionality than anything else). Problem is that the code to change conditional formatting on a locked range inevitably errors if the worksheet is protected (even with UserInterfaceOnly = True) although it works fine and as expected if the worksheet is unprotected either manually or by VBA unprotecting the sheet before that line and re-protecting after that line.

I've played around a bit and it seems to error on anything to do with changing conditional formatting on a locked cell. I wonder if changing conditional format on a locked range while a sheet is protected isn't allowed under the scope of UserInterfaceOnly? If anyone knows if this is a limitation and if there are any other limitations to this argument, it would be great to know.

In my 'This Workbook' module I have:

Private Sub Workbook_open()

ActiveSheet.Protect userinterfaceOnly:=True

End Sub

In my worksheet's module, I have the below - the FormatConditions.Delete line errors with a runtime error 1004: Application or Object Defined Error when that Case resolves to true.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$D$6" Then
    Application.ScreenUpdating = False
        Select Case Range("D6")
            Case "Select Function"
                Range("F6").Value = ("")
                Range("H4:I4").Select
                    Selection.FormatConditions.Delete <<<<<<THIS LINE ERRORS
                    Selection.ClearContents
                Call DeleteButtons
                Call HideAll
                Range("D6").Select
            Case "Goods In & Redelivery"
                Range("F6").Value = ("EXPLANATORY TEXT")
                Call DeleteButtons
                Range("D10:F10").ClearContents
                Call UnHideAll
                Call HideCollection
                Call FillDelivery
                Call GIRButtons
                Range("D10").Select
            Case "Collection & Redelivery"
                Range("F6").Value = ("EXPLANATORY TEXT")
                Call DeleteButtons
                Call UnHideAll
                Call HideGoodsIn
                Call ClearDelivery
                Call CRButtons
                Range("H4").Select
            Case "Delivery Only"
                Range("F6").Value = ("EXPLANATORY TEXT")
                Call DeleteButtons
                Call UnHideAll
                Call HideGoodsInCollection
                Call ClearDelivery
                Call DelButtons
                Range("H4").Select
        End Select
    Application.ScreenUpdating = True
    End If
End Sub

Any insights gratefully appreciated.

解决方案

Along with .Protect UserInterfaceOnly:=True, use AllowFormattingCells:=True. This allows conditional formatting to modify protected cells.

这篇关于userinterfaceonly：= true似乎不允许VBA更改为条件格式化？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！