Page Protection interfering with Hiding Rows

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

[email protected] wrote:
> 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

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:
> Private Sub passdeny_Click()
> ActiveSheet.Unprotect password:="yourpassword"
> DENYPASS
> ActiveSheet.Protect password:="yourpassword"
> End Sub
>
> James
>
> [email protected] wrote:
> > 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

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

"[email protected]" wrote:

> 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
>
>

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:
> 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
>
> "[email protected]" wrote:
>
> > 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
> >
> >

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



[email protected] 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:
> > 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
> >
> > "[email protected]" wrote:
> >
> > > 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
> > >
> > >