Checkbox doesn't work in protected mode

[djkeyclub]

I have an excel sheet in which the majority of it is protected and cells are left open to be filled in by my employees while they cannot manipulate all of them. This is my first time venturing into Macros/VBA, etc. so any help will be appreciated.

I created a checkbox that when clicked, hides multiple rows as well as successfully hides other checkboxes. The code is probably longer than it needs to be and is very simple but I don't know another way to do it at this point. Basically, I want to protect my sheet and the checkbox still work. Can anyone help?

Also, I have the sheet actually named "Employee Information" and I don't know how else to do it except to say "activesheet" though my guess is that my protecting the sheet it no longer recognizes it as an active sheet.

Please help, on a time crunch! I am using Excel2010. Also worth mentioning - when I emailed this to my boss (saved as macro-enabled worksheet), he opened it, enabled the macros and it did not work for him (sheet was unprotected at the time). Any ideas?

Private Sub CheckBox3_Click()



If ActiveSheet.Rows("15:24").Hidden = False Then

ActiveSheet.Rows("15:24").Hidden = True
Else
ActiveSheet.Rows("15:24").Hidden = False
End If



If ActiveSheet.Rows("32:32").Hidden = False Then
ActiveSheet.Rows("32:32").Hidden = True
Else
ActiveSheet.Rows("32:32").Hidden = False
End If



 If ActiveSheet.CheckBox3 = True Then
 ActiveSheet.CheckBox5.Visible = True
 Else
 ActiveSheet.CheckBox5.Visible = False
 End If
 
  If ActiveSheet.CheckBox3 = True Then
 ActiveSheet.CheckBox4.Visible = True
 Else
 ActiveSheet.CheckBox4.Visible = False
 End If
 
  If ActiveSheet.CheckBox3 = True Then
 ActiveSheet.CheckBox6.Visible = True
 Else
 ActiveSheet.CheckBox6.Visible = False
 End If
 
  If ActiveSheet.CheckBox3 = True Then
 ActiveSheet.CheckBox1.Visible = True
 Else
 ActiveSheet.CheckBox1.Visible = False
 End If
 
  If ActiveSheet.CheckBox3 = True Then
 ActiveSheet.CheckBox7.Visible = True
 Else
 ActiveSheet.CheckBox7.Visible = False
 End If
 
  If ActiveSheet.CheckBox3 = True Then
 ActiveSheet.CheckBox8.Visible = True
 Else
 ActiveSheet.CheckBox8.Visible = False
 End If
 
  If ActiveSheet.CheckBox3 = True Then
 ActiveSheet.CheckBox9.Visible = True
 Else
 ActiveSheet.CheckBox9.Visible = False
 End If
 
  If ActiveSheet.CheckBox3 = True Then
 ActiveSheet.CheckBox10.Visible = True
 Else
 ActiveSheet.CheckBox10.Visible = False
 End If
 
  If ActiveSheet.CheckBox3 = True Then
 ActiveSheet.CheckBox11.Visible = True
 Else
 ActiveSheet.CheckBox11.Visible = False
 End If
 
  If ActiveSheet.CheckBox3 = True Then
 ActiveSheet.CheckBox12.Visible = True
 Else
 ActiveSheet.CheckBox12.Visible = False
 End If
 
  If ActiveSheet.CheckBox3 = True Then
 ActiveSheet.CheckBox13.Visible = True
 Else
 ActiveSheet.CheckBox13.Visible = False
 End If
 
  If ActiveSheet.CheckBox3 = True Then
 ActiveSheet.CheckBox14.Visible = True
 Else
 ActiveSheet.CheckBox14.Visible = False
 End If
 
  If ActiveSheet.CheckBox3 = True Then
 ActiveSheet.CheckBox15.Visible = True
 Else
 ActiveSheet.CheckBox15.Visible = False
 End If



End Sub

[Solus Rankin]

Welcome to the forum. Please use code tags to frame your code. To do this click 'Edit Post' under your original post, highlight all the code, and press the # button in the tool bar.

Try this:

Private Sub CheckBox3_Click()

ActiveSheet.Unprotect

If ActiveSheet.Rows("15:24").Hidden = False Then

ActiveSheet.Rows("15:24").Hidden = True
Else
ActiveSheet.Rows("15:24").Hidden = False
End If



If ActiveSheet.Rows("32:32").Hidden = False Then
ActiveSheet.Rows("32:32").Hidden = True
Else
ActiveSheet.Rows("32:32").Hidden = False
End If



If ActiveSheet.CheckBox3 = True Then
ActiveSheet.CheckBox5.Visible = True
Else
ActiveSheet.CheckBox5.Visible = False
End If

If ActiveSheet.CheckBox3 = True Then
ActiveSheet.CheckBox4.Visible = True
Else
ActiveSheet.CheckBox4.Visible = False
End If

If ActiveSheet.CheckBox3 = True Then
ActiveSheet.CheckBox6.Visible = True
Else
ActiveSheet.CheckBox6.Visible = False
End If

If ActiveSheet.CheckBox3 = True Then
ActiveSheet.CheckBox1.Visible = True
Else
ActiveSheet.CheckBox1.Visible = False
End If

If ActiveSheet.CheckBox3 = True Then
ActiveSheet.CheckBox7.Visible = True
Else
ActiveSheet.CheckBox7.Visible = False
End If

If ActiveSheet.CheckBox3 = True Then
ActiveSheet.CheckBox8.Visible = True
Else
ActiveSheet.CheckBox8.Visible = False
End If

If ActiveSheet.CheckBox3 = True Then
ActiveSheet.CheckBox9.Visible = True
Else
ActiveSheet.CheckBox9.Visible = False
End If

If ActiveSheet.CheckBox3 = True Then
ActiveSheet.CheckBox10.Visible = True
Else
ActiveSheet.CheckBox10.Visible = False
End If

If ActiveSheet.CheckBox3 = True Then
ActiveSheet.CheckBox11.Visible = True
Else
ActiveSheet.CheckBox11.Visible = False
End If

If ActiveSheet.CheckBox3 = True Then
ActiveSheet.CheckBox12.Visible = True
Else
ActiveSheet.CheckBox12.Visible = False
End If

If ActiveSheet.CheckBox3 = True Then
ActiveSheet.CheckBox13.Visible = True
Else
ActiveSheet.CheckBox13.Visible = False
End If

If ActiveSheet.CheckBox3 = True Then
ActiveSheet.CheckBox14.Visible = True
Else
ActiveSheet.CheckBox14.Visible = False
End If

If ActiveSheet.CheckBox3 = True Then
ActiveSheet.CheckBox15.Visible = True
Else
ActiveSheet.CheckBox15.Visible = False
End If

ActiveSheet.Protect



End Sub

Also you'll have to make sure that the checkbox has its protected property turned off. You can do this by right clicking the checkbox and going to properties.

[djkeyclub]

thanks for the quick reply! When I ran that code, and went to check the box with the protected sheet, the "unprotect sheet" boxed opened prompting me to put in a password. Any ideas on how to avoid that?

[djkeyclub]

thanks for the quick reply! When I ran that code, and went to check the box with the protected sheet, the "unprotect sheet" boxed opened prompting me to put in a password. Any ideas on how to avoid that?

[Solus Rankin]

Please see post #2 to amend your original post before we proceed.

[djkeyclub]

Original post amended

[djkeyclub]

well, I think I figured it out. I just added the below statement before I started my code and it worked like a charm! Now, I just need to figure out why when I email it to my boss it doesn't work for him.

ActiveSheet.Protect "user_password", UserInterFaceOnly:=True