How To Auto-Center Checkbox In Cell In Excel?

How To Auto-Center Checkbox In Cell In Excel?

To automatically center all checkboxes in cells in a current worksheet, please do as follows.

1. In the worksheet, you need to auto-center all checkboxes, press the Alt + F11 keys to open the Microsoft Visual Basic for Applications window.

2. In the Microsoft Visual Basic for Applications window, please click Insert > Module. Then copy and paste VBA code into the code window.

VBA code: Automatically center all checkboxes in cells

Sub CenterCheckbox ()

Dim xRg As Range

Dim chkBox As OLEObject

Dim chkFBox As CheckBox

On Error Resume Next

Application.ScreenUpdating = False

For Each chkBox In ActiveSheet.OLEObjects

If TypeName(chkBox.Object) = "CheckBox" Then

Set xRg = chkBox.TopLeftCell

chkBox.Width = xRg.Width * 2 / 3

chkBox.Height = xRg.Height

chkBox.Left = xRg.Left + (xRg.Width - chkBox.Width) / 2

chkBox.Top = xRg.Top + (xRg.Height - chkBox.Height) / 2

End If

Next

For Each chkFBox In ActiveSheet.CheckBoxes

Set xRg = chkFBox.TopLeftCell

chkFBox.Width = xRg.Width * 2 / 3

chkFBox.Height = xRg.Height

chkFBox.Left = xRg.Left + (xRg.Width - chkFBox.Width) / 2

chkFBox.Top = xRg.Top + (xRg.Height - chkFBox.Height) / 2

Next

Application.ScreenUpdating = True

End Sub

3. Press the F5 key. Then all checkboxes are moved into the center of the cells immediately as below screenshot shown.

Note: This VBA code can be applied to both CheckBox (ActiveX Control) and CheckBox (Form Control).

For more details kindly follow below URL:

https://www.extendoffice.com/documents/excel/4783-excel-checkbox-center-in-cell.html

Thank you very much for taking the time to reading this topic.