Form Checkboxes are not maintaining cell link

[CSK]

I have a bunch of Form Checkboxes on a worksheet. Each one is linked to the
cell that they are sitting on. For example, the checkbox in cell B40 is cell
linked to B40. The problem is, if I hide rows that have these checkboxes,
the remaining checkboxes sometimes have an incorrect cell link.
Example: Here are the rows and corresponding checkboxes.
B39
B40
B41
B42
B43
B44
I hide row 39 and 40. Now the checkbox in B41 has cell link to B39. I need
the checkboxes to stay with their corresponding rows.

 

[Dave Peterson]

You sure that you're not just seeing the forms checkbox that was on that row
that got hidden.

Those form checkboxes won't resize when you hide the row.

If you're going to hide the rows, you may want to dump the forms toolbar
checkboxes and replace them with checkboxes from the control toolbox toolbar.

You can rightclick on those checkboxes (control toolbox toolbar types)
and choose format control|Properties tab|move and size with cells.

These'll be hidden when you hide the row.

Be careful. If you delete the row, you'll be left with an difficult to find
object.

And you assign the linked cell by rightclicking on the checkbox and choosing
properties.
Then type in the address in the Linkedcell property.
(different from the checkboxes from the Forms toolbar)

 

[CSK]

I just went and unhide the rows that had the checkboxes, and now the rows do
line up with their corresponding checkboxes.

Funny, I had the option checked to move and size with cell for the Form
Checkbox. Would have thought that would work.

So sounds like I need to delete all these form checkboxes and put them back
from the control toolbox.

I will give it a try and let you know how it turns out. Thanks for such a
speedy response.

 

[Dave Peterson]

If you had "move and size" checked, then you're not using checkboxes from the
Forms toolbar.

I'd double check the type of checkboxes before you do anything.

 

[CSK]

I double checked which checkbox I am using and it is coming from the Forms
Toolbar. When I selct the checkbox, right click, Format Controls,
Properties, option for "Move but don't size with cells is selected. I added
the toolbar for checkboxes from the control toolbox toolbar, but the checkbox
is grayed out so I do not know how to add this types of checkboxes to my
worksheet.

Not sure if it makes a difference but I am using Excel 2003.

 

[Dave Peterson]

I use xl2003, also.

Is the worksheet protected?

And when you're assigning the .linkedcell property and changing the "move and
size" stuff, you'll want to click on the design mode icon on that same control
toolbox toolbar.

Then do the work and get out of design mode.

I double checked which checkbox I am using and it is coming from the Forms
Toolbar. When I selct the checkbox, right click, Format Controls,
Properties, option for "Move but don't size with cells is selected. I added
the toolbar for checkboxes from the control toolbox toolbar, but the checkbox
is grayed out so I do not know how to add this types of checkboxes to my
worksheet.

Not sure if it makes a difference but I am using Excel 2003.

 

[Dave Peterson]

Have you grouped multiple sheets?

I use xl2003, also.

Is the worksheet protected?

And when you're assigning the .linkedcell property and changing the "move and
size" stuff, you'll want to click on the design mode icon on that same control
toolbox toolbar.

Then do the work and get out of design mode.

 

[CSK]

I tried adding the checkboxes from the control toolbar. I can add them fine,
can link them to a cell. I check the option to size and move with cell.
What I am now having trouble is getting the checkbox to be attached to the
cell physically so that when I do hide the row the corresponding checkbox
hides too.

Also not sure what the macro behind the checkbox needs to look like. When I
was using the Forms Checkbox to make the value TRUE or FALSE, the TRUE or
FALSE value was then referenced by another cell to decide if the row should
be included in the calculation. So it seems that I need to attach a macro to
the control toolbar checkbox that sets the value to TRUE if checked or FALSE
if unchecked. Not sure what that macro should look like.

Have you grouped multiple sheets?

 

[Dave Peterson]

You don't need any macro. But you do need to assign that linkedcell--and you
need to be out of design mode.

And make sure you put the checkbox completely within the cell--if you overlap
cells, you can have trouble. You may want to turn on the grid lines and set the
zoom factor to 100% to help with the positioning.

I tried adding the checkboxes from the control toolbar. I can add them fine,
can link them to a cell. I check the option to size and move with cell.
What I am now having trouble is getting the checkbox to be attached to the
cell physically so that when I do hide the row the corresponding checkbox
hides too.

Also not sure what the macro behind the checkbox needs to look like. When I
was using the Forms Checkbox to make the value TRUE or FALSE, the TRUE or
FALSE value was then referenced by another cell to decide if the row should
be included in the calculation. So it seems that I need to attach a macro to
the control toolbar checkbox that sets the value to TRUE if checked or FALSE
if unchecked. Not sure what that macro should look like.