How to count the number of selected combobox within Excel?

[Eric]

Does anyone have any suggestions on how to count the number of combobox
within Excel?
I have created a few square click box under form tool, and I would like to
count how many numbers of box has been selected, does anyone have any
suggestions on how to do it?
Thanks in advance for any suggestions
Eric

 

[Jacob Skaria]

Hi Eric

If you have only combo boxes in your sheet try the below line of code in
VBE. If you are new to VBE press Alt+F11 from workbook to launch VBE. From
menu View>Immediate Window. Activate the sheet with the controls and in
immediate window type which will return the number of controls

?ActiveSheet.OLEObjects.count

If you have other controls try the below macro. To run a macro set the
Security level to low/medium in (Tools|Macro|Security). From workbook launch
VBE using short-key Alt+F11. From menu 'Insert' a module and paste the below
code. Save. Get back to Workbook. Run macro from Tools|Macro|Run <selected
macro()>

Reference Microsoft Forms 2.0 Object Library from VBE>Tools>References

Sub CountCombo()
Dim intTemp
For Each objtemp In ActiveSheet.OLEObjects
If TypeOf objtemp.Object Is MSForms.ComboBox Then
intCount = intCount + 1
End If
Next
MsgBox "Number of Combo boxes :" & intCount
End Sub




If this post helps click Yes

 

[Eric]

Thank you very much for suggestions
Would it be possible to check whether the combobox is selected or not? so I
can use IF statement for determining the number of selected combobox.
IF combobox1 is selected, then 1, else 0 +
IF combobox2 is selected, then 1, else 0 + ...

Do you have any suggestions?
Thanks in advance for any suggestions
Eric

 

[JLatham]

I think he just wants to count the ones 'selected/checked' - and I have to
run off to the office right now. Needs one more test inside the If..End If ??

 

[Jacob Skaria]

Hi Eric

If you are looking for the number of combos with a value/text in it try the
macro

Sub ComboCountWithData()
Dim intCount As Integer
For Each objTemp In ActiveSheet.OLEObjects
If TypeOf objTemp.Object Is MSForms.ComboBox Then
If objTemp.Object.Value <> "" Then
intCount = intCount + 1
End If
End If
Next
MsgBox intCount
End Sub

OR if you are looking for number of combo boxes in a selection range; try

Sub CombosInSelectionRange()
Dim objTemp As Object, intTemp As Integer
For Each objTemp In ActiveSheet.OLEObjects
If TypeOf objTemp.Object Is MSForms.ComboBox Then
If Not Intersect(objTemp.TopLeftCell, Selection) Is Nothing Then
intCount = intCount + 1
End If
End If
Next
MsgBox intCount
End Sub

PS: In both cases you will have to refer 'Microsoft Forms 2.0 Object
Library' from VBE>Tools>References


If this post helps click Yes

 

[Eric]

Thank everyone very much for suggestions
Would it be possible not using VBA coding to count the number of selected box?
If I can return the number of selected box into cell A1 with general coding,
then it will be sample and perfect.
Do you have any suggestions?
Thanks in advance for any suggestions
Eric

 

[JLatham]

There's no worksheet function that can deal with controls like that. Any
solution would be VBA based. You could turn the Sub into a Function and use
it as a User Defined Function (UDF).

Public Function ComboCountWithData()
Dim intCount As Integer
intCount=0
For Each objTemp In ActiveSheet.OLEObjects
If TypeOf objTemp.Object Is MSForms.ComboBox Then
If objTemp.Object.Value <> "" Then
intCount = intCount + 1
End If
End If
Next
ComboCountWithData = intCount
End Function

Then you call it just like a regular formula in the worksheet as
=ComboCountWithData()

I haven't tested this, and I'm not sure how it will work with changing
status of the combo boxes.

 

[Eric]

Thank everyone very much for suggestions
Could you please tell me how to call this function?
When the 4 comboboxs are selected, and insert ComboCountWithData() into cell
A1, it returns 0. Could you please tell me how to call this function?
Thank everyone very much for any suggestions
Eric

 

[JLatham]

Eric,
The code needs to be entered into your workbook in a regular code module,
just like you did the Sub. Then you treat it just like any Excel function by
typing an equal sign followed by the name of the function and parameter
(which is not anything in this case) into a cell, as
=ComboCountWithData()

I haven't tested it, I will right now, and if I discover I've made an error
somehow, I'll post back.

 

[JLatham]

Ok, Eric, here goes... first I must set the conditions under which this will
work.

The drop down boxes must be one from the Excel "Control Toolbox", not from
the "Forms" tools.

To work properly the drop down boxes must have their Linked Cell set to a
cell on the worksheet. Right click on each one and choose [Properties] to
set the source of its list (ListFillRange property) and the Linked Cell.

Here is the revised code that works under these restrictions:

Public Function ComboCountWithData() As Integer
Dim intCount As Integer
Dim objTemp As Object
Application.Volatile
intCount = 0
For Each objTemp In ActiveSheet.OLEObjects
If TypeOf objTemp.Object Is MSForms.ComboBox Then
If objTemp.Object.Value <> "" Then
intCount = intCount + 1
End If
End If
Next
ComboCountWithData = intCount
End Function

To put the code into your workbook, open it up. Press [Alt]+[F11] to open
the VB editor. Since you probably already have a module in it with the older
code, just choose that module and replace the code in it with the code above.


Each

 

[Eric]

Thank everyone very much for suggestions

Could you please tell me what to set for the Linked Cell property?
Should I type ListFullRange for Linked Cell property?

I did that, but it does not work for function ComboCountWithData(), which
still return 0, as the combobox is selected.
Do you have any suggestions?
Thank you very much for any suggestions
Eric

Ok, Eric, here goes... first I must set the conditions under which this will
work.

The drop down boxes must be one from the Excel "Control Toolbox", not from
the "Forms" tools.

To work properly the drop down boxes must have their Linked Cell set to a
cell on the worksheet. Right click on each one and choose [Properties] to
set the source of its list (ListFillRange property) and the Linked Cell.

Here is the revised code that works under these restrictions:

Public Function ComboCountWithData() As Integer
Dim intCount As Integer
Dim objTemp As Object
Application.Volatile
intCount = 0
For Each objTemp In ActiveSheet.OLEObjects
If TypeOf objTemp.Object Is MSForms.ComboBox Then
If objTemp.Object.Value <> "" Then
intCount = intCount + 1
End If
End If
Next
ComboCountWithData = intCount
End Function

To put the code into your workbook, open it up. Press [Alt]+[F11] to open
the VB editor. Since you probably already have a module in it with the older
code, just choose that module and replace the code in it with the code above.


Each

Thank everyone very much for suggestions
Could you please tell me how to call this function?
When the 4 comboboxs are selected, and insert ComboCountWithData() into cell
A1, it returns 0. Could you please tell me how to call this function?
Thank everyone very much for any suggestions
Eric