Hide checkboxes when hiding rows

Question

I have used VBA code to hide some rows. These rows are hidden when I click a check box.

The problem I have now is - the check boxes associated with each row will not hide. This also interferes with my original VBA code to hide the rows and stops working. I would like to hide these check boxes with the rows.

Please can you advise?

Pillsbury

Answer 1

You need to set the checkboxes to "Move and Size With Cells." The last time I did this, with Excel 2003 it was easy: just right-click, choose "Properties" and choose that option. Now if you try that you'll see the option, but it's disabled:

So instead you need to access the more modern-looking format menu in Excel 2007 onwards. I did it by clicking the little "more" arrow on the Drawing Tools tab's Format group. For some reason it's enabled there. Once you set it your checkbox will hide with its row:

Malaria

Hi, the move and size with cells is greyed out, the middle option is move but don't size with cells, this one is selected. I right clicked on checkbox properties – Pillsbury

Form control checkbox's - The move with size and cells is greyed out. If I insert an active x checkbox I have the option move and size with cells. I would like to use the form control checkbox – Pillsbury

Hi Doug, I just saw these images you attached - when I refreshed, let me see how this works , thanks for your trouble – Pillsbury

This is so annoying I am using excel 2013 and there is no format tab on the ribbon – Pillsbury

@user2533460, the tab is context sensitive, so it will only appear when you have the check box selected – Epileptoid

I found the format tab now. Everything hides, except row 1 with check box visible. The vba code does not work now, example if I want to show the rows now, it wont show. – Pillsbury

That sounds like another question. If you feel I've answered the question you asked. Please accept it by clicking the checkmark next to it. As a side note, I think there are probably better ways to do what you are trying, for example automating AutoFilter or the Hide/Unhide Row commands. – Malaria

Thanks Doug, I'm not sure how I will hide that one visible box, but this should be fine for now. – Pillsbury

@DougGlancy you are my hero! Really.. nearly went nuts because a policy has "broken" the ActiveX controls which were correctly hidden when the column was hidden. Why the hell is MS differencing here between available "Forms properties" and Shape properties (regarding positioning)?! -.- – Adjust

@FelixBayer, glad I could help. This might also be useful: dailydoseofexcel.com/archives/2014/12/11/… – Malaria

@DougGlancy thanks for the link. Will definitely try if thats the real root for my mysterious ActiveX control problem because it really started after the last updates. – Adjust

This worked for me with a form control text box but ONLY after I first changed the option to "Don't move or size with cells" and then toggled back to "Move and size with cells". – Rumple