can't hide cells using vba when sheet is protected

[andrewb90]

Hello all,

I have a worksheet change event that hides/unhides rows, but when my sheet is protected it doesn't work. now, the sheet is protected in vba uing this:

    ActiveSheet.Protect "password", _    DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowFormattingCells:=True, AllowFormattingColumns:=True, _
        AllowFormattingRows:=True
    Application.ScreenUpdating = True

My understanding is that "AllowFormattingRows" should allow hiding of rows.

This line of code pops an error: 1004: Unable to set the hidden property of the range class.

Cells.EntireRow.Hidden = False

any suggestions out there?

 

[Kenneth Hobson]

Add Protect's UserfaceOnly:=True option. Then your code can make changes.

 

[andrewb90]

I have this:

    ActiveSheet.Protect "password", DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowFormattingCells:=True, AllowFormattingColumns:=True, UserfaceOnly:=True

and I get a runtime error 1004. Did I not put it in the correct spot?

 

[Michael M]

There is a typo in your text

ActiveSheet.Protect "password", DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowFormattingCells:=True, AllowFormattingColumns:=True, User[color=red]Inter[/color]faceOnly:=True

 

[Kenneth Hobson]

Sorry, I misspelled it. UserInterfaceOnly is the parameter's name. Itellisense will show the proper parameter names as you type.

Here are some examples where intellisense works.

Sub Test()
  Dim ws As Worksheet

  Sheet1.Protect "password", DrawingObjects:=True, Contents:=True, Scenarios:=True _
    , AllowFormattingCells:=True, AllowFormattingColumns:=True, UserinterfaceOnly:=True
    
  Sheet1.Protect "password", True, True, True, True, True, True
  
  Set ws = ActiveSheet
  ws.Protect "password", DrawingObjects:=True, Contents:=True, Scenarios:=True _
    , AllowFormattingCells:=True, AllowFormattingColumns:=True, UserinterfaceOnly:=True
    
  ws.Protect "password", True, True, True, True, True, True
End Sub