Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Protect, unprotect sheet from checkbox control

  Asked By: Koila    Date: Jan 04    Category: MS Office    Views: 1015
  

Trying to password protect sheet1 by clicking on the check box. I
recorded a macro to protect and unprotect. I double clicked the
check box control and put an if,then statement in that would call
the aforemetioned macros; however, it's not working. What am I doing
wrong?

Private Sub CheckBox1_Click()
Dim LinkedCell As Boolean

'LinkedCell is a named range for A3 - A3 is where the
'checkbox result is stored

If LinkedCell = 1 Then
Call Protect
Else
Call Unprotect
End If

End Sub

*************

Here are the protect, unprotect macros (in module 1)

Sub Protect()
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True
End Sub

Sub Unprotect()
ActiveSheet.Unprotect
End Sub

**************

Share: 

 

2 Answers Found

 
Answer #1    Answered By: Eileen Carr     Answered On: Jan 04

Take out the Dim line, refer to Linked cell as a named  range, not as a
variable, ensure both the chackbox and cell A3 are not locked, leaving
something like:

Private Sub CheckBox1_Click()
If Range("LinkedCell") Then
Call Protect
Else
Call Unprotect
End If
End Sub

But why don't you do away with A3 altogether and just examine the
checkbox's value?:

Private Sub CheckBox1_Click()
If ActiveSheet.CheckBox1.Value Then
Call Protect
Else
Call Unprotect
End If
End Sub

 
Answer #2    Answered By: Phoebe Brown     Answered On: Jan 04

A few things to help troubleshoot this thing would be to view the
Locals Window so you can see the values of all your variables, then
place a break point right at the begining of your CheckBox1_Click
subroutine. Then, click the check  box and step through it by
pressing F8.

You will see that the value of LinkedCell is not being passed to
your subroutine. See the code below for a solution to this and some
other minor things.

Private Sub CheckBox1_Click()
Dim LinkedCell As Boolean

'LinkedCell is a named  range for A3 - A3 is where the
'checkbox result  is stored

'*You need to transfer the value from
'*the named range  into a variable.
'*The same name can be used.
LinkedCell = Range("LinkedCell")

'*If LinkedCell = 1 Then
'*Since LinkedCell is a boolean,
'*change to the following
If LinkedCell Then
Call Protect
Else
Call Unprotect
End If

End Sub
Sub Protect()
'*make sure LinkedCell is not locked
'*or we won't be able to change back
'*to unprotected
Range("LinkedCell").Locked = False
ActiveSheet.Protect DrawingObjects:=True, _
Contents:=True, Scenarios:=True
End Sub

Sub Unprotect()
ActiveSheet.Unprotect
End Sub

 
Didn't find what you were looking for? Find more on Protect, unprotect sheet from checkbox control Or get search suggestion and latest updates.




Tagged: