Making Checkboxe protection off when adding via Macro

[colinjwatson]

Hi,

I have a Macro that adds checkboxes, I protect the sheet and when I run a different macro to uncheck all it doesn't work. Is there anyway that I can add the checkboxes but have the Protection Locked set to False? I've tested this by manually doing 3 of them and it works, but I have over 1000 boxes and no simple way to do them all at once.

Please Login or Register  to view this content.

Many thanks

Colin

[Neil_]

Hi
protect your spreadsheets UserIntefaceOnly and your code will run regardless of whether protected or not.
Type this directly into the Immediate Window and you're good to go.

Please Login or Register  to view this content.

if your code fails when the sheet is protected in this way, the problem isn't in the protection.

[colinjwatson]

Hey Neil,

I already have that on each of my sheets, what I have is certain fields that I want the user to interact with, and it works ok (including them ticking a check box) - however due to the sheer volume of check boxes I use a macro to add and remove them (as some weeks I will have 1000, other I may have 2000) - Each time my sheet protection works. What I can't get to work is my macro that removed all checks in the check boxes (so if someone saved the file they may have left an item way down the list checked. I just want a play it safe uncheck all button, but they just don't work unless I manually right click and untick the locked box on each checkbox

Thanks

Colin

[Neil_]

As long as you aren't using column 'A' for anything other than the checkboxes, change your code as below. Use the new sub to clear them.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[Neil_]

This will also allow you to filter on column A by the way.

Funny how things work out. This post helped me with this other one.
http://www.excelforum.com/showthread...97#post4503897

[colinjwatson]

Hey Neil, thanks for the help. Just tidying up some old posts