Center aligning checkboxes when generating via VBA

[fullysic]

Hi guys,

I am generating several checkboxes onto a workbook I have via VBA, but the issue I'm having is that the checkboxes are not aligning to the centre of the cells.

I was hoping someone could point me in the right direction with this. I have included a simple version of the code I am using to generate the checkboxes, and any help with aligning them to the center automatically would be much appreciated!

Thanks

Sub CellCheckbox()
Dim myCell As Range
Dim myRng As Range
Dim CBX As CheckBox
With ActiveSheet
.CheckBoxes.Delete
Set myRng = .Range("A1:A10")
End With
For Each myCell In myRng.Cells
With myCell
Set CBX = .Parent.CheckBoxes.Add _
(Top:=.Top, _
Left:=.Left, _
Width:=.Width, _
Height:=.Height)
CBX.Name = "CBX_" & .Address(0, 0)
CBX.Caption = ""
CBX.Value = xlOff
CBX.LinkedCell = .Offset(0, 0).Address(external:=True)
.Offset(0, 1).NumberFormat = ";;;"
End With
Next myCell

End Sub

 

[fullysic]

Hmm.. thought I had figured it out by offsetting the Left value, but it's not a perfect solution... if someone can still help me with code to center it perfectly in the middle of the cell it would be much appreciated.

 

[FishingIsMoreFun]

I would somehow consider using a template.
Possibly open the template and then copy or something.
I can see that constant realignment might be an issue.

Will get back to you if I work something out, other than that you would have to carefully (and I mean carefully) add the coordinates manually... which would also be an arduous task due to the fact that each check box will need separate co-ords.

However it might work, but I would look into getting a template working to your advantage.That way you are guaranteed the same result every time.

cheers

 

[fullysic]

I would somehow consider using a template.
Possibly open the template and then copy or something.
I can see that constant realignment might be an issue.

Will get back to you if I work something out, other than that you would have to carefully (and I mean carefully) add the coordinates manually... which would also be an arduous task due to the fact that each check box will need separate co-ords.

However it might work, but I would look into getting a template working to your advantage.That way you are guaranteed the same result every time.

cheers

Thanks FishingIsMoreFun, I appreciate you taking the time to look into this.

One of the reasons I haven't gone with a template is that the number or checkboxes produced is dynamic (not shown in example code). I would like the checkboxes to show only for items that need have a decision made on them (user can tick or not tick a particular item).

 

[FishingIsMoreFun]

This might help you find another way, by recording a macro and using the Draw- align option, you can at least align them.
If you get 7 checkboxes (of same name) and run code it will align them, though it does not seem to align them to cells.
You would need to use I think the first and second checkbox to determine the default alignment...maybe

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 21/10/2011 by Dan
'

'
   
    ActiveSheet.Shapes.Range(Array("CheckBox1", "CheckBox2", "CheckBox3", _
        "CheckBox4" _
    , "CheckBox5", "CheckBox6", "CheckBox7")).Select
    Selection.ShapeRange.Align msoAlignCenters, False
    Selection.ShapeRange.Distribute msoDistributeVertically, False
End Sub

 

[FishingIsMoreFun]

Thanks FishingIsMoreFun, I appreciate you taking the time to look into this.

One of the reasons I haven't gone with a template is that the number or checkboxes produced is dynamic (not shown in example code). I would like the checkboxes to show only for items that need have a decision made on them (user can tick or not tick a particular item).

Maybe simply hide them, this is the basic hide procedure.
You might be better off just referencing them with a condition.

ActiveSheet.Shapes("CheckBox1").Visible = TRUE

ActiveSheet.Shapes("CheckBox1").Visible = FALSE

 

[fullysic]

Maybe simply hide them, this is the basic hide procedure.
You might be better off just referencing them with a condition.

ActiveSheet.Shapes("CheckBox1").Visible = TRUE

ActiveSheet.Shapes("CheckBox1").Visible = FALSE

Thanks mate, that is a really good idea. I think I might give that a go instead of trying to generate each one.

 

[FishingIsMoreFun]

you wouldn't call it as activeshape, but there are plenty of ways to reference a checkbox.

cheers