Using Array to disable checkbox in worksheet

[Ken]

Hi,
Below is the array using so that I can disable 5 checkbox have in worksheet.

It does not take value from the array assigned when it comes to steps on FOR....Next
e.g. ar_cbox(i) shows 1 instead of the value cbox_dept1

[red]The below code is to deactivate every checkbox and another FOR next to assign null value.
Both doesn't work ? Any advise/suggestion? [/red]

Sub DisableCheckBox
    Dim ar_cbox(5), ar_string(5) As String

    ar_cbox(1) = "cBox_dept1"
    ar_cbox(2) = "cBox_dept2"
    ar_cbox(3) = "cBox_dept3"
    ar_cbox(4) = "cBox_dept4"
    ar_cbox(5) = "cBox_dept5"

    ar_string(1) = "user1"
    ar_string(2) = "user2"
    ar_string(3) = "user3"
    ar_string(4) = "user4"
    ar_string(5) = "user5"

    For i = 1 To 5 Step 1
        Worksheets("Sheet1").ar_cbox(i).Value = False
    Next i
    
    i = 1
    For i = 1 To 5 Step 1
        ar_string(i).value = ""
    Next i
End sub

 

[SkipVought]

Hi,

You have not explained what kind of checkbox: Forms or Control Toolbox???


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Andrzejek]

You do realize that in the statement

Dim [red]ar_cbox(5)[/red], ar_string(5) As String

the part in red is an array of Variants, and not Strings or CheckBoxes.

And since they are Variants, you can assign whatever you want to them, a string ("cBox_dept3") or a boolean (True or False)

Have fun.

---- Andy

 

[xlbo]

you cannot use a string variable to reference an object in the way you are attempting to do

You have a string in an array e.g. "cBox_dept1"

You must refer to objects like

ParentObject.ChildObject.Value = x

Your code is attempting to do :

ParentObject.String.Value = x

Depending on your type of checkbox (as per Skips question), you may be able to use:

Worksheets("Sheet1").Checkboxes(ar_cbox(i)).Value = False

You may also want to look at your array base options. Unless you have set elsewhere Option Base 1, your base array element is 0 not 1 so you would need to use for i = 0 to 4 rather than for i = 1 to 5

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[fumei]

Also, what is the point of declaring values into an array:

    ar_string(1) = "user1"
    ar_string(2) = "user2"
    ar_string(3) = "user3"
    ar_string(4) = "user4"
    ar_string(5) = "user5"

and then immediately make them ""?

    For i = 1 To 5 Step 1
        ar_string(i).value = ""
    Next i

Further, none of the code "disables" the checkboxes.

Gerry

 

[Ken]

Thanks to All,

Answers to most question
1) You have not explained what kind of checkbox: Forms or Control Toolbox???

check box is from control toolbox

2) Variant problem

That is corrected and defined string separately
now it is
Dim ar_cbox(5)
dim ar_string(5) As String
makes no difference whether it is variant or string.

3) On using below code

Worksheets("Sheet1").Checkboxes(ar_cbox(i)).Value = False

gives error

run-time error '1004'
application-defined or object-defined error


Conclusion, I had to manually enter code 5 times as below for it to work

Worksheets("Sheet1").cBox_dept1.Value = False
Worksheets("Sheet1").cBox_dept2.Value = False
....
Worksheets("Sheet1").cBox_dept5.Value = False

All I wanted was to disable the checkbox from array for ease of coding and be object oriented

TechIT
Thanks,

 

[SkipVought]

Try something like this

Sub test()
    Dim shp As Shape
    On Error Resume Next
    For Each shp In ActiveSheet.Shapes
        If shp.OLEFormat.progID = "Forms.CheckBox.1" Then
            If Err.Number = 0 Then
                shp.OLEFormat.Object.Object.Value = False
            Else
                Err.Clear
            End If
        End If
    Next
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}