How to quickly insert multiple checkboxes in Excel?

How can we quickly insert multiple check boxes in Excel? Please follow these tricky methods in Excel:

Insert multiple checkboxes with Fill Handle

Insert multiple checkboxes with VBA code

Kutools for Excel: add 120 new features in Excel. Save one hour every day.
Classic Menu for Office: brings back classic menus to Office 2010 and 2013 (includes Office 365).
Office Tab: brings tabbed interface to Office as the use of web browser Chrome, Firefox and Internet Explorer.

Hot
Amazing! Using Tabs in Excel like Firefox, Chrome, Internet Explore 10!

arrow blue right bubbleInsert multiple checkboxes with Fill Handle

In Excel, Fill Handle is a powerful function to do many things. It can fill the checkboxes as well. First, you must insert a checkbox. Do as follows:

1. Click Developer in the menu bar, then click Insert, and choose the checkbox image under Form Control. See screenshot:

doc-insert-multiple-checkboxes1

2. Click the checkbox image, and you will see plus sign pointer, click where you want to add checkbox.

doc-insert-multiple-checkboxes2

3. Select the text and remove it to a cell, and then delete the name of the checkbox.

4. Then select the cell where the checkbox stay in. And then drag the fill handle over the range of cells that you want to fill. See screenshot:

doc-insert-multiple-checkboxes3


arrow blue right bubbleInsert multiple checkboxes with VBA code

If you think the first method is somewhat troublesome, the following VBA code can help you easily and quickly insert multiple checkboxes.

1. Select the range that you want to add the checkboxes.

2. Click Developer > Visual Basic, a new Microsoft Visual Basic for applications window will be displayed, click Insert > Module, and input the following code into the Module:

Sub AddCheckBoxes()
On Error Resume Next
Dim c As Range, myRange As Range
Set myRange = Selection
For Each c In myRange.Cells
ActiveSheet.checkboxes.Add(c.Left, c.Top, c.Width, c.Height).Select
With Selection
.LinkedCell = c.Address
.Characters.Text = ""
.Name = c.Address
End With
c.Select
With Selection
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, _
Formula1:="=" & c.Address & "=TRUE"
.FormatConditions(1).Font.ColorIndex = 6 'change for other color when ticked
.FormatConditions(1).Interior.ColorIndex = 6 'change for other color when ticked
.Font.ColorIndex = 2 'cell background color = White
End With
Next
myRange.Select
End Sub

3. Then click doc-multiply-calculation-3 button to run the code.

4. And the multiple checkboxes have been inserted in the range neatly and beautifully. See screenshot:

doc-insert-multiple-checkboxes4

Related Artical:

How to quickly delete multiple checkboxes in Excel?


Kutools for Excel

More than 120 Advanced Functions for Excel 2013, 2010, 2007 and Office 365.

screen shot

btn read more     btn download     btn purchase

Comments  

+6#Jamie2013-10-30 08:06
Excellent macro that works beautifully to quickly and easily insert check boxes into a selected range; thank you.

Would you consider showing an example of a macro to insert in a selected range a customized drop down list with three options: "Yes", "No", and "Maybe"?
Reply | Reply with quote | Quote
+1#Prem Gurung2013-11-04 11:16
Great trick... I enjoyed it.
Reply | Reply with quote | Quote
+1#Daniel2013-11-06 09:56
Why is there only one boy in this class????
Reply | Reply with quote | Quote
+2#Jay Chivo2013-11-07 01:09
Quoting Daniel:
Why is there only one boy in this class????

:lol: That's just for demonstration.
Reply | Reply with quote | Quote
+1#Jack2013-11-19 11:35
excellent macro works add checkbox in the cell, thank u :-)
Reply | Reply with quote | Quote
0#Suleman2013-12-05 06:53
Excellent work. Thank you :roll:
Reply | Reply with quote | Quote
+1#O2013-12-05 23:42
AWESOME! Thank you so much!
Reply | Reply with quote | Quote
0## Sean2013-12-13 00:39
When I did this, everything worked great (Thank you!) except when the boxes are checked, it automatically has a yellow fill color in the cell. How do I make it to where the only thing that happens is the box is checked, no fill color?

Thanks!
Reply | Reply with quote | Quote
+1#Dennis2013-12-26 16:57
Quoting # Sean:
When I did this, everything worked great (Thank you!) except when the boxes are checked, it automatically has a yellow fill color in the cell. How do I make it to where the only thing that happens is the box is checked, no fill color?

Thanks!



Just change the line like it says in the comments:

.FormatConditions(1).Interior.ColorIndex = 6 'change for other color when ticked

Leave it out completely if you don't want a color change.

