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.
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.
Posts Related:
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.