Board index » Excel » Page Protection interfering with Hiding Rows

Excel6
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


-

Private Sub passdeny_Click()

ActiveSheet.Unprotect password:="yourpassword"

DENYPASS

ActiveSheet.Protect password:="yourpassword"

End Sub



James



micheldevon@gmail.com wrote:

-

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.





Zone wrote:

-

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@gmail.com" wrote:

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.





jweasl wrote:

-

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







micheldevon@gmail.com wrote:

-