I must say this is certainly one of the best checkboxes techniques I have seen being demonstrated on Excel tutorials. I can't thank you enough Randy for showing a cool, simple way to achieve the effect which I can now adopt and run with it to create some impressive work.
Man, I wish you to have LOTS of money, because you help us so much. I'm signing in to your mentoring prgram for sure as soon as I (financially) can. Seriously.
Thank you so much Marco. I really appreciate that and love to bring value to you. Can't wait to have you in the Mentorship program as well. Thank you for your Likes, Shares & Comments. It really helps.
Even VBA CheckBox is available to use, still i like your way using SYMBOLS , it gave me an idea also that i can use any SYMBOL ( For Transportation Taxi or Bus..) using same method.
Thank you very much for this and is such an easier way then how I was putting checkboxes before!! Just experienced an issue where it no longer inserts a checkbox if I click on the box in column? Any ideas? Using Excel 2016.
Hi Keith, thanks so much for the comment. I am not sure why it would stop working. You can take a screenshot of your code and post it in our Excel For Freelancers Facebook Group here: bit.ly/groupexcel We have 30,000 Members who would love to help you with this. Thanks so much.
Thanks! This was a very clever solution, well done. Exactly what I needed, especially once I adjusted the code slightly so that the boxes only tick through double click. That prevents accidents through tab/keyboard. A couple of question if you would be so kind. 1. What if I have a true table structure. How would I use a dynamic range instead of E7:E16? Lets say the column has the name/header "Check" and the table is called "Table1". 2. If I tick a box, then I need to click outside of that cell in order to be able to un-tick it. Is there a code that fixes this? Not a big problem if you use DoubleClick instead of Selection, so this is more out of curiosity. Thanks again! :)
Hi and thanks for your comment and questions. I am glad you liked the training. You could easily use a dynamic range or simply set the range in VBA to a very large number. If you want VBA to select outside the cell you could add this line of code before the macro ends. Activesheet.offset(0,1).select This will select the cell to the right automatically after the check is entered. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here:bit.ly/groupexcel
Hi, thanks for one of the best learn Videos about VBA and Excel. I have a Question please. How can I search a row Nummer with a specific Text and use it to fill the tables?
Hi and thanks you can use several options including the Match formula which will match exact, you can also use the "Find" function within VBA. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here: bit.ly/groupexcel
Great technique. I think there might be one issue with the code that is setting the "All" checkbox to 2 (partial) when clicking individual rows. Just clicking a cell should not set value of the All checkbox to 2 (partial). If you click 1 row and then unclick 1 row you'll have no rows checked but the All checkbox will show the state of partial (2). Same if you check all rows 1 at a time. When you check the last row it should set the All checkbox to 1 (checked). Thus I think you have to loop through all rows to determine if all rows are blank (uncheck All), all have a value (check All), else partial (2).
Hi and thanks so much for your feedback. You can run a filter to display only non-blank rows, however in a standard dataset there should never be blank rows, and at least the unique ID should contain a value. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here: bit.ly/groupexcel
Using the technique you described above, which by the way, it worked great. How can I add a single check box that can clear all the boxes with one click and also is it possible to make the boxes be able to select or deselect by double clicking like an on/off switch unlike what has to be done now, that you have to go to another cell and then comeback to deselect it.
Hi and thanks so much this training includes a clear all checkbox as well which is the top left checkbox. Also you can change the event instead of Selection_Change to BeforeDoubleClick which will run the macro on a double click. I hope this helps and thanks so much.
Thank you for this amazing tutorial. I have only one issue let's say. I will like to print the selected/checked row's using print selection but if I do that my page is blank. Is there any way to do that? Thanks!
If you want to only print the selected rows, I would do an Advanced filter, and copy to another range, then print that filtered range. You can see how to do an advanced filter with my Drill Down Data video. I hope that helps.
Thank so much. It works great for large tables. Check out the Employee Manager series here in my Channel to see other ways how these checkboxes can be used
@@ExcelForFreelancers I had a problem with coping wingdings signs to VBA editor, so I found page: answers.microsoft.com/en-us/msoffice/forum/all/wingdings-symbols-in-vba/21da1a07-0437-4701-80d7-04c873cf2dd5 there they advised to use the Chr(168) function
Hi, I'm just going through a selection of your back catalogue of videos hence my comments on them, please don't think I'm being critical! Anyway, if you change the custom number format of your cell with the Checkbox in it to: þ;¨;þ then in module1 in your vba create a single line sub or function: Sub updateChecks(rangeToUpdate As Range, updateStatus As Boolean) rangeToUpdate = 1 * updateStatus End Sub then in Selection Change, one line: If Target.Cells.Count = 1 Then Call Module1.updateChecks(Target, Not Target) and CheckBox_Click, just one line too: Call Module1.updateChecks(Range("B2:B15"), Not CheckBox1.Value) 'where B2:B15 is your range of checked cells It's a little more succinct than the existing one. In your version your effectively writing the same procedure twice (you're changing a range from TRUE to FALSE), just that the selection change is passing a single cell, and the Checkbox a range. It allows the strengths of cell formatting to shine through too, which are perfect for true/false 1/0 combinations. I'll try not to hijack any more of your excellent videos for now!
Hi and thanks. It is not necessary if you select another cell in the code such as Sheet1.Range("A1").select This way you don't have to click another cell. I hope this helps and thanks so much.
Great, this saves me a lot of time.... :-) If I want to have this on multiple ranges, is there a way to set these in the script ? I've added a 2nd intersect, but that gives an error If Not Intersect(Target, Range("a1:a20")) Is Nothing Then If Not Intersect(Target, Range("f1:f20")) Is Nothing Then Or do I just repeat the full script for each range I want ? I have quite a lot ....
Hi and thanks you can use this in multiple cells in a range such as If Not Intersect(Target, Range("A1:A20")) Is Nothing Then 'Your Action End If or If Not Intersect(Target, Range("A1,A20")) Is Nothing Then 'Your Action End If I have a free VBA course taught by MVP Sumit Bansal on my site right here: www.excelforfreelancers.com/enroll-in-the-free-excel-vba-course-by-mvp-sumit-bansal-of-trumpexcel/
Just what I have been looking for, simple, elegant, just perfect, it would be even better if I could double click the cell to toggle between options? is there a a piece of code to enable this to happen?
Hi Glenn, Thanks very much, I am glad you like the videos. It is super simple to change it from Single Click to Double Click, just place the code that I added between these two lines of code INSTEAD of the Selection Change Lines of code: Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) End Sub
Hi Randy! Quick question for you please. What if I don't not want the range to be fixed, but automatically dependent on the table lenght, what should I do please? Thanks
Hi Esaie, thanks for your comment. You can modify the range in VBA to include the entire column such as Range("C:C") I hope this helps and thanks so much.
Hi thanks, certainly it's possible. Just go for it. If you have specific and technical issues on that please feel free to post your question in our Excel For Freelancers Facebook group here: bit.ly/groupexcel
Hi Randy, great technique . I have a table with three separate section. Each section has a header with title.and no check box and each row below is with check box. Everything works well but if I add new row the conditional formatting gets messed up. Is there way to fix that? Thanks in advance
Hi thanks for your comment. You may want to extend the conditional formatting to the limit of your table and then use two conditions in your formula so that the condition is only displayed when you have a value in the first column. For example if you have table Range from A2:D9999 you can use a formula like this =and($A2"",Row()=$B$3) This way there has be be a value in columns A (Be sure no $ before the 2) I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here:bit.ly/groupexcel
Nice work.. But found some issues while testing When you click the same cell twice you cant change it ( you have to leave it and click on it again) When you deaktivate all manually the select all checkbox still is gray But thanks anyway i will improve this till its perfect 👌
Any way this could be used for excel mobile? Wanting to be able to do this with iPads for my work, is that possible? If so could you point me in the direction on how to create this same form and be able to make it “clickable” with iPads?
HI and thanks. You can add something like Sheet1.Range("A1").select to the end of your macro. Selecting any other cell in your macro will allow the user to select the same cell again. Thank you for your Likes, Shares & Comments. It really helps.
Hi and thanks very much. Feel free to download this and customize it however you like. If you need help with this, I would be happy to refer this out to a qualified developer. Can you please email me all of your specifications in a detailed scope and I can forward it to a developer? Randy@ExcelForFreelancers.com Thanks so much.
Thank you so much for making this video, however, I am using Japanese Windows, so even I can get the particular font to have the check box in the sheet, I couldn't get the particular font in the VBA code. It becomes "?" in the VBA code. Could you give me some suggestions about the font issue in the VBA code?
HI yes you can use the character equivalent in VBA such as Sheet1.Range("A1").value = Chr(252) I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here: bit.ly/groupexcel
@@ExcelForFreelancers Thank you so much for your quick response. I have joined the Excel For Freelancers Facebook Group, I shall post my question later if I have any. I really appreciate all of this.
A question: How can I make the "checkall" macro to respect my blank cells (no data) so it doesn't add unnecessary checkboxes in all the column. I can already put the checkboxes with IF fuction (where I desire) while in the sheet but once I run the macro to overall check/uncheck it just fill the whole column.
Hi Kimberly you can run a loop (if its not to much data) and inside that loop check for blank, and skip, otherwise check. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here: bit.ly/groupexcel
Hi and thanks very much. This allows to show a display if it is mixed. If a user selects some, but not all, we want to have a display for that in an icon. I hope this helps and thanks so much.
This is very useful information. I have a question though. So lets say there are 10 columns and 5 rows. Each column is made up of check boxes like in your video. Row 1 has 3 boxes checked Row 2 has 7 boxes checked Row 3 has 6 boxes checked Row 4 has 0 boxes checked Row 5 has 5 boxes checked If I want to apply conditional formatting so that all rows with less than 5 checked boxes are formatted one color, all rows with exactly 5 checked boxes to be one color, and all rows with more than 5 checked boxes to be another color. Ex Row 1 and 4 would be Red Row 5 would be Blue Row 2 and 3 would be Green and if I were to uncheck a box from row 3, row 3 would become Blue. What would I have to do to achieve that effect?
You can use a hidden 'helper row' This row will count all of the necessary check boxes you want to count in that row. You can then use conditional formatting based on the # of counted check boxes in that new helper column. I hope this helps. For further assistance please join our active Facebook group here: bit.ly/groupexcel
Very useful tutorial. Everything looks fine on my end with the checkboxes but when one of my coworkers opens the document some of the checkboxes look like little medals (hard to explain without showing a picture). Any idea what this could be?
Hi Lexi, thanks for the comment. Your coworker is probably missing the Wingdings font. Please make sure they have this font installed. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here:bit.ly/groupexcel
Hello Sir Could you help on my problem. I have 10 checkBoxes in userform. I checked 5 or more checkboxes. When I click "combine" Commandbutton I need the selected checkboxes to combine and goes to the textbox1. When i click Commandbutton "ADD" the data from the textbox1 goes to cells. Thanks.
How can I apply the formula for selecting all rows if I will duplicate the sheet? In your video it Is specifically formulated to sheet 5. What if I will duplicate the sheet
HI Mark in that case you would want to simple use a value to value copy to copy large ranges such as Sheet1.("A4:N15").value = Sheet1.("A4:N15").value I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here:bit.ly/groupexcel
Thanks for the video. It's really nice as usual I have two question: how to how many customers I have from table where customers names are repeated?? Second question is: how to know how many families in certain city from table contains first name, last name, and city Please give me the answers It is important
Hi thanks for your comment and I am happy you like the videos. The best way to have your questions answered is through our Facebook group. I have been focused on creating some amazing Excel courses for all Excel enthusiasts and I have created a Facebook group with 6000 Excel Exerts so that many of us can help you with your Excel questions. Can you please post your detailed question, with samples or screenshots directly in our group? If you have not Joined yet, please join here: bit.ly/groupexcel. This way we can get you the answers you need, fast.
You can use the Countif() function to find duplicate for the customer names eg CountIf(Columns("$D:$D"), Range(D1)). Otherwise you can use 'Remove Duplicates' feature under Data on the Excel menu to remove them. As for your second question, you can use Pivot table to find the answer.
Hey! So I have checked and didn't find my question in the comments. What if I want to create only 2 options: empty box and crossed box that will be change each other. Let's say I want to maintain empty cells. I tried this code but it doesn't work (A5 and B5 are the symbols of box) If Not Intersect(Target, Range("L4:L13")) Is Nothing Then If Target.Value = Range("A5").Value Then Target.Value = Range("B5").Value ElseIf Target.Value = Range("B5").Value Then Target.Value = Range("A5").Value End if Thank you!
Hi and thanks very much. You can use something like this: If Not Intersect(Target, Range("L4:L13")) Is Nothing Then If Target.Value = Chr(252) then Target.ClearContents Else Target.Value = Chr(252) End if I hope this helps and thanks so much.
Hi yes sure, selection is either from a mouse or from a cursor. If you automate the tab order to avoid these cells then this would fix the issue. You can see how to automate tab orders in this video: czcams.com/video/40aYorpxPiU/video.html I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question with code screenshots here:bit.ly/groupexcel
Great but what if I want checkboxes be interactive depending if there is data in the table or not so checkboxes grow as data in the table grows automatically.
Hi thanks for your question. Using this system of checkboxes, you just need to adjust the code for the maximum amount of data. Even 500,000 rows this would work fine, so this solution works as your data grows. A simple IF / Then Statement to only use the checkbox feature if there is data in the row would work very well.
Sir i am facing one problem in this that is after insert tickbox and Write VBA code for tickbox when i am selecting the "customers name" then it will rewrite "þ" this sign automatically then why it is happening i tried a lot in code but not able to solve this problem please Help But only i have use this code in VBA "if Target.value=Chr(254) then Target.value=Chr(168) Else Target,Value=Chr(254)" because that symbol(þ) in not able to paste in VBA Code Thanks...
Hi , Thanks for your comment. It would be difficult for us to help unless we see the specific bug you are running into. Can you upload screenshots of your bug and sheet into our Excel For Freelancers Facebook Group here: bit.ly/groupexcel We have 60,000 Members who would love to help you with this. Thanks so much.
Hello Randy, Others too, this worked for me. But when I am creating another table 2-3 rows under the old check box table, I am being given this error: If Not Intersect(Target, Range("J6:J21" & "J35:J38")) Is Nothing Then and this is becoming yellow. Where I am going wrong Please Help! Thank You Randy!
Hi Kunalj, thanks for your comment. Try If Not Intersect(Target, Range("J6:J21,J35:J38")) Is Nothing Then I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here: bit.ly/groupexcel
Hi Ngoc, thanks for your question. Insert Form or ActiveX controls is fine when you have a few rows, but if you have hundreds or thousands of rows it is not practical to insert this many. So this solution works for unlimited rows. I hope this helps and thanks. Cam on em.
Absolutely, there is almost no limit in which you can increase the range of columns or rows, simply change the range inside the code (after you have downloaded my workbook) and it will work for any range, such as E7:Z9999 You can also use conditional formatting to format only those rows with data, so the table looks nice.
I give you a thumbs up for the works. However, I do not prefer to pre-set a max range especially when you speak of a nice looking table. Let me use your table as an example, you have a thick line frame from E6:L16. if it is format as a table, whenever a row is added, the thick line frame expands one row down. If I set the range as E7:Z9999 and click select-all, it always highlight more than the table range. I believe we can do better than that. What do you think?
Yes for sure. For the Select All macro, you would use a 'variable' for the last row, such as: With Sheet1 Dim LastRow as Long LastRow = .Range("F99999").end (xlup).row If .CheckBoxes("Check Box 7").Value = 1 Then .Range("E7:E" & LastRow).Value = "þ" Else: .Range("E7:E" & LastRow).Value = "¨" End If End With This will check for the last row of Data in column F, and only put checkmarks until that last row of data.
Hello, i am very new with VBA and know almost nothing. I already have an existing code on my sheet and i followed your steps to insert check boxes on my worksheet but i am getting error that said "compile error: argument not optional" please advise what i am doing wrobg or what i am missing. I woukd greatly appreciate your help!
Perhaps put your cursor over the highlighted yellow line and check all of your variables for missing values or errors. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question with code screenshots here:bit.ly/groupexcel
I am getting a Run time Error showing it after *******: Option Explicit Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Not Intersect(Target, Range("A6:A17")) Is Nothing Then If Target.Count > 1 Then Exit Sub If Target.Value = "þ" Then Target.Value = "¨" Else: Target.Value = "þ" ******* CheckBoxes("Check Box 7").Value = 2 End If End Sub Also when I click on cells which don't have symbols on them yet, instead of getting the "Checked Symbol"" I get the " þ" ????? Not sure how to fix it?
HI and thanks so much. Instead of using the symbols, use the character equivalent. I teach you how to do this in this video: czcams.com/video/eF678YvQ93g/video.html I hope this helps and thanks so much.
Hi , Thanks for your comment. It would be difficult for us to help unless we see the specific bug you are running into. Can you upload screenshots of your bug and sheet into our Excel For Freelancers Facebook Group here: bit.ly/groupexcel We have 40,000 Members who would love to help you with this. Thanks so much.
Hi it would be hard to know the exact cause of the error unless we see the code and workbook. Can you please join our Excel For Freelancers Facebook Group here: bit.ly/groupexcel Post your detailed question along with screen shots so we can get that fixed for you. Thanks very much.
Hi Apurv, sorry you are getting the issue. I have created a Facebook group with 8000 Excel Exerts so that many of us can help you with your Excel questions. Can you please post your detailed question, with samples or screenshots directly in our group? If you have not Joined yet, please join here: bit.ly/groupexcel. This way we can get you the answers you need, fast.
HI Nicholas. Thanks very much for your comment. Make sure you have the font within the column set to Wingdings and also the font inside VBA Editor should be a western font such as Arial (Western). I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here: bit.ly/groupexcel
Hi Don, thanks for your comment. If you have just a few tht would work fine, however if you have a table with thousands of items that you want to use checkboxes it would. be a lot more time consuming to set up. Thanks for watching.
Nice but too complicated for the less experts. I wonder why don't you use a checkbox from the developer menu from the beginning rather that the symbol. Thanks anyways
Hi That is a great question. The reason I did not use checkboxes from the developers menu is that within a table we can have hundreds or thousands of checkboxes. If we had to use this style of checkboxes it would take too long to program and very difficult to manage. Form or ActiveX checkboxes work great when we have just a few and want to place them above the cells of a table. Thanks very much for your feedback
yeah! that's a brilliant point I didn't think about before, but you're absolutely right, it's a pain to programme those checkboxes for a long list...I'll give your method a go...thanks again for sharing this :)
This is an excellent question and answer, I have wondered about that too. Now I appreciate the ActiveX checkboxes is useful as some sort of a user submission form to capture a small number of fixed input. While the wingdings checkboxes is useful for data manipulation with the intention to expand to hundreds of rows of records. This is fantastic!
🔥Download 350 Of The Worlds Best Templates Here ► rebrand.ly/350Wkbks_YTPinnedCom
I must say this is certainly one of the best checkboxes techniques I have seen being demonstrated on Excel tutorials. I can't thank you enough Randy for showing a cool, simple way to achieve the effect which I can now adopt and run with it to create some impressive work.
Thank's so much Lena, I always appreciate your feedback and comments, and I'll try not to shed tears on constructive criticism as well. :)
I would deeply appreciate you for establishing new technic, creative, useful videos.
HI thank so much for your comment. I will do my best to create and share any new techniques that I discover
We wanted to create tick boxes on several number of cells and sheets and this is very very helpful. Thank you so much for sharing.
HI and thanks so much for your support. I am really glad you like the training.
Absolutely amazing tutorial!!
Very Satisfying.
Applied it to my own spreadsheet for an investment portfolio.
Thank you very much sir!
For sure, you are very welcome, I am so glad you enjoyed it Chris. Thank you for your Likes, Shares & Comments. It really helps.
This is brilliant
Thank you so very much, I really appreciate that.
Hello Randy, as you see, you got a new fan of your *top* Excel ideas. 👍 Your VBA skills are amazing. Thanks a lot. 👍 🌟 🌟
Thank you so much K D, I am super glad you enjoy the videos. I have nearly 150 of them so you have a lot to choose from :)
Man, I wish you to have LOTS of money, because you help us so much. I'm signing in to your mentoring prgram for sure as soon as I (financially) can.
Seriously.
Thank you so much Marco. I really appreciate that and love to bring value to you. Can't wait to have you in the Mentorship program as well. Thank you for your Likes, Shares & Comments. It really helps.
Hi Marco, can you share that code?
Awesome video. Can create some really nice tools for my team at work!
Thank you so very much, I really appreciate that.
Another great lesson! You are teaching me lots of cool stuff Randy, very much appreciated!
For sure, you are very welcome Rob. I am happy you are enjoying the training.
Even VBA CheckBox is available to use, still i like your way using SYMBOLS , it gave me an idea also that i can use any SYMBOL ( For Transportation Taxi or Bus..) using same method.
Hi and thanks so much for your comment. That is a great idea as well. Thank you for your Likes, Shares & Comments. It really helps.
wonderful.. u r skilled.. good work.. appreciate it.. keep doing the good work
Thank you so much Gideon. I appreciate that.
Beautiful ❤️❤️❤️
Thank you so very much, I really appreciate that.
very impressive, this was an extremely elegant method :)
many thanks for that vid
Thanks so much. I am really glad you liked it.
Thank you very much for this and is such an easier way then how I was putting checkboxes before!!
Just experienced an issue where it no longer inserts a checkbox if I click on the box in column? Any ideas? Using Excel 2016.
Hi Keith, thanks so much for the comment. I am not sure why it would stop working. You can take a screenshot of your code and post it in our Excel For Freelancers Facebook Group here: bit.ly/groupexcel
We have 30,000 Members who would love to help you with this. Thanks so much.
Thank you so much. You are so smart!!!!
Thanks so much Mark, I am really glad you liked it.
Nice video 💖
Thanks for the visit
Now All I wan to call you ... *Sir* !! 🔥
Thank you very much. I do appreciate that Godson.
Just great video, again very interesting approach, Thank you ,Cheers
thanks so much. I am glad yo like it.
Thanks! This was a very clever solution, well done.
Exactly what I needed, especially once I adjusted the code slightly so that the boxes only tick through double click. That prevents accidents through tab/keyboard.
A couple of question if you would be so kind.
1. What if I have a true table structure. How would I use a dynamic range instead of E7:E16? Lets say the column has the name/header "Check" and the table is called "Table1".
2. If I tick a box, then I need to click outside of that cell in order to be able to un-tick it. Is there a code that fixes this? Not a big problem if you use DoubleClick instead of Selection, so this is more out of curiosity.
Thanks again! :)
Hi and thanks for your comment and questions. I am glad you liked the training. You could easily use a dynamic range or simply set the range in VBA to a very large number.
If you want VBA to select outside the cell you could add this line of code before the macro ends.
Activesheet.offset(0,1).select
This will select the cell to the right automatically after the check is entered.
I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here:bit.ly/groupexcel
Great video Sir. I appreciate your efforts
Thanks so much I am glad you liked it.
Hi,
thanks for one of the best learn Videos about VBA and Excel.
I have a Question please.
How can I search a row Nummer with a specific Text and use it to fill the tables?
Hi and thanks you can use several options including the Match formula which will match exact, you can also use the "Find" function within VBA.
I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here: bit.ly/groupexcel
Great technique. I think there might be one issue with the code that is setting the "All" checkbox to 2 (partial) when clicking individual rows. Just clicking a cell should not set value of the All checkbox to 2 (partial). If you click 1 row and then unclick 1 row you'll have no rows checked but the All checkbox will show the state of partial (2). Same if you check all rows 1 at a time. When you check the last row it should set the All checkbox to 1 (checked). Thus I think you have to loop through all rows to determine if all rows are blank (uncheck All), all have a value (check All), else partial (2).
Hi and thanks so much for your feedback. You can run a filter to display only non-blank rows, however in a standard dataset there should never be blank rows, and at least the unique ID should contain a value. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here: bit.ly/groupexcel
So much useful stuff, I have to check all your videos :) Well done, subscribed !
Fantastic, I just uploaded a new one I think you will love: czcams.com/video/40aYorpxPiU/video.html
Using the technique you described above, which by the way, it worked great. How can I add a single check box that can clear all the boxes with one click and also is it possible to make the boxes be able to select or deselect by double clicking like an on/off switch unlike what has to be done now, that you have to go to another cell and then comeback to deselect it.
Hi and thanks so much this training includes a clear all checkbox as well which is the top left checkbox. Also you can change the event instead of Selection_Change to BeforeDoubleClick which will run the macro on a double click.
I hope this helps and thanks so much.
Thank you for this amazing tutorial. I have only one issue let's say. I will like to print the selected/checked row's using print selection but if I do that my page is blank.
Is there any way to do that? Thanks!
If you want to only print the selected rows, I would do an Advanced filter, and copy to another range, then print that filtered range. You can see how to do an advanced filter with my Drill Down Data video. I hope that helps.
Thank you!
Lovely. Loving it.
I am glad you love it.
I really like those clickable checkboxes
Thank so much. It works great for large tables. Check out the Employee Manager series here in my Channel to see other ways how these checkboxes can be used
@@ExcelForFreelancers I had a problem with coping wingdings signs to VBA editor, so I found page:
answers.microsoft.com/en-us/msoffice/forum/all/wingdings-symbols-in-vba/21da1a07-0437-4701-80d7-04c873cf2dd5
there they advised to use the Chr(168) function
Hi, I'm just going through a selection of your back catalogue of videos hence my comments on them, please don't think I'm being critical!
Anyway, if you change the custom number format of your cell with the Checkbox in it to:
þ;¨;þ
then in module1 in your vba create a single line sub or function:
Sub updateChecks(rangeToUpdate As Range, updateStatus As Boolean)
rangeToUpdate = 1 * updateStatus
End Sub
then in Selection Change, one line:
If Target.Cells.Count = 1 Then Call Module1.updateChecks(Target, Not Target)
and CheckBox_Click, just one line too:
Call Module1.updateChecks(Range("B2:B15"), Not CheckBox1.Value) 'where B2:B15 is your range of checked cells
It's a little more succinct than the existing one. In your version your effectively writing the same procedure twice (you're changing a range from TRUE to FALSE), just that the selection change is passing a single cell, and the Checkbox a range. It allows the strengths of cell formatting to shine through too, which are perfect for true/false 1/0 combinations. I'll try not to hijack any more of your excellent videos for now!
Rico S Also great suggestion, thank you.
Nice
Thank you so very much, I really appreciate that.
Great video. I just wish you didn't have to click off the cell to change it back to unchecked
Hi and thanks. It is not necessary if you select another cell in the code such as
Sheet1.Range("A1").select
This way you don't have to click another cell.
I hope this helps and thanks so much.
Excellent idea and execution. Where did you get these nice icons for the buttons? Thanks
Hi thanks very much. I am glad you liked it. Many icons I have collected over the years, and some I get from here: icons8.com/iconizer/
Many thanks for your videos.
Good
Thank you so very much, I really appreciate that Forhad
Great, this saves me a lot of time.... :-)
If I want to have this on multiple ranges, is there a way to set these in the script ?
I've added a 2nd intersect, but that gives an error
If Not Intersect(Target, Range("a1:a20")) Is Nothing Then
If Not Intersect(Target, Range("f1:f20")) Is Nothing Then
Or do I just repeat the full script for each range I want ? I have quite a lot ....
Hi and thanks you can use this in multiple cells in a range such as
If Not Intersect(Target, Range("A1:A20")) Is Nothing Then
'Your Action
End If
or
If Not Intersect(Target, Range("A1,A20")) Is Nothing Then
'Your Action
End If
I have a free VBA course taught by MVP Sumit Bansal on my site right here: www.excelforfreelancers.com/enroll-in-the-free-excel-vba-course-by-mvp-sumit-bansal-of-trumpexcel/
Just what I have been looking for, simple, elegant, just perfect, it would be even better if I could double click the cell to toggle between options? is there a a piece of code to enable this to happen?
Hi Glenn, Thanks very much, I am glad you like the videos. It is super simple to change it from Single Click to Double Click, just place the code that I added between these two lines of code INSTEAD of the Selection Change Lines of code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
End Sub
Hi Randy! Quick question for you please. What if I don't not want the range to be fixed, but automatically dependent on the table lenght, what should I do please? Thanks
Hi Esaie, thanks for your comment. You can modify the range in VBA to include the entire column such as Range("C:C")
I hope this helps and thanks so much.
Randy Help! I need to add these checkboxes within the Drill down feature you previously demonstrated. Can we combine the two features?
Hi thanks, certainly it's possible. Just go for it. If you have specific and technical issues on that please feel free to post your question in our Excel For Freelancers Facebook group here: bit.ly/groupexcel
Hi Randy, great technique . I have a table with three separate section. Each section has a header with title.and no check box and each row below is with check box. Everything works well but if I add new row the conditional formatting gets messed up. Is there way to fix that? Thanks in advance
Hi thanks for your comment. You may want to extend the conditional formatting to the limit of your table and then use two conditions in your formula so that the condition is only displayed when you have a value in the first column. For example if you have table Range from A2:D9999 you can use a formula like this =and($A2"",Row()=$B$3)
This way there has be be a value in columns A (Be sure no $ before the 2)
I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here:bit.ly/groupexcel
Nice work..
But found some issues while testing
When you click the same cell twice you cant change it ( you have to leave it and click on it again)
When you deaktivate all manually the select all checkbox still is gray
But thanks anyway i will improve this till its perfect 👌
Hi George, you can simple add at the end of the code to select another cell such as Sheet1.Range("A1").Select
Any way this could be used for excel mobile? Wanting to be able to do this with iPads for my work, is that possible? If so could you point me in the direction on how to create this same form and be able to make it “clickable” with iPads?
HI and thanks very much for your comment Michael. This video will help you do that: czcams.com/video/4QGIWjSBTUo/video.html
I just noticed you can change it if you click on the same cell again. what's the workaround?
HI and thanks. You can add something like Sheet1.Range("A1").select to the end of your macro. Selecting any other cell in your macro will allow the user to select the same cell again. Thank you for your Likes, Shares & Comments. It really helps.
Though i didn't watch the entire videow because of metered network .
Is there away we can copy all cheked boxes and paste to somewhere else? 🤔
Hi and thanks very much. Feel free to download this and customize it however you like. If you need help with this, I would be happy to refer this out to a qualified developer. Can you please email me all of your specifications in a detailed scope and I can forward it to a developer? Randy@ExcelForFreelancers.com Thanks so much.
Thank you so much for making this video, however, I am using Japanese Windows, so even I can get the particular font to have the check box in the sheet, I couldn't get the particular font in the VBA code. It becomes "?" in the VBA code. Could you give me some suggestions about the font issue in the VBA code?
HI yes you can use the character equivalent in VBA such as Sheet1.Range("A1").value = Chr(252)
I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here: bit.ly/groupexcel
@@ExcelForFreelancers Thank you so much for your quick response. I have joined the Excel For Freelancers Facebook Group, I shall post my question later if I have any. I really appreciate all of this.
A question: How can I make the "checkall" macro to respect my blank cells (no data) so it doesn't add unnecessary checkboxes in all the column. I can already put the checkboxes with IF fuction (where I desire) while in the sheet but once I run the macro to overall check/uncheck it just fill the whole column.
Hi Kimberly you can run a loop (if its not to much data) and inside that loop check for blank, and skip, otherwise check. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here: bit.ly/groupexcel
Sir why did you Enable" Mixed" Option of "Select all" checkbox from Properties what its work
Hi and thanks very much. This allows to show a display if it is mixed. If a user selects some, but not all, we want to have a display for that in an icon.
I hope this helps and thanks so much.
عالی
Thank you so very much, I really appreciate that.
This is very useful information. I have a question though.
So lets say there are 10 columns and 5 rows. Each column is made up of check boxes like in your video.
Row 1 has 3 boxes checked
Row 2 has 7 boxes checked
Row 3 has 6 boxes checked
Row 4 has 0 boxes checked
Row 5 has 5 boxes checked
If I want to apply conditional formatting so that all rows with less than 5 checked boxes are formatted one color, all rows with exactly 5 checked boxes to be one color, and all rows with more than 5 checked boxes to be another color.
Ex
Row 1 and 4 would be Red
Row 5 would be Blue
Row 2 and 3 would be Green
and if I were to uncheck a box from row 3, row 3 would become Blue.
What would I have to do to achieve that effect?
You can use a hidden 'helper row' This row will count all of the necessary check boxes you want to count in that row. You can then use conditional formatting based on the # of counted check boxes in that new helper column. I hope this helps. For further assistance please join our active Facebook group here: bit.ly/groupexcel
Very useful tutorial. Everything looks fine on my end with the checkboxes but when one of my coworkers opens the document some of the checkboxes look like little medals (hard to explain without showing a picture). Any idea what this could be?
Hi Lexi, thanks for the comment. Your coworker is probably missing the Wingdings font. Please make sure they have this font installed. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here:bit.ly/groupexcel
Hello Sir
Could you help on my problem.
I have 10 checkBoxes in userform.
I checked 5 or more checkboxes.
When I click "combine" Commandbutton
I need the selected checkboxes to combine and goes to the textbox1.
When i click Commandbutton "ADD" the data from the textbox1 goes to cells.
Thanks.
HI please watch this video to learn how to do that. Thanks so much.
Very useful, thanks :O)
Thank you, much appreciated
How can I apply the formula for selecting all rows if I will duplicate the sheet? In your video it Is specifically formulated to sheet 5. What if I will duplicate the sheet
HI Mark in that case you would want to simple use a value to value copy to copy large ranges such as
Sheet1.("A4:N15").value = Sheet1.("A4:N15").value
I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here:bit.ly/groupexcel
Thanks for the video. It's really nice as usual
I have two question: how to how many customers I have from table where customers names are repeated??
Second question is: how to know how many families in certain city from table contains first name, last name, and city
Please give me the answers
It is important
Hi thanks for your comment and I am happy you like the videos. The best way to have your questions answered is through our Facebook group. I have been focused on creating some amazing Excel courses for all Excel enthusiasts and I have created a Facebook group with 6000 Excel Exerts so that many of us can help you with your Excel questions. Can you please post your detailed question, with samples or screenshots directly in our group? If you have not Joined yet, please join here: bit.ly/groupexcel. This way we can get you the answers you need, fast.
You can use the Countif() function to find duplicate for the customer names eg CountIf(Columns("$D:$D"), Range(D1)). Otherwise you can use 'Remove Duplicates' feature under Data on the Excel menu to remove them. As for your second question, you can use Pivot table to find the answer.
Hey! So I have checked and didn't find my question in the comments. What if I want to create only 2 options: empty box and crossed box that will be change each other. Let's say I want to maintain empty cells. I tried this code but it doesn't work (A5 and B5 are the symbols of box)
If Not Intersect(Target, Range("L4:L13")) Is Nothing Then
If Target.Value = Range("A5").Value Then Target.Value = Range("B5").Value
ElseIf Target.Value = Range("B5").Value Then Target.Value = Range("A5").Value
End if
Thank you!
Hi and thanks very much. You can use something like this:
If Not Intersect(Target, Range("L4:L13")) Is Nothing Then
If Target.Value = Chr(252) then Target.ClearContents Else Target.Value = Chr(252)
End if
I hope this helps and thanks so much.
Fantástico
Gracias Amigo :)
When I tried this it also checks or uncheck if I move the cursor using the arrow keys in the e7:e16 range
Hi yes sure, selection is either from a mouse or from a cursor. If you automate the tab order to avoid these cells then this would fix the issue. You can see how to automate tab orders in this video: czcams.com/video/40aYorpxPiU/video.html
I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question with code screenshots here:bit.ly/groupexcel
👏👏👏👏👏👏 obrigado
Thanks very much :)
Great but what if I want checkboxes be interactive depending if there is data in the table or not so checkboxes grow as data in the table grows automatically.
Hi thanks for your question. Using this system of checkboxes, you just need to adjust the code for the maximum amount of data. Even 500,000 rows this would work fine, so this solution works as your data grows. A simple IF / Then Statement to only use the checkbox feature if there is data in the row would work very well.
@@ExcelForFreelancers could you please explain more.
Sir i am facing one problem in this that is after insert tickbox and Write VBA code for tickbox when i am selecting the "customers name" then it will rewrite "þ" this sign automatically then why it is happening i tried a lot in code but not able to solve this problem please Help
But only i have use this code in VBA "if Target.value=Chr(254) then Target.value=Chr(168) Else Target,Value=Chr(254)" because that symbol(þ) in not able to paste in VBA Code
Thanks...
Hi , Thanks for your comment. It would be difficult for us to help unless we see the specific bug you are running into. Can you upload screenshots of your bug and sheet into our Excel For Freelancers Facebook Group here: bit.ly/groupexcel We have 60,000 Members who would love to help you with this. Thanks so much.
Hello Randy, Others too, this worked for me. But when I am creating another table 2-3 rows under the old check box table, I am being given this error:
If Not Intersect(Target, Range("J6:J21" & "J35:J38")) Is Nothing Then and this is becoming yellow. Where I am going wrong Please Help! Thank You Randy!
Hi Kunalj, thanks for your comment. Try If Not Intersect(Target, Range("J6:J21,J35:J38")) Is Nothing Then
I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here: bit.ly/groupexcel
Can I ask why we didn't use the Insert in Development ribbon instead of writing vba code to create a checkbox in first part of this video ?
Hi Ngoc, thanks for your question. Insert Form or ActiveX controls is fine when you have a few rows, but if you have hundreds or thousands of rows it is not practical to insert this many. So this solution works for unlimited rows. I hope this helps and thanks. Cam on em.
@@ExcelForFreelancersThank you so much for your answer. That why you only inserted the checkbox for choosing all items . Cam on anh nhieu
What if I have more rows of information? Is it possible to make it auto expand to whatever the max row with content? instead of just E7:E16
Absolutely, there is almost no limit in which you can increase the range of columns or rows, simply change the range inside the code (after you have downloaded my workbook) and it will work for any range, such as E7:Z9999
You can also use conditional formatting to format only those rows with data, so the table looks nice.
I give you a thumbs up for the works. However, I do not prefer to pre-set a max range especially when you speak of a nice looking table. Let me use your table as an example, you have a thick line frame from E6:L16. if it is format as a table, whenever a row is added, the thick line frame expands one row down. If I set the range as E7:Z9999 and click select-all, it always highlight more than the table range. I believe we can do better than that. What do you think?
Yes for sure. For the Select All macro, you would use a 'variable' for the last row, such as:
With Sheet1
Dim LastRow as Long
LastRow = .Range("F99999").end (xlup).row
If .CheckBoxes("Check Box 7").Value = 1 Then
.Range("E7:E" & LastRow).Value = "þ"
Else:
.Range("E7:E" & LastRow).Value = "¨"
End If
End With
This will check for the last row of Data in column F, and only put checkmarks until that last row of data.
Victor Winner for sure you can add a filter to only select rows with values. This would work wel
Hello, i am very new with VBA and know almost nothing.
I already have an existing code on my sheet and i followed your steps to insert check boxes on my worksheet but i am getting error that said "compile error: argument not optional" please advise what i am doing wrobg or what i am missing. I woukd greatly appreciate your help!
Perhaps put your cursor over the highlighted yellow line and check all of your variables for missing values or errors. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question with code screenshots here:bit.ly/groupexcel
If Not Intersect(Target,
CHECK YOUR CODE IT'S COMMA AFTER THE TARGET, / IT'S NOT TARGET.
Please you create stock controls system with ms excel vba thank
Ok thanks for your suggestion. I will add it to the list.
I am getting a Run time Error showing it after *******:
Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("A6:A17")) Is Nothing Then
If Target.Count > 1 Then Exit Sub
If Target.Value = "þ" Then Target.Value = "¨" Else: Target.Value = "þ"
******* CheckBoxes("Check Box 7").Value = 2
End If
End Sub
Also when I click on cells which don't have symbols on them yet, instead of getting the "Checked Symbol"" I get the " þ" ?????
Not sure how to fix it?
HI and thanks so much. Instead of using the symbols, use the character equivalent. I teach you how to do this in this video: czcams.com/video/eF678YvQ93g/video.html
I hope this helps and thanks so much.
it is showing compile error : argument not optional . how to remove this error. please help
Hi , Thanks for your comment. It would be difficult for us to help unless we see the specific bug you are running into. Can you upload screenshots of your bug and sheet into our Excel For Freelancers Facebook Group here: bit.ly/groupexcel We have 40,000 Members who would love to help you with this. Thanks so much.
I wrote the same code but I'm getting the Error As "Argument not optional", Could you please help me to solve this. I'm beginner learning VBA.
Hi it would be hard to know the exact cause of the error unless we see the code and workbook. Can you please join our Excel For Freelancers Facebook Group here: bit.ly/groupexcel Post your detailed question along with screen shots so we can get that fixed for you. Thanks very much.
If Not Intersect(Target,
CHECK YOUR CODE IT'S COMMA AFTER THE TARGET, / IT'S NOT TARGET.
Hello I am getting compile error for the same code saying argument not optional
Hi Apurv, sorry you are getting the issue. I have created a Facebook group with 8000 Excel Exerts so that many of us can help you with your Excel questions. Can you please post your detailed question, with samples or screenshots directly in our group? If you have not Joined yet, please join here: bit.ly/groupexcel. This way we can get you the answers you need, fast.
If Not Intersect(Target,
CHECK YOUR CODE IT'S COMMA AFTER THE TARGET, / IT'S NOT TARGET.
looks like the windings checkbox code is different
HI Nicholas. Thanks very much for your comment. Make sure you have the font within the column set to Wingdings and also the font inside VBA Editor should be a western font such as Arial (Western).
I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here: bit.ly/groupexcel
Easier way is to use Checkbox control under Developer tab.
Hi Don, thanks for your comment. If you have just a few tht would work fine, however if you have a table with thousands of items that you want to use checkboxes it would. be a lot more time consuming to set up. Thanks for watching.
Nice but too complicated for the less experts. I wonder why don't you use a checkbox from the developer menu from the beginning rather that the symbol. Thanks anyways
Hi That is a great question. The reason I did not use checkboxes from the developers menu is that within a table we can have hundreds or thousands of checkboxes. If we had to use this style of checkboxes it would take too long to program and very difficult to manage. Form or ActiveX checkboxes work great when we have just a few and want to place them above the cells of a table. Thanks very much for your feedback
yeah! that's a brilliant point I didn't think about before, but you're absolutely right, it's a pain to programme those checkboxes for a long list...I'll give your method a go...thanks again for sharing this :)
Awesome, That's great to hear.
This is an excellent question and answer, I have wondered about that too. Now I appreciate the ActiveX checkboxes is useful as some sort of a user submission form to capture a small number of fixed input. While the wingdings checkboxes is useful for data manipulation with the intention to expand to hundreds of rows of records. This is fantastic!
I should be more beneficial if it becomes in hindi language
Hi thanks very much. If someone will supply the Hindi translations they can add it to this video. :) Thanks for your comment.