Add checkboxes and copy values – VBA
Table of Contents
1. Add Checkboxes to a Worksheet
In this section, I will demonstrate a macro that creates checkboxes in nonempty rows, see checkboxes in column E in image above.
The button "Add Checkboxes" next to column E is assigned to macro Addcheckboxes(), press with left mouse button on the button and the macro will be rund.
The same thing goes with the button "Remove Checkboxes" next to column E is assigned to macro RemoveCheckboxes(), press with left mouse button on the button and that macro will be rund.
VBA code - Add checkboxes
'Name macro
Sub Addcheckboxes()
'Declare variables and data types
Dim cell, LRow As Single
Dim chkbx As CheckBox
Dim CLeft, CTop, CHeight, CWidth As Double
'Don't refresh or update screen while processing macro, this will make the macro quicker.
Application.ScreenUpdating = False
'Find last non empty cell in column A
LRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
'Iterate through 2 to last non empty cell
For cell = 2 To LRow
'Check if cell in column A is not equal to nothing
If Cells(cell, "A").Value <> "" Then
'Save cell dimensions and coordinates of corresponding cell in column E to variables
CLeft = Cells(cell, "E").Left
CTop = Cells(cell, "E").Top
CHeight = Cells(cell, "E").Height
CWidth = Cells(cell, "E").Width
'Create checkbox based on dimension and coordinates data from variables
ActiveSheet.CheckBoxes.Add(CLeft, CTop, CWidth, CHeight).Select
With Selection
.Caption = ""
.Value = xlOff
.Display3DShading = False
End With
End If
Next cell
'Turn on screen refresh
Application.ScreenUpdating = True
End Sub
VBA code - Remove checkboxes
'Name macro
Sub RemoveCheckboxes()
'Declare variables and data types
Dim chkbx As CheckBox
'Iterate through all check boxes on active sheet
For Each chkbx In ActiveSheet.CheckBoxes
'Remove checkbox
chkbx.Delete
'Continue with next checkbox
Next
End Sub
Where to copy VBA code?
- Copy above code.
- Press Alt+F11 to open the Visual Basic Editor.
- Press with right mouse button on on your workbook in the Project Explorer, see image above.
- Press with left mouse button on "Insert".
- Press with left mouse button on "Module".
- Paste VBA code to the module.
- Exit VBE and return to Excel.
I have assigned the macros to two buttons: "Add Checkboxes" and "Remove Checkboxes", the top image shows these buttons.
- Go to Developer tab.
- Press with left mouse button on "Insert" button.
- Press with left mouse button on "Button" button ;-)
- Press with left mouse button on and drag on the worksheet and then release mouse button to create the button.
- Select a macro.
- Press with left mouse button on OK!
In the next post I will describe how to copy selected rows to another sheet.
2. Copy selected rows based on check boxes
This section demonstrates a macro that copies selected rows based on enabled check boxes. The image above shows data on Sheet1 and checkboxes, some of them are enabled and those will be copied to Sheet2 if you press with left mouse button on button "Copy selected rows".
2.1. Worksheet data
The image above shows Sheet2 and records copied from Sheet1.
2.2. VBA code
'Name of macro
Sub CopyRows()
'Go through each check box in active sheet
For Each chkbx In ActiveSheet.CheckBoxes
'If check box is enabled
If chkbx.Value = 1 Then
'Go through each row on worksheet
For r = 1 To Rows.Count
'Check if checkbox is on the same row
If Cells(r, 1).Top = chkbx.Top Then
'Simplify syntax
With Worksheets("Sheet2")
'Identify the cell right below the last non empty cell
LRow = .Range("A" & Rows.Count).End(xlUp).Row + 1
'Copy record from Sheet1 and paste to first empty row on Sheet2
.Range("A" & LRow & ":D" & LRow) = _
Worksheets("Sheet1").Range("A" & r & ":D" & r).Value
End With
'Exit For Loop
Exit For
End If
Next r
End If
Next
End Sub
2.3. Where to copy vba code?
- Copy above code.
- Press Alt+F11 in excel.
- Insert a module.
- Paste code into the code window.
- Return to Excel.
2.4. How to run the macro using a button
I have assigned the macro to a button: Copy selected rows
- Go to Developer tab
- Press with left mouse button on "Insert Controls" button
- Press with left mouse button on "Button" button
- Create the button
- Select CopyRows macro
- Press with left mouse button on OK!
Check boxes category
Today I will share a To-do list excel template with you. You can add text to the sheet and an […]
This tutorial shows you how to list excel files in a specific folder and create adjacent checkboxes, using VBA. The […]
This article demonstrates how the user can run a macro by press with left mouse button oning on a button, […]
Excel categories
48 Responses to “Add checkboxes and copy values – VBA”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
You can reduce your RemoveCheckboxes macro to a one-liner...
Sub RemoveCheckboxes()
ActiveSheet.CheckBoxes.Delete
End Sub
Rick Rothstein (MVP - Excel),
Thanks!!
In your CopyRows macro, since the CheckBox is actually a Shape underneath it all, we can eliminate your inner loop by making use of its TopLeftCell property to determine the row its on.
Sub CopyRows() Â Dim LRow As Long, ChkBx As CheckBox, WS2 As Worksheet Set WS2 = Worksheets("Sheet2") LRow = WS2.Range("A" & Rows.Count).End(xlUp).Row For Each ChkBx In ActiveSheet.CheckBoxes If ChkBx.Value = 1 Then LRow = LRow + 1 WS2.Cells(LRow, "A").Resize(, 4) = Range("A" & _ ChkBx.TopLeftCell.Row).Resize(, 4).Value End If Next End SubHi Rick & Oscar
I am a bit late to the show,
However I have used this code that Rick has altered slightly of Oscars however would you be able to assist on where in the code i can determine the exact cell on sheet 2 that the paste occurs.
I have managed to change the paste column from A to C but i cant seem to find a way to have the pasting start at cell C12 rather than C2.
Any help would be great.
Thank you
Rick Rothstein (MVP - Excel),
Thank you for your valuable comments!
Hi,
I have a similar problem - I would like to use checkboxes in "sheet1" and populate the rows of data into "sheet 2" and Sheet 3"
But can this be done without the use of a macro??
Maybe using auto sum or something??
Please help,
Regards,
Hello,
I have a similar issue. I am trying to replicate your example but for some reason it does not work form. It looks as though the macro runs, but does not pick up the check box. Also, the main difference is that my header begins on row 8, but my check boxes remain in column E.
Would you be able to please advise?
Thanks in advance!!
Carlos,
I assume you have read this post?
Excel vba: Add checkboxes to a sheet (1/2) The code for adding the checkboxes are in that post.
Also, the main difference is that my header begins on row 8
Change this line
to
and all lines containing "E" to your column.
Hi Oscar,
Many thanks for the response. I am still a bit confused on your instructions. Can you validate the changes I have made below:
Sub CopyRows() For Each chkbx In ActiveSheet.CheckBoxes If chkbx.Value = 1 Then For r = 8 To Rows.Count If Cells(r, 1).Top = chkbx.Top Then With Worksheets("Products Summary") '"Products Summary" is the name of tab where I want the copied cells to be place LRow = .Range("A" & Rows.Count).End(xlUp).Row + 1 .Range("A" & LRow & ":D" & LRow) = _ Worksheets("Product Data").Range("A" & r & ":D" & r).Value ' "Product Data" is the tab where my data is located End With Exit For End If Next r End If Next End SubMany thanks again!
Also, a correction to my previous comment. My header begins on both tabs on row 7. First check box in the "Product Data" tab is on row 10. Not sure if this makes a difference.
Thanks again!
Carlos,
Yes, it seems fine. If it doesn´t work upload the file.
Dear Oscar,
When we apply the script shown above, is possible specify, for the copied rows, those columns that we really want?
( Imagine that I have 10 columns with information, but I just want to copy the information available in 5 of them. )
Best Regards and thanks for all your availability to make clear this kind of matters !
Hi Oscar,
I´ve searched for awhile, but didn´t found a solution :-(
Your vba code works fine - nice work!
It´s possible to transfer besides the values, the format (Width, High, Border, Color,..), too?
thanks for your support.
regards from Germany
Hello Mike,
try this:
Sub CopyRows() Application.ScreenUpdating = False For Each chkbx In ActiveSheet.CheckBoxes If chkbx.Value = 1 Then For r = 1 To Rows.Count If Cells(r, 1).Top = chkbx.Top Then With Worksheets("Sheet2") Worksheets("Sheet1").Range("A" & r & ":D" & r).Copy LRow = .Range("A" & Rows.Count).End(xlUp).Row + 1 .Range("A" & LRow & ":D" & LRow).PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False .Range("A" & LRow & ":D" & LRow).PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False End With Application.CutCopyMode = False Exit For End If Next r End If Next Application.ScreenUpdating = True End SubGet the Excel *.xlsm file
Copy-selected-rows-checkboxesv2.xlsm
Hi,
Just want to ask, for this macro when it copies it seems to copy the rows more than once. Any insights how to ammend this?
Bests
Melvin
Hi,
I just wanted to know how i can stop the duplication in the copy and paste section
thanks
HI I am looking for this Fucntion for my attendance thanks
Hi Oscar thanks for posting the Addcheckboxes code, it's just what I have been looking for!
I need to modify it to search through a named range ( as opposed to the last row with data. I am not proficient in VBA at all , so when I tried to alter the code it was rejected.
thanks
i,
Just want to ask, for this macro when it copies it seems to copy the rows more than once. Any insights how to automatically delete the duplicate rows from the sheet the rows are copied to?
Will ActiveSheet("sheet2").clear if put at the start of the CopyRows sub?
Oscar,
I am running the code from Rick and everything seems to be working fine. The thing I need though is for the copy button to copy everything from A to I. Right now I am only copying A to D.
Thanks for your help in advance.
Jim,
Try this
Sub CopyRows() Dim LRow As Long, ChkBx As CheckBox, WS2 As Worksheet Set WS2 = Worksheets("Sheet2") LRow = WS2.Range("A" & Rows.Count).End(xlUp).Row For Each ChkBx In ActiveSheet.CheckBoxes If ChkBx.Value = 1 Then LRow = LRow + 1 WS2.Cells(LRow, "A").Resize(, 9) = Range("A" & _ ChkBx.TopLeftCell.Row).Resize(, 9).Value End If Next End SubOscar,
I am using your code and altered it with multiple Check boxes(multiple columns of Check boxes).Every thing working fine.
As I am having for example 3 checkboxes in a single row, it copies 3 times a single row , I want separate destinations for copied data for every check box.( as it starts pasting from A column, I want it to start from A, and for 2nd check box it should start with any other column that I want and so on..)
Can you plz help
Hi Spider,
I think i am trying to do the same as you have mentioned in your previous post. I have multiple rows of data with three columns with check boxes. Each check box ticked should send the row to a different sheet. Did you manage to get a solution from anywhere?
Hi bit late to the show but I am using this code however would someone be able to assist on how I would be able to alter the code to be able to choose where the cells I paste the information to on sheet2 For example I want to paste the information starting at cell C12 i have managed to get it to paste starting at column C but cant get it to paste to C12 it still starts the pasting at C2.
Any help,
Thanks!
This is really great! Thank you! Can you tell me what code I would need to add in order to remove the original copied data? In other words, now that it has been copied from Sheet 1 to Sheet 2, how can I have it removed from Sheet 1 as part of the same process?
Thank you sir for your very useful and informative post. i want change the color row data after copying to indicate that was copied. please help
Faisal Mirza,
This code colors copied rows light blue:
Sub CopyRows() For Each chkbx In ActiveSheet.CheckBoxes If chkbx.Value = 1 Then For r = 1 To Rows.Count If Cells(r, 1).Top = chkbx.Top Then With Worksheets("Sheet2") LRow = .Range("A" & Rows.Count).End(xlUp).Row + 1 .Range("A" & LRow & ":D" & LRow) = _ Worksheets("Sheet1").Range("A" & r & ":D" & r).Value Worksheets("Sheet1").Range("A" & r & ":D" & r).Interior.ColorIndex = 37 End With Exit For End If Next r End If Next End SubHi Sir,
First of all I'd like to thank you for your code which gave me hope that my requirement is possible in excel.
I modified it as per my requirement and I must say its working 70%.
My requirement is by pressing with left mouse button on a checkbox in a particular row should select that row.So I modified your code as below.
Sub SelectRows() Dim cell, LRow As Single Dim chkbx As CheckBox Application.ScreenUpdating = False LRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row For Each chkbx In ActiveSheet.CheckBoxes If chkbx.Value = 1 Then For r = 1 To Rows.Count If Cells(r, 1).Top = chkbx.Top Then ActiveSheet.Range("A" & r & ":D" & r).Select 'With Worksheets("Sheet2") 'LRow = .Range("A" & Rows.Count).End(xlUp).Row + 1 '.Range("A" & LRow & ":D" & LRow) = _ 'ActiveSheet.Range("A" & r & ":D" & r).Value 'End With Exit For End If Next r End If Next End Subnow if I press with left mouse button on the checkbox it selects that row. I want to select multiple checkbox at a time and multiple row should be selected (as we manually select by pressing CTRL Key and Mouseover cells). Is it possible?
Please help me. I trying my best to find the solution but seems that's not enough.
Dear Mr. Oscar
Well done! Your post has reduced my all odds. need your further help. i want copy rows data from different sheets to single (MAIN) sheet.
JPG LINK BELOW MENTION
https://postimg.org/image/y8nq7wubp/
Sub CopyRows() For Each chkbx In ActiveSheet.CheckBoxes If chkbx.Value = 1 Then For r = 1 To Rows.Count If Cells(r, 1).Top = chkbx.Top Then With Worksheets("Main") LRow = .Range("C" & Rows.Count).End(xlUp).Row + 1 .Range("C" & LRow & ":J" & LRow) = _ Worksheets("1st-Party").Range("C" & r & ":J" & r).Value Worksheets("2nd-Party").Range("C" & r & ":J" & r).Interior.ColorIndex = 37 End With Exit For End If Next r End If Next End SubFaisal Mirza
Sub CopyRows() sht = Array("1st-Party", "2nd-Party", "3rd-Party") For i=LBound(sht) to Ubound(sht) For Each chkbx In Worksheets(sht(i)).CheckBoxes If chkbx.Value = 1 Then For r = 1 To Rows.Count If Cells(r, 1).Top = chkbx.Top Then With Worksheets("Main") LRow = .Range("C" & Rows.Count).End(xlUp).Row + 1 .Range("C" & LRow & ":J" & LRow) = _ Worksheets(sht(i)).Range("C" & r & ":J" & r).Value Worksheets(sht(i)).Range("C" & r & ":J" & r).Interior.ColorIndex = 37 End With Exit For End If Next r End If Next Next i End SubDear Oscar,
how would you code it if you wanted to generate a CSV and not a new sheet.
regards
HI Oscar,
I have tried to copy data from selected checkbox with the help of code which you mention above but it didnt work. Can you guid me what to do to get only selected check box data in new work sheet.
Thank you
kjp
Make sure you change Sheet1 and Sheet2 so it matches your worksheet names.
Oscar
Need your help
I need to start past in c12 in sheet2
Hi Oscar. Congrats for ur blog. Very helpful. I'm trying to use the code above to my project but I need some modifications. I want to use the checkboxes to select rows in 3 different sheets and then paste the selections to another workbook in 3 dif. sheets. Could that be possible? Thanks in advance.
Hello Oscar!
This is Sheela from Germany.
Could you please tell me what should I add to move (Cut) the selected rows from Sheeet 1 and Sheet 2.
I mean the data from Original Destination (Sheet 1)should be erased.
Thanks in Advance.
how to copy images also and paste
suppose I have two columns A and B. How to make checkbox be added automatically to column A once I type anything in column B?
Hi Oscar, I was looking something similar, instated copying into an sheet I like to copy the row in to a email body is this possible?
Thank you I was looking for this but I am having an issue with the
LRow = ActiveSheet.Range("A" & Rows.Count).End(x1Up).RowGiving me a Run-time error '1004':
Application-define or object-defined error
Nole Sheets,
Does this work?
LRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).RowHello,
I've learned quite a bit since reviewing your blog and want to thank you for such great tutorials.
I've run into something I can't quite figure out. If possible, could you assist? Here's what I want to do:
Sheet1: Contains a menu for the user.
Sheet2: Contains an extract of data that contains multiple business unit names in column A.
I want to populate a checkbox list of all the business unit names in Sheet1 with checkboxes to then allow the user to check only the business units they would like to include in the output on Sheet3.
I have code developed that assigns a random number for each row of data on Sheet3, which is then sorted to only the top 25 random numbers (a sample selection). That part of my code works fine, but am trying to learn how to take a list of business units from Column A of Sheet2 and then populate a checkbox list in Sheet1 for the user to then only select the business units they want.
I hope this makes sense, and I appreciate any help you can offer on how to go about doing this.
Kindest Regards,
Jeff
Hello Oscar,
Very usefull code.
When applyng a filter on the source data, the copied lines are different from the selected ones.
Any Idea to fix this ?
By advance,
Sire
This works great!! Is there a way to do this with a Button instead? I can not locate that option anywhere online.
ThankYou for your time.
Thanks for this very useful example! My VBA creates a table with N rows, where N is determined by the value in a named range (RowCount). I'd like to use your framework for populating one of the named columns in my table with checkboxes, but I'm not sure how to reference a table column using VBA. I'd appreciate any suggestions and thanks again for this helpful resource.
Hi,
I kinda new to vba code and I like it.
I am trying to create a function that would create a checkbox into the cell that I am in.
Any clues?
Thx
Thanks for this. I've been looking fpr something like it for a while. The only issue I have is that if the check boxes are "ticked" and then more data is added below the last line, when you run the code again it resets all the previous check boxes back to "off". Is there anyway to not change the checkboxes above the last one created?
hi Oscar,
I have managed to change the paste column from A to D but i cant seem to find a way to have the pasting start at cell D11 rather than D2.