| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to count the number of selected combobox within Excel?
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to count the number of selected combobox within Excel?
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to count the number of selected combobox within Excel?
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to count the number of selected combobox within Excel?
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" 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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to count the number of selected combobox within Excel?
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to count the number of selected combobox within Excel?
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to count the number of selected combobox within Excel?
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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to count the number of selected combobox within Excel?
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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to count the number of selected combobox within Excel?
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 |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to count the number of selected combobox within Excel?
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" 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 |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to count the number of selected combobox within Excel?
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 "JLatham" wrote: 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" 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I get excel to display the number of columns selected? | Excel Discussion (Misc queries) | |||
| show number of selected records in Excel | Excel Discussion (Misc queries) | |||
| Print only selected data from ComboBox? | Excel Discussion (Misc queries) | |||
| Combobox Number Formats | Excel Worksheet Functions | |||
| Number format for Combobox | Excel Discussion (Misc queries) | |||
Linear Mode
