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 VBEToolsReferences


If this post helps click Yes
---------------
Jacob Skaria


"Eric" wrote:

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


"Jacob Skaria" wrote:

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 ViewImmediate 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 VBEToolsReferences

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
---------------
Jacob Skaria


"Eric" wrote:

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