How To EASILY Add Checkboxes To ANY Microsoft Excel Table

hello this is Randy with Excel for freelancers  and today we're going to be discussing how to   create these amazing check boxes in  your Excel tables so please join me alrighty let's get started okay so basically the  idea is that we're going to put check boxes in   this column when we select it we'll check the box  on or off and to get those check boxes what we can   do is we can go to the insert ok tab hold Excel  and then go to symbol now once we click symbol   we'll see a lot of different fonts and a lot of  different shapes we'll use the first two will   use the unchecked and the check and the check  will look like this we'll go ahead and insert   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   wing-dings font ok so we want to make sure that we  want that wing-dings font and then we'll go ahead   and on the upper one will enter the unchecked  one which is this symbol and then we're going   to use the unchecked one okay alright so we've  inserted the two of those and basically the idea   is we're going to use these shapes for either  check or uncheck and so what we want to do is   we want to make sure that all of the cells here  are wing-dings font okay so that can properly so   we'll just go ahead and highlight those and make  sure that we selected wing-dings and check that   okay so now that every cell is the correct font in  the column that we want to put okay and the idea   is when we select this one we want it to uncheck  when we select this one one of the checks we   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   ways once again we can do alt f11 okay that will  launch our VB or we can go into the developers   tab here and if you don't have this developers tab  simply go into file options okay customized ribbon   you can see the developers tab right here okay so  make sure this is selected and once we're into the   Visual Basic we want to go into sheet one because  this is the sheet that we're going to be working   off of sheet one okay so we want to make sure  that the work we're doing and the idea is that   when we select a cell here we want some action so  we're going to be using the selection change okay   so in VBA on cheat one you're going to go into  the worksheet click there and selection change   came up automatically but you could do it for any  other one okay so selection change all right and   now we're going to say hey if we click anywhere in  this range here we want some action to take place   so that range is e seven through sixteen okay so  we're going to go ahead and write some code if not   intersect target range okay and that range was e  seven correct G seven through e 16 okay through   e 16 okay and parenthesis and then go twice there  and then is nothing then and basically what that   says is those are like a double negative here not  and nothing so basically that they'll cancel each   other out and it means if there is some selection  in this range do something okay now what do we   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   change it to selected if it's selected we want  to check if it's checked we want to uncheck so   basically let's go ahead and write some code now  okay but to do that we've also got to know what   the values are that we need to change okay what  the actual values are so we'll go ahead and take   a look at this and if we look here that checkbox  that values that be looking thing okay so we're   going to copy at control-c and then exit out of  the cell in order to go back into the VBA okay   so basically what we want to say is if the target  value is equal print B okay close quotation marks   then okay we want the target value to change  target value to change - what - change - that   uncheck marks equals okay we're going to ahead and  pause the code just first second so it doesn't run   while we're copying and pasting this so that  is looks like this double mark here so we'll   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   do quotation mark cop paste that in and then in  the quotation mark so basically we're saying if   target value is B then change it okay but if  it's not be right if it's not the check mark   that we need to change it to the check mark so  we're going to write some code there also else   meaning if it's if it's not this that we need to  change it to this okay so that we only have two   options else then all we need to do is copy that  right and change it to that okay so now so now   we have this and now if we see how that works now  we can reset the code again okay simply by going   in and resetting the code going out moving in our  and look and now when we click here it's checked   when we unclick there okay and now it works all  we'd is that one line of code now would also be   nice the when we select a specific row that  are nice that that row shows up differently   than the other so the selected rows are different  and we can do that through conditional formatting   and to do that we'll highlight the whole table  but we're going to get a bug when we do that   and that's okay we'd get around it so here's  the bug and it's basically saying what happens   when we highlight more than what row and so we  can see that and simply by writing some code if   the target count is greater than one then exits  up okay and that's all we need to do and we'll   reset that okay and then we go back into this and  we now when we select highlight all the cell's we   don't get anybody okay so that that's something  you want to do on selection change you'll need   that and so to do that let's go ahead into our  conditional formatting but before we do that we   want to copy this particular because that's the  one we want to use for the conditional formula   so we'll copy that exit out of the cell highlight  the entire table go to conditional formatting and   we're going to create a new rule and we'll use a  formula to determine which cells to format okay   and that formula is going to be this basically  we click inside here and then we're going to   click on e seven which is the first row okay and  then equals and then quotation marks paste that   B in there okay and basically the only important  thing is we need to remove this dollar sign from   before the seven because we want this relative  to all the rows in the table we don't want to   write conditional formatting for each row so  once we remove the dollar sign that can be used   for every row and now we want to format this road  differently so let's go ahead and format that and   maybe we want to add a fill and that we can add  some fill effects so we can choose a color let's   say we choose this color and then maybe something  a little bit slightly darker so we can go back in   and then go to more colors we can choose just  a little bit of a darker color okay and we'll   select from light to dark click OK maybe we will  want to bold the font so go into font click bold   to click OK and then we'll click OK alright and  since we have the entire table highlighted it   knows what range so we go back into conditional  formatting and manage those rules okay you'll   see that it's already got the range key 17 to  L 16 already highlighted so we are good with   that okay so now once we select a particular  row automatically it changes all right so it's   really nice okay there are other things that we  may want to add to this to make our table look   great we may want to add a select all ability and  we can do that with a check box on the top just   like have you see in many applications so we can  do that we'll type in select all and I've already   formatted it but you'll want to format it to the  right justification here in the alignment so that   we have some space for a checkbox and we'll go  ahead and put a checkbox in there back into the   developers tab and we're going to click on insert  and then under form controls we're going to use   this checkbox here okay so we'll go ahead insert  that checkbox and we'll just place it up here   for time Bing because we want to be able to see  it and we have our own text already so we don't   need the text there so we'll just remove that  and then we'll go ahead and size the checkbox   appropriately about like that and we'll place it  inside the cell area there okay so now this is   it so now what we want to do is basically when  we select this we want all of the items in the   table to be selected and when we unselect it we  want none of them to be selected so to do that   we're just going to write a little bit of code and  the first thing we want to do is get the identity   of this check box and so to do that we'll just  control click on it and we see that it's check   box seven here in the upper left so we're going  to copy and paste that name so we don't have to   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   going to right-click anywhere and we're going to  create a module ok so right click and create a   module and that's going to place the module down  here and now we want to name that module in this   case we only have one module but when you do your  own projects should be creating many modules and   you want to name them so it's always good practice  give them a name we can do that by clicking on the   properties window here and that's going to bring  up our module and it may be placed down here for   you or the right I place it on the right but  wherever its place we can just route go ahead   and rename that and we will rename that to we'll  call it check all max ok any name is finds one of   you know what it is and you have to make sure that  the name of this macro the name of the module is   not the same as the macro you want to make sure  those are always separate so we're going to write   a macro now and that is going to be we'll call  it check all and none because we're going to use   it for both checking all and checking none ok so  and basically what we want to do is when we when   we check this we want to check them all when we  check it again we want to check none and we can   do that with just a few lines of code alright so  basically we want to know what is the state of   the checkbox right what is the state meaning is it  checked is it unchecked and to get that state we   can write a little bit of temporary code and just  to see what the status message box okay and then   we're going to start out with sheet 1 because we  have to designate the sheet because we're inside   a module so Excel won't know what she were all  unless we designated it sheet 1 ok check boxes and then we need to know the name and remember we  copy that so we can paste that checkbox seven okay   and we need to know the name of that so so what  is the value of that okay so we can click here   and then just click the value value and and that  is going to tell us what the value is when we run   that code so we can run that right now and if it  goes that it's four one four six okay that's kind   of a weird code but that's for the uncheck state  okay and then what about for the check state so   let's go ahead and check it right and let's see  what that value is and that's going to be one   okay when it's checked it's one okay and there's  actually a third state two check boxes right and   that is let's go ahead and look at that format and  we're going to form a right click format control   we'll call it mixed and so that's often used  when you know it's some check so I'm not checked   so that's it important so that looks like that  and now what is the number that excels science   to that so we can go ahead and run this code click  in there and click run and that's going to tell us   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   all we need to do pretty much is change this -  if sheet 1 checkboxes value equals 1 right that   means if they're all check that if the value is 1  we want to check them all ok then ok then what do   we want to do well what we want to do is we want  to check them all ok we want to check them all   right and how to do that basically it give them  all this value here this is the value we want to   give them - if that be so we're going to copy  that ok and we have basically e7 through e 16   we're going to find all those the same value so we  go back into our code here ok exit out of the cell   first back into the code and what we want to do is  cheat sheet Y weekend actually since we're going   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   to then we don't need to continue to assign sheets  anymore so we bring this up between the width   then with and now we can get rid of the sheet 1 we  don't need that anymore okay and then then what we   want we want dot range okay and it was e77 right  through right e 16 e 16 okay and we're going to   find those all the same values so we can simply  do that by equals and then put that little B in   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   it's not one what should it do right and basically  in that case if it's not one we're going to clear   it and remember the cleared is this one right here  right is this one right here is this double marks   here so we're going to copy that one right else  okay all we're going to simply do is dot range   again e seven through 16 value equals and then  quotation marks paste and then quotation marks   so that's it that's all the code and we need to  write for this so basically what we're doing is   if the values if they're checked that we want them  select if it's not check okay let's go ahead and   put that to work and basically the way we want  to do is we want to find that macro so let's   copy the name of the macro okay we're going to  go back into Excel okay we're going to take this   check box this check let's right-click it we're  going to slide a macro to it okay we only have   one macro but we have and many many macros you'll  want to so we're going to select the macro that we   created and that's it okay and now unselect it  now when we click it click it once click it on   click it once pretty not great so now it works  really well okay and we can take this even a   step further what about if it's mixed right in  this case remember we had that third value that   mixed value it would be nice if when we select  something right here it gives it that mixed value   look which is kind of more accurate right so we  can do that very easily with some additional code   and we can go back into the developer and this is  the one basically what we're saying is when when   we make a selection in this area we also want  to add something in okay so basically what we   want to do then is we want to make that checkbox  that mix and remember that was the value two so   all we need to do is call that checkbox so it's  checkboxes and if you remember the name checkbox   seven I believe right let's check that out right  and check box seven and the value equals two all   right and that's what it was right and I believe  it was check box seven right check well there's   a space between I put that space in there  otherwise we're going to be in trouble and   so now when we collect here it gives it that mixed  you see now when we didn't so when we select all   select none and that is how you add selecting all  checkboxes to a table it makes it really easy and   really functional I hope you have enjoyed this  video if you do please like and share as much   as you can it is always greatly appreciated and  I'll be back next week with another video thank   you very much please don't forget to subscribe  to our youtube channel and select these send   me all notifications options so you can get our  videos to you right away as soon as we create them

Channel: Excel For Freelancers

Views: 106,781

Keywords: Microsoft Excel Checkboxes, Excel Clickable Checkboxes, Excel Checkboxes, Checkboxes in Excel, Check Marks in Excel, Adding Checkboxes to Excel, Table of Checkboxes, Select Checkboxes in Excel, Select Multiple Checkboxes, Excel Unlimited Check Boxes, Excel Unlimited Checkboxes, Checking Boxes in Excel, Excel Check boxes, Checking multiple boxes in Excel, Highlight Checked Boxes In Excel

Id: UFw9bAkoCEo

Length: 18min 40sec (1120 seconds)

Published: Wed Aug 09 2017