Kevin is a data engineer and advanced analytics developer. He has over 20 years experience in the field.
Adding a checkbox in Microsoft Excel using these simple steps.
From the Developer menu, select the Insert menu control and select a group panel. This is optional but it provides an efficient way to group the controls together. Once you select the Group control, draw it on the surface of the Excel worksheet using your mouse. When you released the mouse button, the control will appear where you drew it.
Add Grouping Control
With the Group control selected, select the Properties menu option in the ribbon next to the Insert menu option and select Edit Text to change the name of the Group control to “Use form controls”. Next, repeat the above operation by selecting the Checkbox control from the Form Controls pallet and draw it inside the Group control on the Excel Worksheet.
Also remove the gridlines to improve the visual appearance of the controls. To do so, select the Page Layout menu tab and uncheck the Gridlines View option as in the figure below.
Next, change the prompt text of the checkbox by right-clicking on the checkbox and selecting Properties in the ribbon. Then select the Alt Text tab in the properties page and replace the text with “Check to say hello”. Click ok to close the Properties page.
Interface with Checkbox
To enable or interface with the Checkbox control, first a cell on the worksheet by selecting the Control tab from the Properties page again. In the Cell link field, add the address to the cell on the Worksheet. This my example, I added the $H$3 cell. Now every time the checkbox is either checked or unchecked, the $H$3 cell will toggle from TRUE to FALSE. See the following video and corresponding figure below.
Notice when you select the checkbox control, the corresponding linked cell address appears in the Formula field. Next, we will add some VBA to interact with the control and the worksheet.
Select the Checkbox by right-clicking on the control and select the View Code option in the Controls section in the Developer tab. This will open the Visual Basic for Applications editor and automatically add the VB method, CheckBox2_Click(). The CheckBox2 is the name of the control unless you changed it. Click() is the name of the event that is fired when you interact with the control.
In between the Sub, End sub add the following code. This simple code checks the value of the linked cell and it is enabled (True) then assign the string “Hello World” to the cell at row 2 and column 10. Likewise, if the H3 cell is unchecked (False), then reset the cell at 2, 10 to an empty string again.
You can also interact between the checkbox and the worksheet by assign a macro. Add a macro by right-clicking on the control and select Assign macro. You can assign the macro (method) or assign a new one.
This concludes this tutorial. We have seen how easy it is to add a visual control like a Checkbox to a worksheet and to interact with the control by defining a macro or assigning another macro using a little bit of Visual Basic for Applications (VBA) code.
This article is accurate and true to the best of the author’s knowledge. Content is for informational or entertainment purposes only and does not substitute for personal counsel or professional advice in business, financial, legal, or technical matters.
© 2021 Kevin Languedoc
Kevin Languedoc (author) from Canada on January 02, 2021:
Umesh Chandra Bhatt from Kharghar, Navi Mumbai, India on January 01, 2021:
Very nice. Thanks.