Check Box Control

[Guest]

I've inserted a checkbox from the Control Toolbar into a worksheet, but I
can't change it's size or justification. I'd like it to be centered,
horizontally and vertically, in whatever cell I put it in, no matter the size
of that particular cell. I'd also like it to be larger.
Any ideas?

Thanks

 

[merjet]

Sizing it or moving it must be done in in Design Mode. On the Control
Toolbox toolbar use the first icon.

Hth,
Merjet

 

[Dave Peterson]

Maybe you can modify this to what you want:

Option Explicit
Sub testme()

Dim myCell As Range
Dim myRng As Range
Dim wks As Worksheet
Dim myCBX As OLEObject

Set wks = Worksheets("sheet1")

With wks
Set myRng = .Range("a1:a10")
End With

For Each myCell In myRng.Cells
With myCell
Set myCBX = .Parent.OLEObjects.Add _
(ClassType:="Forms.CheckBox.1", _
Link:=False, _
DisplayAsIcon:=False, _
Left:=.Left + (.Width / 2), _
Width:=.Width / 2, _
Height:=.Height)

With myCBX
.Placement = xlMoveAndSize
.LinkedCell = myCell.Address(external:=True)
.Object.Caption = ""
.Name = "CBX_" & myCell.Address(0, 0)
End With

.NumberFormat = ";;;"

End With
Next myCell

End Sub