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 and delete multiple checkboxes with one click by using Kutools for Excel


Insert multiple checkboxes in selections with one click:

Kutools for Excel's Batch Insert Check Boxes utility can help you to quickly insert multiple checkboxes at once. Click to free download this feature.

doc-insert-multiple-checkboxes1

Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download and free trial Now!


arrow blue right bubble Insert 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 bubble Insert 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 bubble Insert 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 Excel : with more than 120 handy Excel add-ins, free to try with no limitation in 60 days.

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 > Batch Insert Check Boxes, see screenshot:

doc-insert-multiple-checkboxes06

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

Insert checkboxes into blank cells Insert checkboxes into data cells
doc-insert-multiple-checkboxes7 doc-insert-multiple-checkboxes8

Tips. If you want to quickly delete multiple checkboxes, please try to use the Kutools for Excel’s Delete Check Boxes as shown in the following screenshot. It’s full function without limitation in 60 days, please download and have a free trial now.

doc-insert-multiple-checkboxes9

Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download and free trial Now!


arrow blue right bubble Insert and delete multiple checkboxes with Kutools for Excel

Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download and free trial Now!


Related Article:

How to quickly delete multiple checkboxes in Excel?

 

Recommended Productivity Tools

Office Tab

gold star1 Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.

Kutools for Excel

gold star1 Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!

gold star1 200 New Features for Excel, Make Excel Much Easy and Powerful:

  • Merge Cell/Rows/Columns without Losing Data.
  • Combine and Consolidate Multiple Sheets and Workbooks.
  • Compare Ranges, Copy Multiple Ranges, Convert Text to Date, Unit and Currency Conversion.
  • Count by Colors, Paging Subtotals, Advanced Sort and Super Filter,
  • More Select/Insert/Delete/Text/Format/Link/Comment/Workbooks/Worksheets Tools...

Screen shot of Kutools for Excel

btn read more btn download btn purchase

Comments  

Permalink +10 Jamie
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"?
2013-10-30 08:06 Reply Reply with quote Quote
Permalink +1 Prem Gurung
Great trick... I enjoyed it.
2013-11-04 11:16 Reply Reply with quote Quote
Permalink 0 Daniel
Why is there only one boy in this class????
2013-11-06 09:56 Reply Reply with quote Quote
Permalink +2 Jay Chivo
Quoting Daniel:
Why is there only one boy in this class????

:lol: That's just for demonstration.
2013-11-07 01:09 Reply Reply with quote Quote
Permalink +2 Jack
excellent macro works add checkbox in the cell, thank u :-)
2013-11-19 11:35 Reply Reply with quote Quote
Permalink 0 Suleman
Excellent work. Thank you :roll:
2013-12-05 06:53 Reply Reply with quote Quote
Permalink +1 O
AWESOME! Thank you so much!
2013-12-05 23:42 Reply Reply with quote Quote
Permalink 0 # 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!
2013-12-13 00:39 Reply Reply with quote Quote
Permalink +1 Dennis
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!
2013-12-26 16:57 Reply Reply with quote Quote
Permalink +6 Lior
I did it but when I check one boc, all are being checked together. how can I separate them?
2014-01-02 12:04 Reply Reply with quote Quote
Permalink +1 Violet
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!
2014-01-27 18:21 Reply Reply with quote Quote
Permalink 0 Jackeroo
Quoting Violet:
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!

For Each Rng In WorkRng
With Ws.CheckBoxes.A dd((Rng.Offset( 0, 1).Left - Rng.Left) / 2 + Rng.Left - CkBoxSize, Rng.Top, 0.33, Rng.Height)
.Characters.Tex t = Rng.Value
End With
2017-02-20 19:13 Reply Reply with quote Quote
Permalink 0 CM_Mario
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.
2014-01-29 12:46 Reply Reply with quote Quote
Permalink 0 Kman
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.
2014-02-08 23:18 Reply Reply with quote Quote
Permalink 0 Priyanka
Can we then filter on those check boxes? Basically display only those who attended?
2014-02-19 14:12 Reply Reply with quote Quote
Permalink 0 Priyanka
Can we then filter those check boxes? Basically display only those who attended?
2014-02-19 14:12 Reply Reply with quote Quote
Permalink +1 Kman
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.
2014-02-20 05:13 Reply Reply with quote Quote
Permalink 0 Ejaz-Ul-Haque
Thanks a lot!,
for check box.
2014-02-26 12:02 Reply Reply with quote Quote
Permalink 0 MikeC
Is there a way to have the checkbox text be the text of the selected cell?
2014-02-27 16:46 Reply Reply with quote Quote
Permalink 0 Danny
Yes. Yes, there is.
There is always a way.
2014-02-28 12:26 Reply Reply with quote Quote
Permalink +1 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
2014-03-10 14:52 Reply Reply with quote Quote
Permalink 0 Meg
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!!!
2014-03-04 01:34 Reply Reply with quote Quote
Permalink 0 FAISAL
how to change the reference address for multiple checkboxes
2014-03-09 17:50 Reply Reply with quote Quote
Permalink 0 Reuben
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
2014-03-07 00:00 Reply Reply with quote Quote
Permalink 0 FAISAL
how to change cell address
2014-03-09 17:52 Reply Reply with quote Quote
Permalink 0 Kman
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.Che ckBoxes.Count = 0 Then Exit Sub

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


