Checkbox - Move and Size with Cells

[PATSYS]

When I create a checkbox, then click on FORMAT CONTROL -> PROPERTIES, the option "Move and Size with Cells" is not highlighted (i.e. not available)?

Is there a way to have this enabled?

Thanks[/img]

 

[dakota727]

Forms Check box vs controls Toolbox Check box

It sounds like you created your check box by using the forms checkbox. If you go to view->toolbars->contol toolbox and create the control from there the move and size option should be available.

 

[PATSYS]

Re: Forms Check box vs controls Toolbox Check box

It sounds like you created your check box by using the forms checkbox. If you go to view->toolbars->contol toolbox and create the control from there the move and size option should be available.

Hi,

Thanks for the info, that is something new to me.

However I have one question, this checkbox does not look to have exactly the same functionality as the one from the FORMS toolbar, there seems to be no way to link it to a cell i.e. in such a way that if the box is checked, a certain cell returns a value TRUE, etc.

Am I right with this observation?

Thanks again

 

[GlennUK]

You can link it to a cell. View Properties, and put a cell ref in the LinkedCell property.

 

[TaChiang]

When I create a checkbox, then click on FORMAT CONTROL -> PROPERTIES, the option "Move and Size with Cells" is not highlighted (i.e. not available)?

Is there a way to have this enabled?

Thanks[/img]

There is a work around.
- Select 2 or more checkboxes.
By using the functionality 'select objects' (arrow) of the 'drawing' toolbar.
- Then select: 'draw' / 'group' from the 'drawing' toolbar.
After this you can ungroup again, if you wish. Now the greyed out "Move and size with Cells" is selected.
Strange but it works.
Good luck.
Ta Chiang

 

[IEP81]

There is a work around.
- Select 2 or more checkboxes.
By using the functionality 'select objects' (arrow) of the 'drawing' toolbar.
- Then select: 'draw' / 'group' from the 'drawing' toolbar.
After this you can ungroup again, if you wish. Now the greyed out "Move and size with Cells" is selected.
Strange but it works.
Good luck.
Ta Chiang

Hey TiChiang pretty trick that one! I had the same problem that PATSYS mentioned ("Move and Size with Cells" is not highlighted) and find your solution, so just wanted to say thanks, it worked.

 

[mwf05]

@TaChiang, Does this only work in earlier versions of Excel (2003, 2007)? I tried it in 2010 and 2013 but could not get it to work. Looks like a great trick that would save me a lot of time and effort if I could get it to work. Still don't understand why the Forms Checkbox is not able to be sized with the cell as it changes.

 

[s hal]

Considering TaChiang last logged on in 2008, I doubt that they are going to see and reply to this... That said, I have 2010, and this does not appear to be an option. I tried to force excel to create the checkbox and have that property, but it is disabled (even though it gives no error or warnings when I tell it to do it)

 

[HIGH_I]

I searched quite a few places to find a VBA solution to this problem that didn't involve accessing the .Hidden property in worksheet.Change level code. This was what got me to the actual solution for Excel 2010. Please note this is only true when trying to programmatically assign the .Placement setting a value of xlMoveAndSize (which is grayed out in the standard "Format Control..." dialog box, but not grayed out if you access the more advanced Properties window.)

Essentially, you can't get the .Placement setting of a control (like a form-level check box) to go to xlMoveAndSize using VBA, if you try to access the control individually.

However, if you create an initial Check Box (or likely any other form control) and name it "HiddenCheckBox", the following code will work:

    ActiveSheet.CheckBoxes.Add(ActiveCell.Left, ActiveCell.Top, 54, 18).Select
    With Selection
        .Name = "YourUniqueName"
        .Caption = "Your Caption"
        .Value = False
        .LinkedCell = "A1"
        .Display3DShading = False
        .Placement = XlPlacement.xlMoveAndSize
        .PrintObject = True
    End With
    
    ActiveSheet.Shapes.Range(Array("YourUniqueName", "HiddenCheckBox")).Select
    Selection.Placement = xlMoveAndSize

The .Placement portion in the With section does NOT work (and could be removed), only the final two lines that select the new object along with another object allowed the setting to take hold.