When putting checkboxes into Excel cells, you might observe that it is challenging to organize every checkbox in a manner. Actually, you may arrange them nicely by moving all of the checkboxes to the cell center. In this article, we will show you how to Align Checkboxes in Excel.
What is Checkbox in Excel?
A checkbox is an interactive tool in Excel that lets you select or deselect an option. You must have seen it on one of the numerous online web forms. In Excel, checkboxes can be used to make dynamic charts, dashboards, and interactive checklists.
How to Align Checkboxes in Excel: 2 Handy Approaches
First, we will add checkboxes in Excel. Then, we will align them using the Page Layout tab and VBA Code in the next two methods. Let’s suppose we have a sample data set to align checkboxes in Excel.
1. Utilizing Page Layout Tab to Align Checkboxes in Excel
Using the Developer tab, we will first create checkboxes in an Excel worksheet. Then, from the Page Layout tab, we will choose the Selection Pane command to align checkboxes. To complete the work, adhere to the procedures listed below.
Step 1:
- First, select the Developer tab.
- Then click on the Insert command.
- Now, to create a checkbox, choose the Checkbox from the Form Controls pane.
- In the cell where you want the check box to appear in your Excel spreadsheet (mine is in column B).
- Afterward, click the cursor (the cursor will transform into a cross). You’ll be able to use it to design a box for yourself that will specify the dimensions of your new checkbox.
- Therefore, your new checkbox includes text that is shown below.
Step 2:
- Firstly, open the Page Layout tab.
- Now, go to the Selection Pane command.
Step 3:
- Then, select all the Checkboxes.
Step 4:
- Again, choose the Align Center option from the Align command.
Step 5:
- The final results with center-aligned checkboxes are as follows.
Read More: How to Link Multiple Checkboxes in Excel
2. Applying VBA Code to Align Checkboxes in Excel
It’s quite easy to construct a macro function in Microsoft Excel for aligning checkboxes. Therefore, now that we have a column of checkboxes, they can all be in a wavy line like mine in the image below. If so, we’ll have to make them straight. In this following technique, we will generate a VBA Code in Excel to Align Checkboxes.
Step 1:
- First, navigate to the Developer tab.
- Then, go to the Visual Basic option.
Step 2:
- Now, the Visual Basic Application window will open.
- Besides, select the Insert option.
- Then, click on the Module option to write your VBA Code.
Step 3:
- Finally, paste the following VBA code.
Sub Alignment_Checkbox()
Dim zRg As Range
Dim checkBox1 As Object
Dim checkBox2 As CheckBox
On Error Resume Next
'Screen will not be Updated
'For running the code fast
Application.ScreenUpdating = False
'Aligned Checkboxes using property change
For Each CheckBox In ActiveSheet.Objects
If TypeName(checkBox1.Object) = "CheckBox" Then
Set zRg = checkBox1.TopLeftCell
checkBox1.Width = zRg.Width * 2 / 3
checkBox1.Left = zRg.Left + (zRg.Width - checkBox1.Width) / 2
End If
Next
For Each checkBox2 In ActiveSheet.CheckBoxes
'Aligned Checkboxes using property change
Set zRg = checkBox2.TopLeftCell
checkBox2.Width = zRg.Width * 2 / 3
checkBox2.Left = zRg.Left + (zRg.Width - checkBox2.Width) / 2
Next
Application.ScreenUpdating = True
End Sub
- First, we call our Sub Procedure Alignment_Checkbox_Folder.
- Then, we refer to our current Worksheet as Active Worksheet.
- Afterward, for applying the condition for the checkboxes, we use the If conditional statement by If TypeName(checkBox1.Object) = “CheckBox”.
- Here, we specify the Range for the Checkboxes by Set zRg = checkBox1.TopLeftCell.
- To specify the Width of the checkboxes, we use checkBox1.Width = zRg.Width * 2 / 3 statement.
- Finally, to define the alignment of the checkboxes with the cells, we apply the checkBox1.Left = zRg.Left + (zRg.Width – checkBox1.Width) / 2 statement.
Step 4:
- Â When you use the VBA Code, you will see the following outcomes of center-aligned checkboxes.
Download Practice workbook
You may download the following Excel workbook for better understanding and practice yourself.
Conclusion
In this article, I’ve covered 2 handy methods to Align Checkboxes in Excel. I sincerely hope you enjoyed and learned a lot from this article. If you have any questions, comments, or recommendations, kindly leave them in the comment section below.
Related Articles
- How to Add Checkbox in Excel without Using Developer Tab
- How to Resize Checkbox in Excel
- How to Count Checkboxes in Excel
- How to Group Checkboxes in Excel
- How to Filter Checkboxes in Excel
- How to Apply Conditional Formatting Using Checkbox in Excel
- How to Remove Checkboxes from Excel
<< Go Back to Excel CheckBox | Form Control in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
This method seems to work well for a single column or row of checkboxes. I have a sheet with multiple columns and rows of checkboxes, but your method is lining them up in a single column in the middle of the page.
Any suggestions?
Hello TIM
Thanks for reaching out and sharing your query. You want an Excel VBA code that works perfectly for multiple columns and rows of checkboxes. I am presenting an enhanced VBA sub-procedure that may help you.
OUTPUT OVERVIEW:
Enhanced Excel VBA Code:
Hopefully, the sub-procedure will reduce your hassle. Good luck.
Regards
Lutfor Rahman Shimanto
Excel & VBA Developer
ExcelDemy