PDA

View Full Version : How to get variables numbers from array of numbers



omp001
11-21-2011, 06:51 PM
With the code below I'm trying to loop through some check boxes and count how many of them are marked.
If I give a value to 'i', e.g. 15, then the code works but if I try to get 'i' from array that give me an error cause LBound = 0.
How can I do to get numbers from the array to make 'CheckBox3', CheckBox4', and so on ?
Thanks in advance.
Sub CountChckBoxesOn()
Dim i, k As Long, Cx As Variant
k = 0
Cx = Array(3, 4, 15, 18)
For i = LBound(Cx) To UBound(Cx)
With ActiveSheet.CheckBoxes("CheckBox" & i)
If .Value = xlOn Then
k = k + 1
End If
End With
Next i
MsgBox k
End Sub

GTO
11-21-2011, 06:53 PM
Greetings Osvaldo,

Not tested but I believe you want:
Sub CountChckBoxesOn()
Dim i, k As Long, Cx As Variant
k = 0
Cx = Array(3, 4, 15, 18)
For i = LBound(Cx) To UBound(Cx)
With ActiveSheet.CheckBoxes("CheckBox" & Cx(i))
If .Value = xlOn Then
k = k + 1
End If
End With
Next i
MsgBox k
End Sub

omp001
11-21-2011, 07:16 PM
Howdy, GTO.
Thanks for your time.

It's giving me the same error:
'runtime error 1004'
'not possible to obtain ownership of the CheckBox class Worksheet'
on this line: 'With ActiveSheet.CheckBoxes("CheckBox" & Cx(i))'
and LBound(Cx) =0
and To UBound(Cx)=3

thanks again

GTO
11-21-2011, 07:18 PM
Can you attach a small example workbook? Just with the checkboxes as created and named in the real project.

omp001
11-21-2011, 07:35 PM
Here we go GTO.
Thanks.

GTO
11-21-2011, 07:56 PM
Your checkboxes are named like 'Check Box 1', etc. You need like:
With ActiveSheet.CheckBoxes("Check Box " & Cx(i))
To see the checkbox's name, right click on it and look in the names box.

omp001
11-21-2011, 08:16 PM
Hi GTO. Thanks for your assitance and your time.
The error is appearing because the code is not picking up the value contained in the array as a numeric value. If you run the code with F8, arriving on the line 'With ActiveSheet.CheckBoxes ("Check Box" & Cx (i))' you can see that LBond = 0, this value will cause an error because I don't have 'CheckBox0'.
The other hand, if I assign a value to 'i', disabling the array, it works properly.
So I think the point is how to get number value from array and not number index, as it's getting now. Maybe the array need to be made in a different way.
Thanks so much.

GTO
11-21-2011, 08:40 PM
Hi GTO. Thanks for your assitance and your time.
The error is appearing because the code is not picking up the value contained in the array as a numeric value. If you run the code with F8, arriving on the line 'With ActiveSheet.CheckBoxes ("Check Box" & Cx (i))' you can see that LBond = 0, this value will cause an error because I don't have 'CheckBox0'.

Hi Osvaldo,

I am afraid that the above is incorrect. I should mention that you may have an oversight in your array(), as at least the check boxes on the sample sheet are named Check Box 1, 4 and 6. Thus - with the sample sheet you provided, step thru this code:

Sub CountChckBoxesOn()
Dim i, k As Long, Cx As Variant
k = 0
Cx = Array(1, 4, 6)
For i = LBound(Cx) To UBound(Cx)
With ActiveSheet.CheckBoxes("Check Box " & Cx(i))
If .Value = xlOn Then
k = k + 1
End If
End With
Next i
MsgBox k
End Sub

When you get to the With ActiveSheet... line, hover over the Cx portion. You will see that the first element in the array correctly returns 1.

Does that make sense?

Mark

mikerickson
11-21-2011, 09:11 PM
Try With ActiveSheet.Shapes("CheckBox" & Cx(i)).ControlFormat

omp001
11-22-2011, 03:55 AM
It's working. Thank you for your help.
comments:
1. numbers of boxes - you are right, before post the sample I changed the numbers in array to show that there was no logic in the numbering sequence
2. names - maybe as you opened the sample file the box names were shown in English, but in my version the names is as they are in the code
3. values ​​for 'Cx (i)' - for me this portion shows 0, 1 and 2, (index number I guess) as the loop progresses, and not the value in the array
But the important thing is that it's working fine and I really appreciate your help.
Nice day.

PS:
Hi Mike.
I'll give it a try.
Thanks a lot.