Hi,

Does anyone know a good way to lock all checkboxes (or other user form
objects) on a worksheet using an excel macro? I have a macro that will cycle
through all the worksheets in a workbook, and it needs to set the lock
property on all check boxes it finds along the way.


Thanks!

What kind of checkboxes

Control Toolbox Toolbar/ActiveX Checkbox:

for each sh in thisworkbook.Worksheets
for each obj in sh.OleObjects
if typeof Obj.Object is MSForms.Checkbox then
obj.Locked = True
end if
Next
Next

CheckBox from the Forms Toolbar:

for each sh in thisworkbook.Worksheets
for each cbox in sh.CheckBoxes
cbox.Locked = True
next
Next

--
Regards,
Tom Ogilvy


"Erik Andreassen" wrote in
message ...
Hi,

Does anyone know a good way to lock all checkboxes (or other user form
objects) on a worksheet using an excel macro? I have a macro that will
cycle
through all the worksheets in a workbook, and it needs to set the lock
property on all check boxes it finds along the way.


Thanks!

Hey, thanks for the help.

Working with the code, the first set works perfectly for ActiveX checkboxes.
The second does indeed set the "locked" property for form checkboxes, but
they can still be changed when the worksheet is protected. Do you know any
way around this? It might be that I just simply have to change all the check
boxes to ActiveX controls.

Thanks.

"Tom Ogilvy" wrote:

What kind of checkboxes

Control Toolbox Toolbar/ActiveX Checkbox:

for each sh in thisworkbook.Worksheets
for each obj in sh.OleObjects
if typeof Obj.Object is MSForms.Checkbox then
obj.Locked = True
end if
Next
Next

CheckBox from the Forms Toolbar:

for each sh in thisworkbook.Worksheets
for each cbox in sh.CheckBoxes
cbox.Locked = True
next
Next

--
Regards,
Tom Ogilvy


"Erik Andreassen" wrote in
message ...
Hi,

Does anyone know a good way to lock all checkboxes (or other user form
objects) on a worksheet using an excel macro? I have a macro that will
cycle
through all the worksheets in a workbook, and it needs to set the lock
property on all check boxes it finds along the way.


Thanks!

for each sh in thisworkbook.Worksheets
for each cbox in sh.CheckBoxes
cbox.Enabled = False
next
Next


This locks the checkbox whether the sheet is protected or not.

--
Regards,
Tom Ogilvy


"Erik Andreassen" wrote in
message ...
Hey, thanks for the help.

Working with the code, the first set works perfectly for ActiveX
checkboxes.
The second does indeed set the "locked" property for form checkboxes, but
they can still be changed when the worksheet is protected. Do you know
any
way around this? It might be that I just simply have to change all the
check
boxes to ActiveX controls.

Thanks.

"Tom Ogilvy" wrote:

What kind of checkboxes

Control Toolbox Toolbar/ActiveX Checkbox:

for each sh in thisworkbook.Worksheets
for each obj in sh.OleObjects
if typeof Obj.Object is MSForms.Checkbox then
obj.Locked = True
end if
Next
Next

CheckBox from the Forms Toolbar:

for each sh in thisworkbook.Worksheets
for each cbox in sh.CheckBoxes
cbox.Locked = True
next
Next

--
Regards,
Tom Ogilvy


"Erik Andreassen" wrote in
message ...
Hi,

Does anyone know a good way to lock all checkboxes (or other user form
objects) on a worksheet using an excel macro? I have a macro that
will
cycle
through all the worksheets in a workbook, and it needs to set the lock
property on all check boxes it finds along the way.


Thanks!