For Each ckbox In ActiveSheet.Sha pes
'Test that the shape is a control.
If ckbox.Type = msoFormControl Then
'test that the control is a checkbox
If ckbox.FormContr olType = xlCheckBox Then
'find the cell under the checkbox that is within the selection
Set rngLinkedCell = Application.Int ersect(ckbox.To pLeftCell, Selection)
'assign a new link address and rename the checkbox to match.
If Not rngLinkedCell Is Nothing Then
With ckbox
.OLEFormat.Obje ct.LinkedCell = rngLinkedCell.A ddress
.Name = rngLinkedCell.A ddress
.TextFrame.Char acters.Text = rngLinkedCell.T ext
End With
End If
End If
End If
Next
2014-03-10 04:06 Reply Reply with quote Quote
Permalink 0 xyz
The code worked great. Thank you!
2014-03-28 15:09 Reply Reply with quote Quote
Permalink 0 Cole
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/inde pendent?
2014-03-28 15:59 Reply Reply with quote Quote
Permalink 0 Kman
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.
2014-04-02 04:10 Reply Reply with quote Quote
Permalink 0 Ali Mac
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.
2014-04-15 01:23 Reply Reply with quote Quote
Permalink 0 Jackeroo
Quoting Ali Mac:
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.


For Each Rng In WorkRng
With Ws.CheckBoxes.A dd((Rng.Offset( 0, 1).Left - Rng.Left) / 2 + Rng.Left - 8, Rng.Top, 0.33, Rng.Height)
.Characters.Tex t = Rng.Value
End With
2017-02-20 19:11 Reply Reply with quote Quote
Permalink 0 Ali Mac
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.
2014-04-15 23:44 Reply Reply with quote Quote
Permalink 0 Jennebeth Monserat
it really helped a lot. thank to you!
2014-04-17 11:57 Reply Reply with quote Quote
Permalink 0 Ed
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 '
2014-04-21 16:12 Reply Reply with quote Quote
Permalink 0 Brian
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?
2014-04-30 18:44 Reply Reply with quote Quote
Permalink 0 Kman
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.
2014-05-04 03:46 Reply Reply with quote Quote
Permalink 0 Jessica
:-) 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!
2014-06-27 17:08 Reply Reply with quote Quote
Permalink +1 Kman
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
2014-07-01 02:42 Reply Reply with quote Quote
Permalink 0 TipTup21
Great Post!

Is there a Way to do the VBA Code with toggle buttons?
2014-12-05 20:26 Reply Reply with quote Quote
Permalink 0 JeffFromTims
I'm attempting to make a call log (in a table format) that has check boxes. I want to be able to sort/filter the table and have the check boxes move accordingly with the other data. At the moment, they're fixed.
2015-02-10 15:35 Reply Reply with quote Quote
Permalink 0 LLD
Hello, this macro is a GREAT timesaver - thank you! I'm terrible with VBA, but I was trying to determine which line(s) in the code I may be able to comment to keep the range-reminder dialog from appearing. Is that possible?

I'm prepping some checklists for our marketing group and entering a MILLION checkboxes (so you can imagine how much this macro helps), but I have to stop with each group/range and click on that darn dialog.

Any way to disable it so I can maybe re-enable it later? Thanks for any help! -LLD
2015-02-27 20:34 Reply Reply with quote Quote
Permalink 0 Kamolga
THANK YOU!!! Great post!
2015-02-28 18:18 Reply Reply with quote Quote
Permalink 0 Matthew
Really good macro

How do insert them already checked? i.e. their value is already 1?
Thanks
2015-06-23 09:43 Reply Reply with quote Quote
Permalink 0 sam
Hi Just followed your code to create a list of checkboxes on my spreadsheet, I am very new to this but have managed to follow it and worked perfectly. I just cant remove the checkbox labels coming up? i have a pretty long spreadsheet and deleting them individually is impossible. can i remove the labels that come up with each checkbox??? please help
2015-07-09 02:29 Reply Reply with quote Quote
Permalink 0 Ruby Lee
Why is my name in this list? I did not take this class.
2015-10-30 23:32 Reply Reply with quote Quote
Permalink 0 Nesh
Hye there. im new here n new for using excel.can i know how to put other excel sheet under one excel sheet. example when i open 1 excel sheet and i got my audit plan. Then i got list of country overall such as Canada and Belgium. So what i wan to know how if i click Canada and my audit plan for Canada will appear.
2016-09-27 10:45 Reply Reply with quote Quote
Permalink 0 Jeff
I just purchased the program. When I insert the checkboxes, they are slightly lower than the cell itself, so they are encroaching into the cell below. How can I reposition each of those cells?
2017-05-06 17:23 Reply Reply with quote Quote
Permalink 0 Tianao Li
Great macro, gracefully written!!! Thank you!
2017-06-09 17:34 Reply Reply with quote Quote

Add comment


Security code
Refresh