format checkbox
 

I want to center checkboxes I have added in the middle of a cell. Can I do
that? Here is my code...

Option Explicit
Sub addCBX()
Dim myCBX As CheckBox
Dim myCell As Range
Dim RAN As Range

Set RAN = ActiveCell
With ActiveSheet
.CheckBoxes.Delete
For Each myCell In RAN

With myCell
Set myCBX = .Parent.CheckBoxes.Add _
(Top:=.Top, Width:=.Width, _
Left:=.Left, Height:=.Height)
With myCBX
.LinkedCell = myCell.Offset(0, 7).Address(external:=True)
.Caption = ""
.Name = "CBX_" & myCell.Address(0, 0)
'.OnAction = "'" & ThisWorkbook.Name & "'!dothework"
End With
.NumberFormat = ";;;"
End With

Next myCell
End With
End Sub

format checkbox
 

This seemed to work ok for me:

Set myCBX = .Parent.CheckBoxes.Add _
(Top:=.Top, _
Width:=24, _
Left:=.Left + ((.Width - 24) / 2), _
Height:=.Height)

You could fiddle with that width if it's off a bit.

John wrote:

I want to center checkboxes I have added in the middle of a cell. Can I do
that? Here is my code...

Option Explicit
Sub addCBX()
Dim myCBX As CheckBox
Dim myCell As Range
Dim RAN As Range

Set RAN = ActiveCell
With ActiveSheet
.CheckBoxes.Delete
For Each myCell In RAN

With myCell
Set myCBX = .Parent.CheckBoxes.Add _
(Top:=.Top, Width:=.Width, _
Left:=.Left, Height:=.Height)
With myCBX
.LinkedCell = myCell.Offset(0, 7).Address(external:=True)
.Caption = ""
.Name = "CBX_" & myCell.Address(0, 0)
'.OnAction = "'" & ThisWorkbook.Name & "'!dothework"
End With
.NumberFormat = ";;;"
End With

Next myCell
End With
End Sub

--

Dave Peterson

format checkbox
 

Thanks again Dave, your a champ!

"Dave Peterson" wrote:

This seemed to work ok for me:

Set myCBX = .Parent.CheckBoxes.Add _
(Top:=.Top, _
Width:=24, _
Left:=.Left + ((.Width - 24) / 2), _
Height:=.Height)

You could fiddle with that width if it's off a bit.

John wrote:

I want to center checkboxes I have added in the middle of a cell. Can I do
that? Here is my code...

Option Explicit
Sub addCBX()
Dim myCBX As CheckBox
Dim myCell As Range
Dim RAN As Range

Set RAN = ActiveCell
With ActiveSheet
.CheckBoxes.Delete
For Each myCell In RAN

With myCell
Set myCBX = .Parent.CheckBoxes.Add _
(Top:=.Top, Width:=.Width, _
Left:=.Left, Height:=.Height)
With myCBX
.LinkedCell = myCell.Offset(0, 7).Address(external:=True)
.Caption = ""
.Name = "CBX_" & myCell.Address(0, 0)
'.OnAction = "'" & ThisWorkbook.Name & "'!dothework"
End With
.NumberFormat = ";;;"
End With

Next myCell
End With
End Sub

--

Dave Peterson