I watched a lot of youtube vids before finding yours and none of the other suggestions were working. Your macro worked like a charm. Thank you for saving so much of my time.
i've been looking for a video or post that showed exactly this for a long time...most previous ones used overly-complicated, extremely confusing code to do the same exact thing. thank you! this will be huge time saver for me in the future.
I'm just getting into excel. I'm working on a project that requires me to create a "generator", and an area to store saved ideas from said generator. This video SAVED me, man. Copy and pasting check-boxes was driving me crazy. Thank you! Edit: A question for the knowing, can a similar macro be done for conditional formatting? Copy and pasting a rule-set that then updates based on the values of the new cells it is in?
Thank you Corey. Your comments are much appreciated. Yes, you can copy and paste Conditional Formatting rules by either copy then Paste Special options to formatting. This includes Conditional Formatting. There is also the Format Painter button on the left of the Home tab that will do it. This button is brilliant!!!
Excellent demonstration. Quick question - If I have lots of checkboxes in the same worksheet, but only want to use this macro on a subset of them, how would I modify the macro? Could I do it by selecting the appropriate checkboxes first, or adjust for a particular row/column? Thanks
that is a very useful tip, alan! i have wathced your previous videos, which were also a great help, but this video, with the use of a simple macro is really a huge help! thanks for all your tips and tricks.
Thank you for this video. Very useful and helped me immensely. I have a question though, every time I open the excel sheet with Macro, it asks me to enable Macros and then opens the document as a 'Document2'. Then I have to save it again and override the original so I don't end up having 2 or more copies of the same excel sheet. How can I fix it please as its becoming quite a nuisance now saving every time :)? Thank you
Yes you could use something like below. Dim OleObj As OLEObject For Each OleObj in Activesheet.OLEObjects If Typename(OleObj.Object) = "ComboBox" Then 'your code to offset the cell link End If Next
Hi,Thank you for the video. It's wonderful. Could you please assist me. When running the code, I am getting a compile error stating "A module is not a valid type".
Thanks for your great video, could you please to tell me how to use two checkbox to reflect on one cell Ex: if i need to show 1 if both of column and row checkboxes are selected. And show 0 if one of them is now selected. Thx in advance.
Any issues with having the Link Cell on the same cell as the ActiveX checkboxes e.g. chk.TopLeftCell.Offset(0,0).Address rather than moving one column to the left as illustrated in the tutorial? I gather the reasons why Link Cell is used is to capture what has been checked/unchecked through the Boolean True/False value assigned to the cell for the purpose of doing more data manipulation and filtering.
You are absolutely right with the reasons for the link cell Lena. I can't see a reason why you couldn't use the same cell as the check box. give it a try.
Will do. I thought it would be neater that way and negates the need to have it on another column which may be accidentally deleted or tampered with. Thanks for the validation on my comments :) Love your tutorials.
Thank you! I've watched many videos on this topic, and this is the most helpful solution I've found for this problem. But if I have 200 boxes (some checked and most unchecked), is there a way to write the code so that if you already have numerous unchecked boxes, they will automatically register as "False" without having to manually check and uncheck all of them?
You're welcome Lindsay! You could crate a select all checkbox like you get with the Excel Filter. Use a loop just like in this macro but have it uncheck all the boxes for you instead of link to a cell
You're welcome. I believe the issue here is what I mention at 05:45 regarding the top left corner of the checkbox. Ensure that your assigning the correct linked cell.
@@Computergaga Thank you Thank you THANK YOU!! That was it. The box was just very slightly overlapping the cell above it. I resized and copied over the check boxes with the new ones, (Looks abit over lapped) but it works as intended now. Thanks again. PS. Do you know an easy way of bulk deleting the check boxes? I tried clear contents, cutting and pasting them all out but it didn't work. Thank you :)
I am looking for a way to let the income statement REPORT expand and add the new expenses or revenue categories automatically in their correct places when I add them to the categories tables, can you advise?
Thank you so much for posting this! Quick question: I'm using this to copy a row with several check boxes and paste (in the subsequent rows) many sets of that copy. The cell references for all the new check boxes remain the same as the originals until I open the VBA module again and click "Run". After that, all the cell references update properly. Is there a way to make the cell references update properly without having to perform this (seemingly unnecessary) step?
Is there any reason why you would to 'TopLeftCell' instead of just link it to the left cell?? Your macro certainly worked for me. I'm trying to make a excel program that can calculate the percentage of certain brand's sales amount out of the total sales amount at a mall. So I assigned check boxes next to all the brands and when I check the box, the result would show the summed percentage of all the checked brands out of the total. But for some reason, the percentage doesn't add up to 100% even though all the boxes are checked :/
hi id like to know how to hide a check box completely depending if a date is entered into let say g5 then i want a check box to appear in g1. if i take the date out of g5 then the check box disapears
Active X controls are more flexible. Form controls are the standard controls built into Excel, which are normally all you need. You can find more information here - support.office.com/en-us/article/overview-of-forms-form-controls-and-activex-controls-on-a-worksheet-15ba7e28-8d7f-42ab-9470-ffb9ab94e7c2
Absolutely. The following code would activate check box 3 (when you click a check box the name will appear in the top left name box) if cell A1 contain sally. If Range("A1").Value = "sally" Then ActiveSheet.CheckBoxes("Check Box 3").Value = True End If
Hi, I just have query regarding this code. Suppose, I have a excel sheet there are multiple fields like slno, employee id, firstname, lastname, address etc and there is a huge entries which is more than 10000. I wan't to add a checkbox for each entries and as soon as I click on checkbox employee id to be highlighted with background color and font color. But, I don't wan't to show whether it is true or false from any other cell when I select the checkbox. I wan't to setup if condition in macro code if checkbox is true then, employee id should get highlighted or If checkbox is false then employee id should get ride off from background color or cell color. Is there any approach we can use the macro code and hide true or false from any other cell ?
Is there a way to copy the cell and paste in different workbook or a sheet in order. When checkbox is ticked. And each time opening the workbook do i have to run the macro.
The macro only needs to be run once. Yes you can copy and paste the cell containing the checkbox and the cell that you linked the checkbox to and it should work. The cell you linked the checkbox to would be the same on the destination sheet/workbook. You could run the macro again if you needed it different.
I need some help. I'm trying to use the code on the description, but instead of having true or false value on the active sheet I would like to have it in sheet2 for example. can anybody help me please?
You can reference a cell to see if it contains TRUE or FALSE on another sheet by using If Worksheets("Sheet2").Range("A3").value = TRUE then .... End If
This technique is used because the cell to link to is relative to the checkbox i.e. to it left. But you can reference a column absolutely with the Range object such as Range("B4")or with a row number variable Range("B" & RowNum)
Why would you do that Richard? :) It would complicate things but can absolutely be done. You would get the code to link it to the next empty cell to its left instead. This would however create inconsistencies. So whatever formulas, charts or macros that you are using the checkboxes for just got more difficult.
I'm not sure. When I protect a worksheet it will no stop me checking a checkbox whether the checkbox is locked or not. Have you used the form control checkbox or activex? Maybe your in Design Mode? Or when you open the spreadsheet content has been disabled?
I need to know how to make a yes and no check box that also shows the total of each in a separate box - please help if you can. I have been trying for 2 weeks. . help
Instead of chk.TopLeftCell.Offset(0,-1).Address you would reference the cell you want. For example, Sheets("Sheet2").Range("A2") You would need a loop through the cells on the other sheet though for each checkbox. Referencing the cell to the left was simpler.
What will be the vba codes if checkboxes are linked with cells with true or false in column C, starting from C4, instead of A4??? Please let me know. Thanks in advance!
The checkbox itself will return only TRUE or FALSE as it can only return a logical value. But, in another cell you can use an IF to display the correct text i.e., if cell B2 had the result from the checkbox of TRUE or FALSE =IF(B","Paid","Pending")
Very useful, you've just saved me hours of tedious work, and so simply and clearly explained too. thank you.
You're welcome, thank you Richard.
I watched a lot of youtube vids before finding yours and none of the other suggestions were working. Your macro worked like a charm. Thank you for saving so much of my time.
My pleasure. Glad I could help, Mathew 👍
THANK YOU!!! I have been looking all over for this! Such an elegant solution to a painful headache 😍
Happy I could help.
i've been looking for a video or post that showed exactly this for a long time...most previous ones used overly-complicated, extremely confusing code to do the same exact thing. thank you! this will be huge time saver for me in the future.
Great to hear. My pleasure Martial.
Very useful, you made it so simple and easy to understand. Many thank's for all the excellent work.
You're very welcome. Thank you.
Not all heroes wear capes, I was about to give up. Thanks.
😎 Good work, Alca 👍
Thank you. Huge time saver.
Excellent - nifty little VBA code sub routine.
Thank you phsagar.
I'm just getting into excel. I'm working on a project that requires me to create a "generator", and an area to store saved ideas from said generator.
This video SAVED me, man. Copy and pasting check-boxes was driving me crazy. Thank you!
Edit: A question for the knowing, can a similar macro be done for conditional formatting? Copy and pasting a rule-set that then updates based on the values of the new cells it is in?
Thank you Corey. Your comments are much appreciated.
Yes, you can copy and paste Conditional Formatting rules by either copy then Paste Special options to formatting. This includes Conditional Formatting. There is also the Format Painter button on the left of the Home tab that will do it. This button is brilliant!!!
Excellent demonstration. Quick question - If I have lots of checkboxes in the same worksheet, but only want to use this macro on a subset of them, how would I modify the macro? Could I do it by selecting the appropriate checkboxes first, or adjust for a particular row/column? Thanks
Absolutely spot on - had amend and not do the offset but it worked so easy! thank you so so much
You're welcome, Natasha. Thank you.
You saved my time in 3 years apart.
Great to hear. Thank you Jon.
Very kind of you. I appreciate the demonstration.
Thanks CavalierNSN.
that is a very useful tip, alan! i have wathced your previous videos, which were also a great help,
but this video, with the use of a simple macro is really a huge help! thanks for all your tips and tricks.
My pleasure Colin.
Thank you, still working
You saved my day 🥰
You're welcome!
Hi there, thank you - you saved me a lot of time - useful tip.
You're welcome. Thank you, Fabio.
Thanks. Great solution to frequent need to set up checkboxes in worksheets. I’m going to see if this will work with Excel data tables (ListObjects).
You're welcome, Jim. Thanks.
This saved me sooo much time, thank you.
You're welcome John.
Živ bio 1000 godina! Svaka čast! Thank you! :D
My pleasure. Thank you.
thank you for this it's very helpful
You're very welcome!
Thank you Computergaga - that's awesome
Great to hear Jen.
this help me out a lot ,thanks
Great to hear, Freddy 👍
Just brilliant mate, thanks so much.
Where should i begin if if want to learn coding like this in excel?
No problem.
My Excel VBA course for beginners is a great place to start 😊 bit.ly/37XSKfZ
Thank you bro! God bless you!
My pleasure, Ignacio. Thank you.
Thanks so much for your help!
You're welcome, thank you.
Thank you for this video. Very useful and helped me immensely. I have a question though, every time I open the excel sheet with Macro, it asks me to enable Macros and then opens the document as a 'Document2'. Then I have to save it again and override the original so I don't end up having 2 or more copies of the same excel sheet. How can I fix it please as its becoming quite a nuisance now saving every time :)? Thank you
Thanks for your help. I've been looking for it for so long :)
Also i want to ask you something. Is this method works with comboboxes. How can i select all combo boxes and offset their linked their cell ?
Yes you could use something like below.
Dim OleObj As OLEObject
For Each OleObj in Activesheet.OLEObjects
If Typename(OleObj.Object) = "ComboBox" Then
'your code to offset the cell link
End If
Next
Thank you very much :)
Your welcome Fatih.
Thank you, it helped me a lot!!
Great to hear, Jonathan.
Very nice trick.
Very nice
Thank you for this
You're welcome Ser, thank you.
Thank you for sharing the code., however, i am facing issues when filtering the list:
can you help solving this issue.,
Hi,Thank you for the video. It's wonderful. Could you please assist me. When running the code, I am getting a compile error stating "A module is not a valid type".
Thanks for your great video, could you please to tell me how to use two checkbox to reflect on one cell
Ex: if i need to show 1 if both of column and row checkboxes are selected. And show 0 if one of them is now selected.
Thx in advance.
Any issues with having the Link Cell on the same cell as the ActiveX checkboxes e.g. chk.TopLeftCell.Offset(0,0).Address rather than moving one column to the left as illustrated in the tutorial? I gather the reasons why Link Cell is used is to capture what has been checked/unchecked through the Boolean True/False value assigned to the cell for the purpose of doing more data manipulation and filtering.
You are absolutely right with the reasons for the link cell Lena.
I can't see a reason why you couldn't use the same cell as the check box. give it a try.
Will do. I thought it would be neater that way and negates the need to have it on another column which may be accidentally deleted or tampered with. Thanks for the validation on my comments :) Love your tutorials.
I can confirm that this does work!
Thank you! I've watched many videos on this topic, and this is the most helpful solution I've found for this problem. But if I have 200 boxes (some checked and most unchecked), is there a way to write the code so that if you already have numerous unchecked boxes, they will automatically register as "False" without having to manually check and uncheck all of them?
You're welcome Lindsay! You could crate a select all checkbox like you get with the Excel Filter. Use a loop just like in this macro but have it uncheck all the boxes for you instead of link to a cell
@@Computergaga That makes sense. I tried this and it worked well. Thank you so much!
Thank you so much for this. I am having the issue though that only when I check the checkbox below does the row above it change a different color.
You're welcome. I believe the issue here is what I mention at 05:45 regarding the top left corner of the checkbox.
Ensure that your assigning the correct linked cell.
@@Computergaga Thank you Thank you THANK YOU!! That was it. The box was just very slightly overlapping the cell above it. I resized and copied over the check boxes with the new ones, (Looks abit over lapped) but it works as intended now. Thanks again.
PS. Do you know an easy way of bulk deleting the check boxes? I tried clear contents, cutting and pasting them all out but it didn't work.
Thank you :)
GREAT! SMART! THANKS
🤩🤩🤩🤩🤩
You're welcome!
I am looking for a way to let the income statement REPORT expand and add the new expenses or revenue categories automatically in their correct places when I add them to the categories tables, can you advise?
THANK YOU
Your welcome.
Thank you so much for posting this! Quick question: I'm using this to copy a row with several check boxes and paste (in the subsequent rows) many sets of that copy. The cell references for all the new check boxes remain the same as the originals until I open the VBA module again and click "Run". After that, all the cell references update properly. Is there a way to make the cell references update properly without having to perform this (seemingly unnecessary) step?
I'm not sure Ben. All seems a little strange to me.
Is there any reason why you would to 'TopLeftCell' instead of just link it to the left cell??
Your macro certainly worked for me. I'm trying to make a excel program that can calculate the percentage of
certain brand's sales amount out of the total sales amount at a mall.
So I assigned check boxes next to all the brands and when I check the box, the result would show the
summed percentage of all the checked brands out of the total. But for some reason, the percentage doesn't add up to 100%
even though all the boxes are checked :/
Maybe the total percentage is a rounding issue.
How did you manage to hide the "true" and False" words even though it has been assigned to (0,-1)? Will I have to create another macro for this?
Hey i am getting this error.
Run-time error '1004':
Method 'LinkedCell' of object 'CheckBox' failed
I am on the latest version on Mac Office .
Hi Luke,
Sorry I know there are difference but I'm not very familiar with Excel on the Mac.
Did you find out to fix this?
Super!
Thank you.
hi id like to know how to hide a check box completely depending if a date is entered into let say g5 then i want a check box to appear in g1. if i take the date out of g5 then the check box disapears
What is difference bet ActiveX and form component?
Active X controls are more flexible. Form controls are the standard controls built into Excel, which are normally all you need.
You can find more information here - support.office.com/en-us/article/overview-of-forms-form-controls-and-activex-controls-on-a-worksheet-15ba7e28-8d7f-42ab-9470-ffb9ab94e7c2
What if I want to link in to column B instead of A
What if I have 5 checkboxes in my sheet and I want only the checkbox on Column J to activate the True/False ( to the right i.e to column K)?
Is there a way to active the checkboxes in relation to a cell using macros. Say if in A1 is written Sally The checkbox in B6 will become active.
Absolutely. The following code would activate check box 3 (when you click a check box the name will appear in the top left name box) if cell A1 contain sally.
If Range("A1").Value = "sally" Then
ActiveSheet.CheckBoxes("Check Box 3").Value = True
End If
+Computergaga Thank you so much!
Thank you!!
My pleasure.
I'm using this code verbatim but doesn't seem to work on ActiveX checkboxes, any reasons why this might be the case?
Hi,
I just have query regarding this code.
Suppose, I have a excel sheet there are multiple fields like slno, employee id, firstname, lastname, address etc and there is a huge entries which is more than 10000.
I wan't to add a checkbox for each entries and as soon as I click on checkbox employee id to be highlighted
with background color and font color.
But, I don't wan't to show whether it is true or false from any other cell when I select the checkbox.
I wan't to setup if condition in macro code if checkbox is true then, employee id should get highlighted or
If checkbox is false then employee id should get ride off from background color or cell color.
Is there any approach we can use the macro code and hide true or false from any other cell ?
Genius
Thank you Jaypee.
Is there a way to copy the cell and paste in different workbook or a sheet in order. When checkbox is ticked. And each time opening the workbook do i have to run the macro.
The macro only needs to be run once. Yes you can copy and paste the cell containing the checkbox and the cell that you linked the checkbox to and it should work.
The cell you linked the checkbox to would be the same on the destination sheet/workbook. You could run the macro again if you needed it different.
I need some help.
I'm trying to use the code on the description, but instead of having true or false value on the active sheet I would like to have it in sheet2 for example.
can anybody help me please?
How to check in automatic but based on status true or false in another sheet. thank you
You can reference a cell to see if it contains TRUE or FALSE on another sheet by using
If Worksheets("Sheet2").Range("A3").value = TRUE then
....
End If
Hi there, May I know how I can link to a particular column instead of using (0, -1)?
This technique is used because the cell to link to is relative to the checkbox i.e. to it left.
But you can reference a column absolutely with the Range object such as Range("B4")or with a row number variable Range("B" & RowNum)
How to make check box macro that strikethrough the cell
So when a check box is checked a different cell gets a diagonal line across it?
How would the code differ if you have 2 check boxes in 1 cell?
Why would you do that Richard? :)
It would complicate things but can absolutely be done. You would get the code to link it to the next empty cell to its left instead. This would however create inconsistencies. So whatever formulas, charts or macros that you are using the checkboxes for just got more difficult.
how can i activat the time with a check box
This works when not protected. When I protect the sheet it will not allow a check even though the properties is unlocked. Am I forgetting something?
I'm not sure. When I protect a worksheet it will no stop me checking a checkbox whether the checkbox is locked or not.
Have you used the form control checkbox or activex? Maybe your in Design Mode? Or when you open the spreadsheet content has been disabled?
I need to know how to make a yes and no check box that also shows the total of each in a separate box - please help if you can. I have been trying for 2 weeks. . help
Check out this video Chuck - ruclips.net/video/bWlnIhCMjfM/видео.html
nice video but how can I link to cells in another worksheet?
Instead of chk.TopLeftCell.Offset(0,-1).Address you would reference the cell you want. For example, Sheets("Sheet2").Range("A2")
You would need a loop through the cells on the other sheet though for each checkbox. Referencing the cell to the left was simpler.
@@Computergaga hi thx for your answer. i solved it by string concatenation and different offset:
"Sheet!" &chk.TopLeftCell.Offset(i, 2).Address
What will be the vba codes if checkboxes are linked with cells with true or false in column C, starting from C4, instead of A4??? Please let me know. Thanks in advance!
I'm getting a run time error 438 - object does not support this property or method...not sure what i did
Sounds like a possible mistype somewhere Ron. I would double check the syntax.
HOW ABOUT INSTED OF TRUE OR FALSE . I WANT TO PUT PAID OR PENDING HOW SHOULD I DO THAT?
The checkbox itself will return only TRUE or FALSE as it can only return a logical value. But, in another cell you can use an IF to display the correct text i.e., if cell B2 had the result from the checkbox of TRUE or FALSE
=IF(B","Paid","Pending")
This is not working when I insert a macro for 0, -1 nothing shows up to the left...
The checkboxes I'm using are ActiveX