How To EASILY Add Checkboxes To ANY Microsoft Excel Table
Aug 14, 2023
How To EASILY Add Checkboxes To ANY Microsoft Excel Table
ðĨāļĢāļąāļāđāļāļĄāđāļāļĨāļāļāļĩāđāļāļĩāļāļĩāđāļŠāļļāļ 250 āđāļāļāļāļĩāđāļāļĩāđâš https://bit.ly/250WKBK-Desc https://bit.ly/PatreonDesc http://bit.ly/MentorProgram_YTDesc http://bit.ly/Dashboard_YTDesc https://bit.ly/VBACourse_Discount https://bit.ly/VBACourse_Discount http://bit.ly/ClickCheck_Wb http://bit.ly/ClickCheck_FbDl http://bit.ly/Dashboard_YTDesc http://bit.ly/MentorProgram_YTDesc https://bit.ly/FreeVBACourse http://bit.ly/UERG_YTDesc https://bit.ly/250WKBK-Desc https://bit.ly/AutoMacroAddIn https://bit.ly/Pine-Bi http://bit.ly/SecureExcel http://bit.ly/ExcelProducts http://bit.ly/groupexcel https://bit.ly/EFF_FanPage https://twitter.com/Excel4Freelance https://www.ExcelForFreelancers.com https://www.linkedin.com/in/excelforf âĶhttps://www.facebook.com/ExcelForFree âĶhttps://www.instagram.com/excelforfre âĶhttps://rumble.com/c/ExcelForFreelancers https://locals.com/member/ExcelFreela âĶhttps://t.me/ExcelForFreelancers https://gab.com/ExcelForFreelancers
Content
0 -> hello this is Randy with Excel for freelancersÂ
and today we're going to be discussing how to Â
5.64 -> create these amazing check boxes inÂ
your Excel tables so please join me
11.19 -> alrighty let's get started okay so basically theÂ
idea is that we're going to put check boxes in Â
29.43 -> this column when we select it we'll check the boxÂ
on or off and to get those check boxes what we can Â
37.05 -> do is we can go to the insert ok tab hold ExcelÂ
and then go to symbol now once we click symbol Â
44.76 -> we'll see a lot of different fonts and a lot ofÂ
different shapes we'll use the first two will Â
49.8 -> use the unchecked and the check and the checkÂ
will look like this we'll go ahead and insert Â
54.81 -> that and you see I've already got it in the blueÂ
font here so basically it's this D here but it's a Â
62.37 -> wing-dings font ok so we want to make sure that weÂ
want that wing-dings font and then we'll go ahead Â
67.89 -> and on the upper one will enter the uncheckedÂ
one which is this symbol and then we're going Â
73.47 -> to use the unchecked one okay alright so we'veÂ
inserted the two of those and basically the idea Â
79.74 -> is we're going to use these shapes for eitherÂ
check or uncheck and so what we want to do is Â
85.32 -> we want to make sure that all of the cells hereÂ
are wing-dings font okay so that can properly so Â
91.98 -> we'll just go ahead and highlight those and makeÂ
sure that we selected wing-dings and check that Â
97.26 -> okay so now that every cell is the correct font inÂ
the column that we want to put okay and the idea Â
105.03 -> is when we select this one we want it to uncheckÂ
when we select this one one of the checks we Â
110.64 -> basically want it in our DBA to change it okay andÂ
to do that we'll go into our VBA and we can go to Â
119.28 -> ways once again we can do alt f11 okay that willÂ
launch our VB or we can go into the developers Â
126.21 -> tab here and if you don't have this developers tabÂ
simply go into file options okay customized ribbon Â
134.26 -> you can see the developers tab right here okay soÂ
make sure this is selected and once we're into the Â
141.73 -> Visual Basic we want to go into sheet one becauseÂ
this is the sheet that we're going to be working Â
146.56 -> off of sheet one okay so we want to make sureÂ
that the work we're doing and the idea is that Â
151.99 -> when we select a cell here we want some action soÂ
we're going to be using the selection change okay Â
160.93 -> so in VBA on cheat one you're going to go intoÂ
the worksheet click there and selection change Â
168.64 -> came up automatically but you could do it for anyÂ
other one okay so selection change all right and Â
174.34 -> now we're going to say hey if we click anywhere inÂ
this range here we want some action to take place Â
183.64 -> so that range is e seven through sixteen okay soÂ
we're going to go ahead and write some code if not Â
192.19 -> intersect target range okay and that range was eÂ
seven correct G seven through e 16 okay through Â
207.01 -> e 16 okay and parenthesis and then go twice thereÂ
and then is nothing then and basically what that Â
220.3 -> says is those are like a double negative here notÂ
and nothing so basically that they'll cancel each Â
225.91 -> other out and it means if there is some selectionÂ
in this range do something okay now what do we Â
233.35 -> want it to do well basically what we want to do isÂ
if if there's if this is the target and we want to Â
243.16 -> change it to selected if it's selected we wantÂ
to check if it's checked we want to uncheck so Â
248.08 -> basically let's go ahead and write some code nowÂ
okay but to do that we've also got to know what Â
253.24 -> the values are that we need to change okay whatÂ
the actual values are so we'll go ahead and take Â
258.73 -> a look at this and if we look here that checkboxÂ
that values that be looking thing okay so we're Â
266.14 -> going to copy at control-c and then exit out ofÂ
the cell in order to go back into the VBA okay Â
274.27 -> so basically what we want to say is if the targetÂ
value is equal print B okay close quotation marks Â
286.18 -> then okay we want the target value to changeÂ
target value to change - what - change - that Â
294.61 -> uncheck marks equals okay we're going to ahead andÂ
pause the code just first second so it doesn't run Â
300.13 -> while we're copying and pasting this so thatÂ
is looks like this double mark here so we'll Â
308.38 -> go ahead and copy and Pat copy that and exit outÂ
of the cell we'll go back in to VBA and then we'll Â
316.87 -> do quotation mark cop paste that in and then inÂ
the quotation mark so basically we're saying if Â
323.2 -> target value is B then change it okay but ifÂ
it's not be right if it's not the check mark Â
330.28 -> that we need to change it to the check mark soÂ
we're going to write some code there also else Â
334.78 -> meaning if it's if it's not this that we need toÂ
change it to this okay so that we only have two Â
341.92 -> options else then all we need to do is copy thatÂ
right and change it to that okay so now so now Â
352.48 -> we have this and now if we see how that works nowÂ
we can reset the code again okay simply by going Â
361.03 -> in and resetting the code going out moving in ourÂ
and look and now when we click here it's checked Â
368.47 -> when we unclick there okay and now it works allÂ
we'd is that one line of code now would also be Â
375.22 -> nice the when we select a specific row thatÂ
are nice that that row shows up differently Â
380.98 -> than the other so the selected rows are differentÂ
and we can do that through conditional formatting Â
385.78 -> and to do that we'll highlight the whole tableÂ
but we're going to get a bug when we do that Â
390.01 -> and that's okay we'd get around it so here'sÂ
the bug and it's basically saying what happens Â
394.93 -> when we highlight more than what row and so weÂ
can see that and simply by writing some code if Â
402.93 -> the target count is greater than one then exitsÂ
up okay and that's all we need to do and we'll Â
412.98 -> reset that okay and then we go back into this andÂ
we now when we select highlight all the cell's we Â
421.5 -> don't get anybody okay so that that's somethingÂ
you want to do on selection change you'll need Â
426.96 -> that and so to do that let's go ahead into ourÂ
conditional formatting but before we do that we Â
434.1 -> want to copy this particular because that's theÂ
one we want to use for the conditional formula Â
441.54 -> so we'll copy that exit out of the cell highlightÂ
the entire table go to conditional formatting and Â
449.67 -> we're going to create a new rule and we'll use aÂ
formula to determine which cells to format okay Â
456.84 -> and that formula is going to be this basicallyÂ
we click inside here and then we're going to Â
461.79 -> click on e seven which is the first row okay andÂ
then equals and then quotation marks paste that Â
471.54 -> B in there okay and basically the only importantÂ
thing is we need to remove this dollar sign from Â
478.32 -> before the seven because we want this relativeÂ
to all the rows in the table we don't want to Â
483.42 -> write conditional formatting for each row soÂ
once we remove the dollar sign that can be used Â
488.46 -> for every row and now we want to format this roadÂ
differently so let's go ahead and format that and Â
493.71 -> maybe we want to add a fill and that we can addÂ
some fill effects so we can choose a color let's Â
500.67 -> say we choose this color and then maybe somethingÂ
a little bit slightly darker so we can go back in Â
507.57 -> and then go to more colors we can choose justÂ
a little bit of a darker color okay and we'll Â
512.94 -> select from light to dark click OK maybe we willÂ
want to bold the font so go into font click bold Â
519.96 -> to click OK and then we'll click OK alright andÂ
since we have the entire table highlighted it Â
526.89 -> knows what range so we go back into conditionalÂ
formatting and manage those rules okay you'll Â
532.59 -> see that it's already got the range key 17 toÂ
L 16 already highlighted so we are good with Â
538.33 -> that okay so now once we select a particularÂ
row automatically it changes all right so it's Â
547.57 -> really nice okay there are other things that weÂ
may want to add to this to make our table look Â
553.24 -> great we may want to add a select all ability andÂ
we can do that with a check box on the top just Â
559.81 -> like have you see in many applications so we canÂ
do that we'll type in select all and I've already Â
566.71 -> formatted it but you'll want to format it to theÂ
right justification here in the alignment so that Â
572.17 -> we have some space for a checkbox and we'll goÂ
ahead and put a checkbox in there back into the Â
578.56 -> developers tab and we're going to click on insertÂ
and then under form controls we're going to use Â
584.8 -> this checkbox here okay so we'll go ahead insertÂ
that checkbox and we'll just place it up here Â
590.32 -> for time Bing because we want to be able to seeÂ
it and we have our own text already so we don't Â
595.9 -> need the text there so we'll just remove thatÂ
and then we'll go ahead and size the checkbox Â
600.43 -> appropriately about like that and we'll place itÂ
inside the cell area there okay so now this is Â
609.94 -> it so now what we want to do is basically whenÂ
we select this we want all of the items in the Â
616.72 -> table to be selected and when we unselect it weÂ
want none of them to be selected so to do that Â
622.18 -> we're just going to write a little bit of code andÂ
the first thing we want to do is get the identity Â
627.79 -> of this check box and so to do that we'll justÂ
control click on it and we see that it's check Â
634.48 -> box seven here in the upper left so we're goingÂ
to copy and paste that name so we don't have to Â
640.03 -> rewrite it and then we're going to go into our VBAÂ
and from our VBA what we're going to do is we're Â
646.51 -> going to right-click anywhere and we're going toÂ
create a module ok so right click and create a Â
652.03 -> module and that's going to place the module downÂ
here and now we want to name that module in this Â
657.19 -> case we only have one module but when you do yourÂ
own projects should be creating many modules and Â
662.38 -> you want to name them so it's always good practiceÂ
give them a name we can do that by clicking on the Â
667.71 -> properties window here and that's going to bringÂ
up our module and it may be placed down here for Â
672.93 -> you or the right I place it on the right butÂ
wherever its place we can just route go ahead Â
676.65 -> and rename that and we will rename that to we'llÂ
call it check all max ok any name is finds one of Â
688.59 -> you know what it is and you have to make sure thatÂ
the name of this macro the name of the module is Â
694.62 -> not the same as the macro you want to make sureÂ
those are always separate so we're going to write Â
698.97 -> a macro now and that is going to be we'll callÂ
it check all and none because we're going to use Â
708.66 -> it for both checking all and checking none ok soÂ
and basically what we want to do is when we when Â
717.15 -> we check this we want to check them all when weÂ
check it again we want to check none and we can Â
722.04 -> do that with just a few lines of code alright soÂ
basically we want to know what is the state of Â
730.29 -> the checkbox right what is the state meaning is itÂ
checked is it unchecked and to get that state we Â
736.8 -> can write a little bit of temporary code and justÂ
to see what the status message box okay and then Â
743.52 -> we're going to start out with sheet 1 because weÂ
have to designate the sheet because we're inside Â
747.48 -> a module so Excel won't know what she were allÂ
unless we designated it sheet 1 ok check boxes
757.36 -> and then we need to know the name and remember weÂ
copy that so we can paste that checkbox seven okay Â
764.71 -> and we need to know the name of that so so whatÂ
is the value of that okay so we can click here Â
771.79 -> and then just click the value value and and thatÂ
is going to tell us what the value is when we run Â
777.82 -> that code so we can run that right now and if itÂ
goes that it's four one four six okay that's kind Â
783.49 -> of a weird code but that's for the uncheck stateÂ
okay and then what about for the check state so Â
788.53 -> let's go ahead and check it right and let's seeÂ
what that value is and that's going to be one Â
792.55 -> okay when it's checked it's one okay and there'sÂ
actually a third state two check boxes right and Â
798.85 -> that is let's go ahead and look at that format andÂ
we're going to form a right click format control Â
804.22 -> we'll call it mixed and so that's often usedÂ
when you know it's some check so I'm not checked Â
809.86 -> so that's it important so that looks like thatÂ
and now what is the number that excels science Â
814.6 -> to that so we can go ahead and run this code clickÂ
in there and click run and that's going to tell us Â
819.01 -> it's 2 so we have 2 + 1 + - 4 1 + 2 6 or somethingÂ
like that but we won't need to remember that ok so Â
827.14 -> all we need to do pretty much is change this -Â
if sheet 1 checkboxes value equals 1 right that Â
835.69 -> means if they're all check that if the value is 1Â
we want to check them all ok then ok then what do Â
844.12 -> we want to do well what we want to do is we wantÂ
to check them all ok we want to check them all Â
849.52 -> right and how to do that basically it give themÂ
all this value here this is the value we want to Â
856.06 -> give them - if that be so we're going to copyÂ
that ok and we have basically e7 through e 16Â Â
863.65 -> we're going to find all those the same value so weÂ
go back into our code here ok exit out of the cell Â
870.31 -> first back into the code and what we want to do isÂ
cheat sheet Y weekend actually since we're going Â
876.4 -> to be using sheet 1 let's go ahead and refer to itÂ
with sheet 1 ok like that and then we don't need Â
883.42 -> to then we don't need to continue to assign sheetsÂ
anymore so we bring this up between the width Â
890.48 -> then with and now we can get rid of the sheet 1 weÂ
don't need that anymore okay and then then what we Â
898.94 -> want we want dot range okay and it was e77 rightÂ
through right e 16 e 16 okay and we're going to Â
911.18 -> find those all the same values so we can simplyÂ
do that by equals and then put that little B in Â
916.7 -> there okay so if it's one we're going to put theÂ
B but what if it's not one else okay that means if Â
923.99 -> it's not one what should it do right and basicallyÂ
in that case if it's not one we're going to clear Â
928.61 -> it and remember the cleared is this one right hereÂ
right is this one right here is this double marks Â
934.34 -> here so we're going to copy that one right elseÂ
okay all we're going to simply do is dot range Â
941.45 -> again e seven through 16 value equals and thenÂ
quotation marks paste and then quotation marks Â
956.39 -> so that's it that's all the code and we need toÂ
write for this so basically what we're doing is Â
960.68 -> if the values if they're checked that we want themÂ
select if it's not check okay let's go ahead and Â
965.6 -> put that to work and basically the way we wantÂ
to do is we want to find that macro so let's Â
971.24 -> copy the name of the macro okay we're going toÂ
go back into Excel okay we're going to take this Â
978.59 -> check box this check let's right-click it we'reÂ
going to slide a macro to it okay we only have Â
984.11 -> one macro but we have and many many macros you'llÂ
want to so we're going to select the macro that we Â
989.33 -> created and that's it okay and now unselect itÂ
now when we click it click it once click it on Â
996.5 -> click it once pretty not great so now it worksÂ
really well okay and we can take this even a Â
1003.28 -> step further what about if it's mixed right inÂ
this case remember we had that third value that Â
1010.03 -> mixed value it would be nice if when we selectÂ
something right here it gives it that mixed value Â
1016.21 -> look which is kind of more accurate right so weÂ
can do that very easily with some additional code Â
1021.49 -> and we can go back into the developer and this isÂ
the one basically what we're saying is when when Â
1027.73 -> we make a selection in this area we also wantÂ
to add something in okay so basically what we Â
1037.9 -> want to do then is we want to make that checkboxÂ
that mix and remember that was the value two so Â
1043.93 -> all we need to do is call that checkbox so it'sÂ
checkboxes and if you remember the name checkbox Â
1051.7 -> seven I believe right let's check that out rightÂ
and check box seven and the value equals two all Â
1061.75 -> right and that's what it was right and I believeÂ
it was check box seven right check well there's Â
1066.88 -> a space between I put that space in thereÂ
otherwise we're going to be in trouble and Â
1071.53 -> so now when we collect here it gives it that mixedÂ
you see now when we didn't so when we select all Â
1080.95 -> select none and that is how you add selecting allÂ
checkboxes to a table it makes it really easy and Â
1087.85 -> really functional I hope you have enjoyed thisÂ
video if you do please like and share as much Â
1093.82 -> as you can it is always greatly appreciated andÂ
I'll be back next week with another video thank Â
1098.74 -> you very much please don't forget to subscribeÂ
to our youtube channel and select these send Â
1104.98 -> me all notifications options so you can get ourÂ
videos to you right away as soon as we create them
Source: https://www.youtube.com/watch?v=UFw9bAkoCEo
