Check Boxes Not Hiding With Column

emailcolink

New Member
Joined
Mar 31, 2015
Messages
37
I have a workbook with some form control check boxes that refuse to hide when the column they are in is hidden. All of the check boxes are created by a macro, so they all have the same properties, size, and alignment in the cell. This is only happening for a few check boxes out of 100 that are on that workbook. Any thoughts?
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,971
CheckBoxes (and other shapes) are not in cells or columns, they are above it in a different layer.

The hide/unhide routine should explicitly hide/unhide any shapes that you want hidden at the same time as cells.

One trick is to put the shape's TopLeftCell address in its .AlternativeText property so VB can easily see what cells are associated with each Shape.
 

Josiah

Board Regular
Joined
Apr 10, 2008
Messages
178
I have a similar situation where multiple columns have cells that contain checkboxes (aligned WITHIN the cells) and they ALL remain visible when my "Hide Empty Columns" macro is activated.
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,971
The cells don't contain the checkboxes. The check boxes are in a layer above the cells. When the cell's width changes or they are hidden, that doesn't effect the checkbox.

You have to explicitly deal with the checkboxes separately from the cell hiding/unhiding.
 

emailcolink

New Member
Joined
Mar 31, 2015
Messages
37

ADVERTISEMENT

One trick is to put the shape's TopLeftCell address in its .AlternativeText property so VB can easily see what cells are associated with each Shape.

I have not heard of that one. I'll play around with it and see if I can solve it from there. Thanks!
 

Josiah

Board Regular
Joined
Apr 10, 2008
Messages
178
The cells don't contain the checkboxes. The check boxes are in a layer above the cells. When the cell's width changes or they are hidden, that doesn't effect the checkbox.

You have to explicitly deal with the checkboxes separately from the cell hiding/unhiding.

What I meant was that they're not outside the borders of the cell or midway between two cells so the sheet might think it's associated with a different one.
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,971
The .TopLeftCell and .BottomRightCell properties of a shape (including Forms menu control) is good at locating where a shape is at any one time.
But as the cells change, those properties change. If you put a string in a shape's .AlternativeText, that tells you where it should be, then its easy to adjust a shape's location to match the changing
 

Watch MrExcel Video

Forum statistics

Threads
1,127,861
Messages
5,627,307
Members
416,239
Latest member
Counselor85027

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top