You have a bunch of Forms Control Checkboxes in Excel and you need to know how many in a certain column are checked. The solution in today's episode is the wrong way to go. The better way is back in podcast 1871:
To register for the Oct 21, 2014 Miami Seminar: Bill "MrExcel" Jelen at IMA 8 CPE Seminar
Transcript of the video:
The MrExcel podcast is sponsored by MrExcel Power Excel Seminar.
Miami, Florida: October 21st 2014.
Montgomery, Alabama: November 7th 2014.
Check the URL down there in the description, would love to see you there!
Learn Excel from MrExcel podcast, episode 1929.
Count How Many Checkboxes Selected in a Range with VBA.
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
On this episode: how to count, how many Forms Control checkboxes are selected in a range.
I want to beg you, not to listen to this podcast, just stop right here, go listen to podcast 1871 instead.
A much better way to go, but this question was sent in by someone, who already has the checkboxes, doesn't want to go back and we're kind of stuck with this method.
So, what are these checkboxes?
These are Forms Control checkboxes.
We can turn them on and off with a single click.
How do we get these?
You have to go to the DEVELOPER tab, which is not on by default, so right click, Customize the Ribbon, and choose Developer.
Click OK.
Once you have the DEVELOPER tab, it's here under Insert, there's two kinds of Controls: Form Controls, ActiveX Controls.
We are using Form Controls today.
You can just add little check boxes like that.
Okay, now, in order to do this, to count how many items are checked within a certain range, we're going to have to use VBA.
You'll see up here, that this file is stored as xlsx and we cannot have macros in xlsx, so first thing: File, Save As, we’re saving the exact same space with the exact same name, but we have to change the file type.
The Save As, type from xlsx (the evil file, the file, the only file in the history of spreadsheets, that does not allow VBA) to xlsm, podcast 1929.
If you've never used macros before, we want Alt, T (T as in Tom), M (as in Michael), S (as in Sam), to get to the Security and change from this top setting to the second setting, that will allow macros to run.
All right, all of the pre-work, we press Alt+F11 to get to the VBA editor.
I have some personal macro workbook stuff here.
But we'll find our file, 1929, we will Insert a Module, and I'm going to paste in code that I have already written.
So we create a brand new function called “CountChecked”.
“CountChecked”, we're going to pass it a range.
“MyRange”, “Application.Volatile” – it's still not going to work at the end, but this will at least get it to calculate after an F9.
So CountChecked, this is the variable that we're going to increment.
So right now, it starts at zero ( = 0).
“For Each cb in MyRange.Parent.CheckBoxes” – that's right, there's no way to loop through all of the checkboxes in MyRange.
We have to go through all of the checkboxes on the entire spreadsheet.
How incredibly inefficient is this.
But that's what we have to do.
For each checkbox, when I look at the checkbox top left cell (“cb.TopLeftcell”) and see if it intersects MyRange (“If Not Intersect (MyRange,…“).
If it's nothing (“Is Nothing Then”), then we know that it's outside of the range.
Otherwise, check and see what the value is, if it's one (“If cb.Value = 1 Then”), then we do “CountChecked = CountChecked + 1”.
You think: hey, why don't we just do “CountChecked = CountChecked + cb.Value”?
Because when it's unchecked, it's like a minus 4096-something, completely unuseful.
So you have to have all of this code typed exactly correct.
And then we come back here and let's just come down to this spot.
=CountChecked(C1:F11) of this big range here.
And I'm going to count: one, two, three of them should be on, so we get 3.
If I check something else, now here is where this whole thing falls apart.
The calculation chain in Excel doesn't know to recalculate the cell in response to me checking this box.
So I check the box, see, it does not change.
I have to go either press F9 or back here on the FORMULAS tab, I have to press Calculate Now and that will get it to update.
This is horrible.
The method in podcast 1871 – yeah, I understand, it's more work, but it will return the right results instead of these evil, evil, wrong results.
So people check a whole bunch of boxes, they have to press F9 in order to get the results to work.
I don't recommend this.
This is… this is just dangerous, to have the wrong result on there, but if you're already so far down the path, where you have 10 million checkboxes already installed, then maybe… Maybe this method will work.
I want to thank you for stopping by, we'll see you next time for another netcast – MrExcel.
Miami, Florida: October 21st 2014.
Montgomery, Alabama: November 7th 2014.
Check the URL down there in the description, would love to see you there!
Learn Excel from MrExcel podcast, episode 1929.
Count How Many Checkboxes Selected in a Range with VBA.
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
On this episode: how to count, how many Forms Control checkboxes are selected in a range.
I want to beg you, not to listen to this podcast, just stop right here, go listen to podcast 1871 instead.
A much better way to go, but this question was sent in by someone, who already has the checkboxes, doesn't want to go back and we're kind of stuck with this method.
So, what are these checkboxes?
These are Forms Control checkboxes.
We can turn them on and off with a single click.
How do we get these?
You have to go to the DEVELOPER tab, which is not on by default, so right click, Customize the Ribbon, and choose Developer.
Click OK.
Once you have the DEVELOPER tab, it's here under Insert, there's two kinds of Controls: Form Controls, ActiveX Controls.
We are using Form Controls today.
You can just add little check boxes like that.
Okay, now, in order to do this, to count how many items are checked within a certain range, we're going to have to use VBA.
You'll see up here, that this file is stored as xlsx and we cannot have macros in xlsx, so first thing: File, Save As, we’re saving the exact same space with the exact same name, but we have to change the file type.
The Save As, type from xlsx (the evil file, the file, the only file in the history of spreadsheets, that does not allow VBA) to xlsm, podcast 1929.
If you've never used macros before, we want Alt, T (T as in Tom), M (as in Michael), S (as in Sam), to get to the Security and change from this top setting to the second setting, that will allow macros to run.
All right, all of the pre-work, we press Alt+F11 to get to the VBA editor.
I have some personal macro workbook stuff here.
But we'll find our file, 1929, we will Insert a Module, and I'm going to paste in code that I have already written.
So we create a brand new function called “CountChecked”.
“CountChecked”, we're going to pass it a range.
“MyRange”, “Application.Volatile” – it's still not going to work at the end, but this will at least get it to calculate after an F9.
So CountChecked, this is the variable that we're going to increment.
So right now, it starts at zero ( = 0).
“For Each cb in MyRange.Parent.CheckBoxes” – that's right, there's no way to loop through all of the checkboxes in MyRange.
We have to go through all of the checkboxes on the entire spreadsheet.
How incredibly inefficient is this.
But that's what we have to do.
For each checkbox, when I look at the checkbox top left cell (“cb.TopLeftcell”) and see if it intersects MyRange (“If Not Intersect (MyRange,…“).
If it's nothing (“Is Nothing Then”), then we know that it's outside of the range.
Otherwise, check and see what the value is, if it's one (“If cb.Value = 1 Then”), then we do “CountChecked = CountChecked + 1”.
You think: hey, why don't we just do “CountChecked = CountChecked + cb.Value”?
Because when it's unchecked, it's like a minus 4096-something, completely unuseful.
So you have to have all of this code typed exactly correct.
And then we come back here and let's just come down to this spot.
=CountChecked(C1:F11) of this big range here.
And I'm going to count: one, two, three of them should be on, so we get 3.
If I check something else, now here is where this whole thing falls apart.
The calculation chain in Excel doesn't know to recalculate the cell in response to me checking this box.
So I check the box, see, it does not change.
I have to go either press F9 or back here on the FORMULAS tab, I have to press Calculate Now and that will get it to update.
This is horrible.
The method in podcast 1871 – yeah, I understand, it's more work, but it will return the right results instead of these evil, evil, wrong results.
So people check a whole bunch of boxes, they have to press F9 in order to get the results to work.
I don't recommend this.
This is… this is just dangerous, to have the wrong result on there, but if you're already so far down the path, where you have 10 million checkboxes already installed, then maybe… Maybe this method will work.
I want to thank you for stopping by, we'll see you next time for another netcast – MrExcel.
