Check Boxes Not Hiding With Column

[emailcolink]

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?

 

[mikerickson]

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]

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]

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]

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]

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]

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