Page Protection interfering with Hiding Rows

[[email protected]]

I 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

[Tom Hutchins]

Protect your sheet using VBA, and include the parameter
UserInterfaceOnly:=True

Sheets("Sheet1").Protect UserInterfaceOnly:=True

This protects the sheet but astill llows macros to manipulate it.

Hope this helps,

Hutch

"[email protected]" wrote:

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

[[email protected]]

So something like this?

Sub ProtectTCR()
Sheets("TCR").Protect password:="TCR", UserInterfaceOnly:=True
End Sub


If this is correct, then how do I enable it? I've placed it in the VBA
Object for this sheet. Does it need to be in the "ThisWorkbook"
instead? Or in a module?


Tom Hutchins wrote:
> Protect your sheet using VBA, and include the parameter
> UserInterfaceOnly:=True
>
> Sheets("Sheet1").Protect UserInterfaceOnly:=True
>
> This protects the sheet but astill llows macros to manipulate it.
>
> Hope this helps,
>
> Hutch
>
> "[email protected]" wrote:
>
> > I 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
> >
> >

[Tom Hutchins]

You run it like any other macro. The module for that sheet, the ThisWorkbook
module, or a VBA module will all work. From Excel, select Tools >> Macro >>
Macros. Select ProtectTCR and click Run. To protect the sheet automatically
when the workbook is opened, place the Protect statement in a Workbook_Open
event (in the ThisWorkbook module).

Regards, Hutch

"[email protected]" wrote:

> So something like this?
>
> Sub ProtectTCR()
> Sheets("TCR").Protect password:="TCR", UserInterfaceOnly:=True
> End Sub
>
>
> If this is correct, then how do I enable it? I've placed it in the VBA
> Object for this sheet. Does it need to be in the "ThisWorkbook"
> instead? Or in a module?
>
>
> Tom Hutchins wrote:
> > Protect your sheet using VBA, and include the parameter
> > UserInterfaceOnly:=True
> >
> > Sheets("Sheet1").Protect UserInterfaceOnly:=True
> >
> > This protects the sheet but astill llows macros to manipulate it.
> >
> > Hope this helps,
> >
> > Hutch
> >
> > "[email protected]" wrote:
> >
> > > I 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
> > >
> > >
>
>