Protect Checkboxes
 

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

Protect Checkboxes
 

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???

Jordan wrote:

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

Protect Checkboxes
 

activesheet.checkboxes(1).Enabled = False

--
Regards,
Tom Ogilvy


"Jordan" wrote:

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

Protect Checkboxes
 

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

"Dave Peterson" wrote:

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???

Jordan wrote:

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

Protect Checkboxes
 

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.

--
Regards,
Tom Ogilvy


"Jordan" wrote:

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

"Dave Peterson" wrote:

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???

Jordan wrote:

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

Protect Checkboxes
 

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

"Tom Ogilvy" wrote:

activesheet.checkboxes(1).Enabled = False

--
Regards,
Tom Ogilvy


"Jordan" wrote:

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

Protect Checkboxes
 

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


--
Regards,
Tom Ogilvy


"Jordan" wrote:

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

"Tom Ogilvy" wrote:

activesheet.checkboxes(1).Enabled = False

--
Regards,
Tom Ogilvy


"Jordan" wrote:

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

Protect Checkboxes
 

Awesome, the Dim Checkbox as Checkbox worked.

thanks,
jordan

"Tom Ogilvy" wrote:

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


--
Regards,
Tom Ogilvy


"Jordan" wrote:

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

"Tom Ogilvy" wrote:

activesheet.checkboxes(1).Enabled = False

--
Regards,
Tom Ogilvy


"Jordan" wrote:

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

Protect Checkboxes
 

Well, it happened again!

Tom Ogilvy wrote:

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.

--
Regards,
Tom Ogilvy

"Jordan" wrote:

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

"Dave Peterson" wrote:

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???

Jordan wrote:

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


--

Dave Peterson

Protect Checkboxes
 

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)


"Jordan" wrote in message
...
Awesome, the Dim Checkbox as Checkbox worked.

thanks,
jordan

"Tom Ogilvy" wrote:

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


--
Regards,
Tom Ogilvy


"Jordan" wrote:

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

"Tom Ogilvy" wrote:

activesheet.checkboxes(1).Enabled = False

--
Regards,
Tom Ogilvy


"Jordan" wrote:

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