Cheers!
Reply | Reply with quote | Quote
+3#Lior2014-01-02 12:04
I did it but when I check one boc, all are being checked together. how can I separate them?
Reply | Reply with quote | Quote
+1#Violet2014-01-27 18:21
The code works great! I edit the color now I'd like to center the checkboxes in each cell, any idea what I would need to modify for that? Thanks!
Reply | Reply with quote | Quote
0#CM_Mario2014-01-29 12:46
It keeps giving me an error when I open it that it had to fix errors? Any assistance? If someone would like to help I will post the report that I'm getting from Microsoft office.
Reply | Reply with quote | Quote
0#Kman2014-02-08 23:18
The check boxes go a bit nuts when you copy them with their linked cells. I had to write a thing to reset the linking for when I want to copy those cells somewhere else.
Reply | Reply with quote | Quote
0#Priyanka2014-02-19 14:12
Can we then filter on those check boxes? Basically display only those who attended?
Reply | Reply with quote | Quote
0#Priyanka2014-02-19 14:12
Can we then filter those check boxes? Basically display only those who attended?
Reply | Reply with quote | Quote
0#Kman2014-02-20 05:13
Yeah totally. The checkboxes are actually independent of the cells. They basically float over the spreadsheet and are linked to a cell by address inserting True and False values when clicked (or N/A if you make it a three-state checkbox).

But when you filter in excel it hides specific rows. It will not hide the check boxes. I really don't suggest filtering with this macro. Strange things will happen and you might not realize when there are several check boxes sitting on top of each other or that two check boxes are linked to one cell and a cell is linked to no checkboxes.

What I usually do is I will create a list and instead of checkmarks I will type a 1 or a 0 into the adjacent cell and then total. You can even pivot table the thing if your information gets complicated. For instance say you had another column that had the gender of your attendees. You could figure out how many men and how many women attended.
Reply | Reply with quote | Quote
0#Ejaz-Ul-Haque2014-02-26 12:02
Thanks a lot!,
for check box.
Reply | Reply with quote | Quote
0#MikeC2014-02-27 16:46
Is there a way to have the checkbox text be the text of the selected cell?
Reply | Reply with quote | Quote
0#Danny2014-02-28 12:26
Yes. Yes, there is.
There is always a way.
Reply | Reply with quote | Quote
0#MikeC2014-03-10 14:52
Quoting Danny:
Yes. Yes, there is.
There is always a way.


OK. How would I accomplish this. I am not well versed in excel syntax. I have tried .text and .value (for c in the above code). With incorrect results. I have tried a myriad of things that would normally be the result but no luck.
What I have is a large bullet list of items that I would like to replace the bullet with a check box and have the value and text of that box be the original text of the bullet item
Reply | Reply with quote | Quote
0#Meg2014-03-04 01:34
Hey guys, this is so helpful - thanks! Quick question, I'm completely inexperienced with this type of stuff so sorry if it's a stupid one! I would like to just have tick boxes (without the true or false) and I would like the boxes to be yellow when not ticked but have no fill once ticked, to indicate students who have not handed in drafts and assessment, etc. Is this possible? Could someone please post the actual code for this, or walk me through it? Many thanks in advance!!!
Reply | Reply with quote | Quote
0#FAISAL2014-03-09 17:50
how to change the reference address for multiple checkboxes
Reply | Reply with quote | Quote
0#Reuben2014-03-07 00:00
I have a excel spreadsheet that has a question and then to the right are other columns "Controls Met, column f and G with check boxes that have been check, column F yes or column G no. I only want to use the filter to get each of the rolls that have a check mark, but the filter does not see s check mark
Reply | Reply with quote | Quote
0#FAISAL2014-03-09 17:52
how to change cell address
Reply | Reply with quote | Quote
0#Kman2014-03-10 04:06
Sub RelinkCheckBoxes()
'You select cells that include the checkboxes that are acting weird.
'Then you run the macro.
'It changes those checkboxes so that the linked cells correspond to the cells underneath.
'Also it makes the checkbox text turn into the cell text.

Dim ckbox As Shape
Dim rngLinkedCell As Range

If ActiveSheet.CheckBoxes.Count = 0 Then Exit Sub

'Test that selection is a range.
If TypeName(Selection) "Range" Then Exit Sub


For Each ckbox In ActiveSheet.Shapes
'Test that the shape is a control.
If ckbox.Type = msoFormControl Then
'test that the control is a checkbox
If ckbox.FormControlType = xlCheckBox Then
'find the cell under the checkbox that is within the selection
Set rngLinkedCell = Application.Intersect(ckbox.TopLeftCell, Selection)
'assign a new link address and rename the checkbox to match.
If Not rngLinkedCell Is Nothing Then
With ckbox
.OLEFormat.Object.LinkedCell = rngLinkedCell.Address
.Name = rngLinkedCell.Address
.TextFrame.Characters.Text = rngLinkedCell.Text
End With
End If
End If
End If
Next
Reply | Reply with quote | Quote
0#xyz2014-03-28 15:09
The code worked great. Thank you!
Reply | Reply with quote | Quote
0#Cole2014-03-28 15:59
Hey, I found this and it works great, but im using it for a daily checklist and i put rows, and when i check one box (i.e. in row 3) all check boxes in that row are checked.

Is there a way to make each check box individual/independent?
Reply | Reply with quote | Quote
0#Kman2014-04-02 04:10
You need to either delete those check boxes that are linked and re-create them with the macro or you can try the code I posted earlier that resets the link between cells and check boxes.
Reply | Reply with quote | Quote

Add comment


Security code
Refresh