Protect Checkboxes

[Guest]

Hello,

I have a worksheet that contains a ton of checkboxes created using the Forms
toolbar not the Control Toolbox. I want to protect that worksheet so that
people can't do anything to the worksheet itself, including checking or
unchecking those checkboxes. There is a maco that goes through and
unprotects the worksheet, checks the appropriate boxes and then protects the
workbook again. The problem I have is that when I protect the worksheet I
can stop them from doing anything except checking the boxes. How do I stop
them from being able to check the boxes?

Thanks,
jordan

 

[Dave Peterson]

You have to unlock those linked cells.

Maybe you could put them in a column and hide the column--or even put them in a
different worksheet???

 

[Guest]

activesheet.checkboxes(1).Enabled = False

 

[Guest]

Unfortunatly they want to see all the available checkboxes, they just don't
want anyone to have the ability to click on them.

 

[Guest]

apparently Dave misread the requirement. (although locking (rather than
unlocking) the underlying linked cells is not totally effective from a visual
standpoint)

See my answer in this thread.

 

[Guest]

Hey Tom,

I gave that a shot too and I keep getting "object doesn't support that
method," which I believe is because I'm using Forms instead of the Control
Toolbox because the checkboxes on the control toolbox are becoming a
nightmare for formatting. The descriptions keep autosizing and other such
stuff. So I tried the following code and it will let me select the toolboxes
but it won't allow me to enable or disable them, infact it won't allow me to
set their values to true or false either.

Dim CheckBoxC As Object
For Each CheckBoxC In ActiveSheet.Shapes
CheckBoxC.Select = True
CheckBoxC.Enabled = False
Next

 

[Guest]

the command I gave you works fine. Your interpretation might be flawed.
This should work as well.

Dim CheckBoxC As Checkbox
For Each CheckBoxC In ActiveSheet.Checkboxes
CheckBoxC.Select = True
CheckBoxC.Enabled = False
Next

 

[Guest]

Awesome, the Dim Checkbox as Checkbox worked.

thanks,
jordan

 

[Dave Peterson]

Well, it happened again!

apparently Dave misread the requirement. (although locking (rather than
unlocking) the underlying linked cells is not totally effective from a visual
standpoint)

See my answer in this thread.

 

[Chip Pearson]

Jordan,

Both the Excel library and the MSForms library have an object named
"CheckBox". To prevent the possibility of ambiguity, you should include the
library name in the Dim statement.

Dim CheckBoxC As Excel.CheckBox
if you are using the check box from the Forms toolbar or

Dim CheckBoxC As MSForms.CheckBox
if you are using the check box from the Control toolbar.



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
www.cpearson.com
(email address is on the web site)