Friday, November 14, 2014

Adding CheckBoxes, Centering Issue

The Problem

A poster at ExcelGuru was having problems getting checkboxes s/he was adding using a macro to horizontally center in the cells. <link
The code included the following
Dim myCell as Range
Dim CBX as CheckBox        
For Each myCell in Selection
    With myCell
            Set CBX = .Parent.CheckBoxes.Add( _
                        Top:=.Top, _
                        Left:=.Left, _
                        Width:=1, _
                        Height:=1)
            ' ...
            CBX.Left = .Left + ((.Width - 17) / 2) '.Left + ((.Width - CBX.Width) / 2)
            ' ...
        End With
Next 

The Answer

The problem is this is not a case of what you see is the whole story. If you were to go to the added checkbox and select it while in Designer mode you would see the square image is not the whole of the object. With a little experimentation I found that the magic number to use instead of CBX.Width is 17. 

The Final Code

The following works for me.

Sub CellCheckbox()
    Dim myCell As Range
    Dim myRng As Range
    Dim CBX As CheckBox

    For Each myCell In Selection
        With myCell
            Set CBX = .Parent.CheckBoxes.Add( _
                        Top:=.Top, _
                        Left:=.Left, _
                        Width:=1, _
                        Height:=1)
            CBX.Name = .Address(0, 0)
            CBX.Caption = ""
            CBX.Left = .Left + ((.Width - 17) / 2)
            CBX.Top = .Top + ((.Height - CBX.Height) / 2)
            CBX.LinkedCell = .Offset(0, 1).Address(external:=True)
            CBX.Value = xlOff
        End With
    Next myCell
End Sub