shades
04-05-2005, 06:39 AM
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?
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?