Lock Checkbox value

[Kingnothing]

In an excel sheet, I have 30~40 checkboxes. These CheckBoxes are not
linked to any cells, the only purpose that they serve is to label
certain aspects about a patient. i.e. A user would select CheckBox1 if
they have hypertension, CheckBox2 if they have diabetes, so on and so
forth. The CheckBoxes are only there to show certain aspects of a
patient.

I want to make it so that once all appropriate checkboxes have been
set, that their value's are protected after the worksheet is protected.
I have selected the locked property to be true, and after protecting
the sheet a user can still modify the CheckBox's value. How can I
prevent this? For more reasons than you want to know, setting the
enable property to false is not an option.

I had this idea, but it doesn't work.
There exists a named cell "OBRStatus" that contians the text of
"unlocked" or "locked" depending on the protection status of the sheet.
I then wrote this macro:
Private Sub CheckBox1_Click()
If Range("OBRStatus").Value = "LOCKED" Then
If CheckBox1.Value Then
CheckBox1.Value = False
Else
CheckBox1.Value = True
End If
End If
End Sub

You would think that would work right? Just toggling the value back to
it's original state if sheet is locked. Nope, here is what I found out
after I placed a breakpoint at the If Range...
After the CheckBox1.Value is changed, the functions goes to End Sub as
it should, but then it restarts as though the toggling of the value
envoked another CheckBox1_Click event.

If anyone has any ideas on how I can solve this problem please let me
know.
Thanks,
Clay Rose
(e-mail address removed)

 

[ben]

--
When you lose your mind, you free your life.

In an excel sheet, I have 30~40 checkboxes. These CheckBoxes are not
linked to any cells, the only purpose that they serve is to label
certain aspects about a patient. i.e. A user would select CheckBox1 if
they have hypertension, CheckBox2 if they have diabetes, so on and so
forth. The CheckBoxes are only there to show certain aspects of a
patient.

I want to make it so that once all appropriate checkboxes have been
set, that their value's are protected after the worksheet is protected.
I have selected the locked property to be true, and after protecting
the sheet a user can still modify the CheckBox's value. How can I
prevent this? For more reasons than you want to know, setting the
enable property to false is not an option.

I had this idea, but it doesn't work.
There exists a named cell "OBRStatus" that contians the text of
"unlocked" or "locked" depending on the protection status of the sheet.
I then wrote this macro:
Private Sub CheckBox1_Click()
If Range("OBRStatus").Value = "LOCKED" Then
If CheckBox1.Value Then
CheckBox1.Value = False
Else
CheckBox1.Value = True
End If
End If
End Sub

You would think that would work right? Just toggling the value back to
it's original state if sheet is locked. Nope, here is what I found out
after I placed a breakpoint at the If Range...
After the CheckBox1.Value is changed, the functions goes to End Sub as
it should, but then it restarts as though the toggling of the value
envoked another CheckBox1_Click event.

If anyone has any ideas on how I can solve this problem please let me
know.
Thanks,
Clay Rose
(e-mail address removed)

don't use the enable propery, use the locked property does not go dark but
you can not manually change them
BEn

 

[ben]

umm maybe that only works on forms you could try this


public norepeat as integer
'put at top of module
Private Sub CheckBox1_Click()
if norepeat = 1 then exit sub
norepeat = 1
If Range("OBRStatus").Value = "LOCKED" Then
If CheckBox1.Value Then
CheckBox1.Value = False
Else
CheckBox1.Value = True
End If
End If
norepeat = 0
End Sub