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

Insert multiple checkboxes with Kutools for Excel

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 check box and move 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. 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 InsertCheckBoxes()
'Update 20140506
Dim Rng As Range
Dim WorkRng As Range
Dim Ws As Worksheet
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
Set Ws = Application.ActiveSheet
Application.ScreenUpdating = False
For Each Rng In WorkRng
With Ws.CheckBoxes.Add(Rng.Left, Rng.Top, Rng.Width, Rng.Height)
.Characters.Text = Rng.Value
End With
Next
WorkRng.ClearContents
WorkRng.Select
Application.ScreenUpdating = True
End Sub

2. Then click doc-multiply-calculation-3 button to run the code. And a prompt box will pop out to remind you to select a range of cells that you want to insert the checkboxes. See screenshot:

doc-insert-multiple-checkboxes4

3. And then click OK, the checkboxes have been inserted in the range neatly and beautifully. See screenshot:

doc-insert-multiple-checkboxes5


arrow blue right bubbleInsert multiple checkboxes with Kutools for Excel

Here, I can introduce you an easy and handy way to insert multiple checkboxes-Kutools for Excel, with its Batch Insert Check Box feature, you can insert the checkboxes into blank cells or cells with data.

Kutools for Excelincludes more than 120 handy Excel tools. Free to try with no limitation in 30 days.Get it Now.

After installing Kutools for Excel, please do with following steps:

1. Select a range of cells that you want to insert the checkboxes.

2. Click Kutools > Insert Tools > Batch Insert Check Box, see screenshot:

doc-insert-multiple-checkboxes6

3. Then your selected cells have been filled with the checkboxes as following screenshots shown:

Insert checkboxes into blank cellsInsert checkboxes into data cells
doc-insert-multiple-checkboxes7doc-insert-multiple-checkboxes8

To know more about this feature, please go to Batch Insert Check Box.


Related Article:

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  

+9#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
0#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
+1#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
+1#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
0#Ali Mac2014-04-15 01:23
hi,

would you be able to help with one small tweak to your code, which is working just great for me otherwise?

i would like the cell to be exactly centered within the cell housing it.

thanks and regards.
Reply | Reply with quote | Quote
0#Ali Mac2014-04-15 23:44
hi,

this macro works beautifully for me. i needed a little favour - i would like to ensure that the checkboxes are placed bang in the centre of the cell. can someone please help?

best regards.
Reply | Reply with quote | Quote
0#Jennebeth Monserat2014-04-17 11:57
it really helped a lot. thank to you!
Reply | Reply with quote | Quote
0#Ed2014-04-21 16:12
Quoting MikeC:
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 Quoting MikeC:
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

replace the bullet with a check box and have the value and text of that box be the original text of the bullet item


Original code, plus it takes the list that is in the cells and uses that as the text for the check box
Sub AddCheckBoxes()
On Error Resume Next
Dim c As Range, myRange As Range, Text As String
Set myRange = Selection
For Each c In myRange.Cells
BoxLabel = c.Value '
Reply | Reply with quote | Quote
0#Brian2014-04-30 18:44
I have an xml mapped excel spreadsheet where a user copies and pastes a number of rows then we can export to an xml file. I would like to add a column with check boxes and have the user check appropriately then have that field export as true/false to xml. Any idea how this could be accomplished?
Reply | Reply with quote | Quote
0#Kman2014-05-04 03:46
If I understand you correctly you have an xml mapped excel file that is intended for users to copy and paste into. Then the user users the XML Export tool to create a second file that is an XML file that is then used in another process.

Well if you have the excel file with cells mapped you could have the check boxes setup in the file ahead of time. The checkboxes can be placed on cells that are mapped. The checkboxes insert True/False into the cells they are linked to or turn empty or to a check mark depending on what is entered into the cell. If a user pastes values into those cells the checkbox won't be affected unless that pasted value is "True" or "1". You can have the user either paste values or use the checkboxes to enter TRUE/FALSE. Once the user has gotten the excel file the way they want they can then export to XML.
Reply | Reply with quote | Quote
0#Jessica2014-06-27 17:08
:-) Hello,
Thanks for the tips! I was wondering, is there another simple way to insert checkboxes into the actual cell..vs floating?
Also, can you insert...
-as a dropdown field - categorizable (is that even a word? ;/)
-in the cell - so you can format alignment

thanks!
Reply | Reply with quote | Quote
+1#Kman2014-07-01 02:42
There is no built-in way that Microsoft makes that lets you turn Excel into a check list. There are tons of different list applications.

I do know one way that you can make it the checkmarks work in the way that you are probably expecting but it is not simple or straightforward and would not be practical unless you did it once. http://www.excelhero.com/blog/2011/03/excel-dynamic-checkmark.html

It uses conditional formatting, dynamically named ranges and an event listener script.

You could also just use a font and type it in. http://www.addintools.com/documents/excel/where-is-check-mark-symbol.html

And about text alignment within a cell through a dropdown,... I'm not sure what you want to do. But if you want the text alignment to change with the value in a cell you could try a custom number format where you use asterisk followed by a space to repeat a space. Format the cells as center aligned. That would force the values entered into the cell to the left or the right depending on your custom number format: http://office.microsoft.com/en-us/excel-help/create-or-delete-a-custom-number-format-HP001216503.aspx
Reply | Reply with quote | Quote

Add comment


Security code
Refresh