Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to Auto-Centre a Checkbox in a Cell in Excel?
If you've ever tried to insert multiple checkboxes in Excel, you may have encountered difficulty arranging them in a logical order. It will be an irritating process to arrange the checkboxes in Excel, as they are placed very randomly. But using the VBA code, we can auto-centre the checkboxes easily. This tutorial will help you understand how you can auto-centre checkboxes in cells in Excel.
Auto-Centre Checkbox in a Cell in Excel
Here, we will first insert a VBA module and then run it to complete our task. Let us see a simple process to understand how we can auto-centre checkboxes in cells in Excel using the VBA application. Our process will include both active X and form control checkboxes. Both active X and form controllers are present under the developer option.
Step 1
Consider an Excel sheet with multiple checkboxes for active X and form control, as shown in the image below.
Now right-click on the sheet name and select view code to open the vba application, then click on Insert, Select Module, and type the programme into the textbox as shown in the below image.
Program
Sub CenterCheckbox()
'Updated By nirmal
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
Step 2
Now save the sheet as a macro-enabled template and click on F5 to run the code, and all the checkboxes in the sheet will be arranged in the centre as shown in the below image.
Conclusion
In this tutorial, we used a simple example to demonstrate how we can auto-centre a checkbox in a cell in Excel.
2K+ Views
