Page Protection interfering with Hiding Rows

[micheldevon]

have a sheet that hides groups of rows based on a cell's information.
I'd prefer this to happen automatically through VBA, but currently I'm
stuck using a Toggle Button to hide/unhide these rows.

The problem I run into though is that that toggle button works
brilliantly until I turn on the page protection. Once I turn on the
protection I keep getting errors that the page is protected and can't
be updated.


And due to the fact that the people who will be using this know
absolutely no Excel at all and break formulas on the old sheet
regularly... well, the sheet has to be write-protected.


The code I'm currently using is this:
Sub DENYPASS()


Application.ScreenUpdating = False
Application.EnableEvents = False


If Range("B2") = 1 Then
Rows("11:41").EntireRow.Hidden = True
Rows("42:72").EntireRow.Hidden = False
Else
Rows("11:41").EntireRow.Hidden = False
Rows("42:72").EntireRow.Hidden = True
End If


Application.ScreenUpdating = True
Application.EnableEvents = True


End Sub


with a private sub to connect it to the Toggle Button


Private Sub passdeny_Click()
DENYPASS
End Sub

 

[Zone]

Private Sub passdeny_Click()
ActiveSheet.Unprotect password:="yourpassword"
DENYPASS
ActiveSheet.Protect password:="yourpassword"
End Sub

James

 

[micheldevon]

Well, shoot. I was excited that there was an answer that was that
simple. I had tried the password unprotect/protect earlier but had the
code in the SUB rather than the PRIVATE SUB, and so I was excited to
see something so simple.

Alas, it doesn't work for some reason. Crap.
Thanks anyway, James.

 

[jweasl]

Have you tried unprotecting the cells they can change and then protecting the
page? That way they can edit only what you want them to be able to edit

 

[micheldevon]

Yep. The problem with that approach it seems, is that I'm trying to put
two pages of information on one page. If they need page "A" they press
the toggle button and it hides page "B"s rows, and if they need page
"B" - vice versa.

The problem comes in with the hiding and unhiding of rows. Since some
rows have protected cells, some don't, and some have both, the Macro
just tells me that it can't run because of the protected cells.

Basically it comes down to: I was trying to minimize the number of
sheets in the workbook (not necessarily the size of the file) to appeal
to the lowest common denominator of mentality, but I may have to put
the third sheet back in.

 

[Zone]

Well, you could do this. Rather simple-minded, it just unprotects both
sheets for the duration of the sub and then reprotects them.

Private Sub passdeny_Click()
Worksheets("Sheet1").Unprotect password:="yourpassword1"
Worksheets("Sheet2").Unprotect password:="yourpassword2"
DENYPASS
Worksheets("Sheet1").Protect password:="yourpassword1"
Worksheets("Sheet2").Protect password:="yourpassword2"
End Sub

James