Using the Protect Workseet function with the attached code - please help.

Tanner_2004

Well-known Member
Joined
Jun 1, 2010
Messages
616
This code works great...UNLESS, I protect the worksheet. I need to protect the worksheet because it contains cells with formulaes. My assumption is that the hiding of row 27 upon clicking the check box is giving me the trouble - the code is opened asking me to debug. Any ideas/solutions would be greatly appreciated. Thank you!

Private Sub CheckBox1_Click()
Application.ScreenUpdating = False
If CheckBox1.Value = True Then
Rows(27).EntireRow.Hidden = False
Worksheets("Scenarios").Visible = True
Worksheets("Scenario Charts").Visible = True


Else
Rows(27).EntireRow.Hidden = True
Worksheets("Scenarios").Visible = False
Worksheets("Scenario Charts").Visible = False
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try like this

Rich (BB code):
Private Sub CheckBox1_Click()
Application.ScreenUpdating = False
ActiveSheet.Unprotect Password:="abc"
If CheckBox1.Value = True Then
Rows(27).EntireRow.Hidden = False
Worksheets("Scenarios").Visible = True
Worksheets("Scenario Charts").Visible = True


Else
Rows(27).EntireRow.Hidden = True
Worksheets("Scenarios").Visible = False
Worksheets("Scenario Charts").Visible = False
ActiveSheet.Protect Password:="abc"
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,374
Office Version
  1. 2019
Platform
  1. Windows
try this:

Code:
Private Sub CheckBox1_Click()
Application.ScreenUpdating = False
With Me
If .CheckBox1.Value = True Then
.Unprotect Password:="" '< add password if requred
.Rows(27).EntireRow.Hidden = False
Worksheets("Scenarios").Visible = True
Worksheets("Scenario Charts").Visible = True

Else
.Rows(27).EntireRow.Hidden = True

Worksheets("Scenarios").Visible = False
Worksheets("Scenario Charts").Visible = False
End If
.Protect Password:=""
End With
Application.ScreenUpdating = True

End Sub

dave
 
Last edited:

Tanner_2004

Well-known Member
Joined
Jun 1, 2010
Messages
616
try this:

Code:
Private Sub CheckBox1_Click()
Application.ScreenUpdating = False
With Me
If .CheckBox1.Value = True Then
.Unprotect Password:="" '< add password if requred
.Rows(27).EntireRow.Hidden = False
Worksheets("Scenarios").Visible = True
Worksheets("Scenario Charts").Visible = True

Else
.Rows(27).EntireRow.Hidden = True

Worksheets("Scenarios").Visible = False
Worksheets("Scenario Charts").Visible = False
End If
.Protect Password:=""
End With
Application.ScreenUpdating = True

End Sub

dave

Thank you, but I get the same run time 1004 error. Unable to set the Hidden property of the range class

BTW, I do not add a password; O ijust protect the sheet via the "Protect Sheet" command under the Review section of the ribbon. Thans again!
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Then

Code:
Private Sub CheckBox1_Click()
Application.ScreenUpdating = False
ActiveSheet.Unprotect
If CheckBox1.Value = True Then
Rows(27).EntireRow.Hidden = False
Worksheets("Scenarios").Visible = True
Worksheets("Scenario Charts").Visible = True


Else
Rows(27).EntireRow.Hidden = True
Worksheets("Scenarios").Visible = False
Worksheets("Scenario Charts").Visible = False
ActiveSheet.Protect
 

Tanner_2004

Well-known Member
Joined
Jun 1, 2010
Messages
616
Thanks again. The "problem" is it works fine until I place a check in the checkbox. It appears to unprotect entire worksheet after it is checked. I do not not what to protect cell H27 (there is no formulae in it). But ir is the row that I hide when the checkbox is unchecked, then unhidden when the check box is checked.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,521
Messages
5,596,645
Members
414,083
Latest member
Mrsash

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top