Conflict Protect and WS_Calculation

[shades]

I have updated a template that changes by quarter. The worksheet_Calculate code looks at the data and changes how many rows are visible, depending on whether there is data in the underlying data base. It works great.


Private Sub Worksheet_Calculate()
Dim i As Integer
Application.ScreenUpdating = False
For i = 10 To 20
Select Case Cells(i, 2).Value
Case 0
Worksheets("QtrCharts").Rows(i & ":20").EntireRow.Hidden = True
Case Else
Worksheets("QtrCharts").Rows(i & ":20").EntireRow.Hidden = False
End Select
Next i
Application.ScreenUpdating = True
End Sub

However, I send this template to about 80 people throughout the US, and I have always used Worksheet Protection (to prevent accidental changes). Until I added the above code, the protection works fine.


Sub ProtectAll()
Dim ws As Worksheet
Application.ScreenUpdating = False
For Each ws In ActiveWorkbook.Worksheets
ws.Protect Password:="password", DrawingObjects:=True, _
Contents:=True, Scenarios:=True
Next ws
ActiveWorkbook.Protect Password:="password", _
Structure:=True, Windows:=False
Application.ScreenUpdating = True
End Sub


Now that I have both in the workbook, in the WS_Calculate code, this line of code errors ("Unable to set the Hidden property of the Range class"):



Case Else
Worksheets("QtrCharts").Rows(i & ":20").EntireRow.Hidden = False

When I look at the Protection options, it allows inserting and deleting of rows, but not hiding. Is there a way to accomplish this?

[Zack Barresse]

Turn protection off, make the change, then turn protection back on. It's pretty much standard PITA, I mean practice. Also, I'd suggest throwing in an Application.EnableEvents clause with your ScreenUpdating as the Calculate event will trigger other events when hiding/unhiding rows/columns.

[Jacob Hilderbrand]

Another thing you can use is the UserInterfaceOnly Property. This will protect the worksheet only for the user and macros will be able to run as if the worksheet was not protected.

This property cannot be permanently set though, so you need to set it on the workbook open event or at the beginning of your macro.


Dim ws As Worksheet
For Each ws In Worksheets
ws.Protect Password:="Password", DrawingObjects:=True, _
Contents:=True, Scenarios:=True, UserInterfaceOnly:=True
Next

[shades]

Thanks, guys. Jake, that piece, UserInterfaceOnly:=True, was exactly what I needed. Now I am very happy, because the auto-updating of the table (different sizes) has been well-received, and the protection code is the final capstone. Thank you!

[Jacob Hilderbrand]

Glad to help. :beerchug: