Select a single check box

Question

I have a piece of code that only works when there are many check boxes. However when I only have one checkbox the code bugs. Basically what I want the macro to do is to select the ckeckbox as an object and align it to a cell it should also work if more than one checkbox. Could you please help? Many thanks in advance. Please see code attached

Worksheets("Analysis Line Cupboards by Pick").CheckBoxes.Select
Selection.ShapeRange.Align msoAlignCenters, msoFalse
Selection.ShapeRange.IncrementLeft 45
Range("A10000").Select

Answer 1

You can align a checkbox (or any shape) on another shape, or on a cell with the left property.

Example, for an ActiveX checkbox in a Sheet to be aligned on cell B5:

Sheets("Sheet1").Shapes("Checkbox_1").Left = Sheets("Sheet1").Range("B5").Left

In your case (adapt it):

Sheets("Analysis Line Cupboards by Pick").Shapes("Checkbox name").Left = cell.left

Or, if you have multiple checkboxes, give them appropriate names and loop through them. For example, their names could be chbx_A, chbx_Hello, chbx_10 or something.

Sub AlignCHBX()
    dim shp as shape
    for each shp in Sheets("Analysis Line Cupboards by Pick").Shapes
        if shp.name like "chbx*" then shp.left = Sheets("Analysis Line Cupboards by Pick").Range("B1").Left
    next
End sub

This verifies that the shape's name starts with chbx to avoid moving other shapes. You can use this to differentiate certain groups of checkboxes, as well!

For a NON-ActiveX checkbox, use the following syntax to refer to it's left, for example:

Sheets("Sheet1").OLEObjects("chbx_A").Left

For it's value:

Sheets("Sheet1").OLEObjects("chbx_A").Object.Value

For .Top it works the same way. The .Left and .Top properties are numeric values measured in pixels. If you want to go to an absolute position, you can write Sheets("Sheet1").OLEObjects("chbx_A").Left = 150 for example. When you are making the shape's Left = to the cell's left, the code actually goes to see what Absolute value the left position of the cell is, and gives it to that shape. If I can elaborate, you could write:

 dim nbPosition as Double
 nbPosition = ActiveSheet.Range("B5").Left
 debug.print nbPosition 'It could say 40, for example, depending on column width
 ActiveSheets.Shapes("Shape1").Left = nbPosition + 10 'Will send it 10 pixels further than nbPosition