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.

"Eric" wrote:

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" wrote:

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" wrote:

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

"Jacob Skaria" wrote:

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