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

[Tanner_2004]

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

 

[VoG]

Try like this

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]

try this:

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

 

[Tanner_2004]

try this:

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]

Then

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]

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.