Control Arrays; Making it work on my form

[kazlady]

Okay, so I got this from
http://www.mrexcel.com/forum/showthread.php?t=3525

The page is addressing exactly what I want to do. I want to have my code check to see what the value of a checkBox is and run the corresponding click event code if it's true. These are Activex Control Boxes. My code doesn't work. I get several different errors. I have left all of the checkboxes at the default name. This is the last key to my project and I can show this tool to my manager. This is one of the major keys though. Any help would be appreciated. Sometimes I get a type mismatch error, sometimes I get an object or with block variable... error, sometimes I get an invalid character error. I have tried it a few different ways. I can't seem to make it work. Any help would be appreciated.

Private Sub OptionButton8_Click()
Dim i As Integer
Dim CheckBox(1 To 26) As Control
Set CheckBox(1) = CheckBox1
Set CheckBox(2) = CheckBox2
Set CheckBox(3) = CheckBox3
Set CheckBox(4) = CheckBox4
Set CheckBox(5) = CheckBox5
Set CheckBox(6) = CheckBox6
Set CheckBox(7) = CheckBox7
Set CheckBox(8) = CheckBox8
Set CheckBox(9) = CheckBox9
Set CheckBox(10) = CheckBox10
Set CheckBox(11) = CheckBox11
Set CheckBox(12) = CheckBox12
Set CheckBox(13) = CheckBox13
Set CheckBox(14) = CheckBox14
Set CheckBox(15) = CheckBox15
Set CheckBox(16) = CheckBox16
Set CheckBox(17) = CheckBox17
Set CheckBox(18) = CheckBox18
Set CheckBox(19) = CheckBox19
Set CheckBox(20) = CheckBox20
Set CheckBox(21) = CheckBox21
Set CheckBox(22) = CheckBox22
Set CheckBox(23) = CheckBox23
Set CheckBox(24) = CheckBox24
Set CheckBox(25) = CheckBox25
Set CheckBox(26) = CheckBox26
For i = 1 To 26
If CheckBox(i).Value = True Then
Call CheckBox(i)_Click
Else: End If
Next
End Sub

 

[Colin Legg]

The control arrays we use in VB6 are not supported in VBA. As described on that other thread, a workaround is to use a collection of custom class objects.

Here's a step by step example for you to try in a new project.

Add a userform to the project and add some checkboxes to it.

Add the following code to the userform class module:

[COLOR=blue]Private[/COLOR] [COLOR=blue]Sub[/COLOR] UserForm_Initialize()
 
    [COLOR=blue]Const[/COLOR] sTYPENAME [COLOR=blue]As[/COLOR] [COLOR=blue]String[/COLOR] = "CheckBox"
 
    [COLOR=blue]Static[/COLOR] colcCheckboxes [COLOR=blue]As[/COLOR] Collection
    [COLOR=blue]Dim[/COLOR] ctl [COLOR=blue]As[/COLOR] MSForms.Control
    [COLOR=blue]Dim[/COLOR] cCheckBox [COLOR=blue]As[/COLOR] clsCheckbox
 
    [COLOR=blue]For[/COLOR] [COLOR=blue]Each[/COLOR] ctl [COLOR=blue]In[/COLOR] Controls
        [COLOR=blue]If[/COLOR] [COLOR=blue]TypeName[/COLOR](ctl) = sTYPENAME [COLOR=blue]Then[/COLOR]
            [COLOR=blue]Set[/COLOR] cCheckBox = [COLOR=blue]New[/COLOR] clsCheckbox
            [COLOR=blue]Set[/COLOR] cCheckBox.CheckBox = ctl
 
            [COLOR=blue]If[/COLOR] colcCheckboxes [COLOR=blue]Is[/COLOR] [COLOR=blue]Nothing[/COLOR] [COLOR=blue]Then[/COLOR] [COLOR=blue]Set[/COLOR] colcCheckboxes = [COLOR=blue]New[/COLOR] Collection
            colcCheckboxes.Add cCheckBox
        [COLOR=blue]End[/COLOR] [COLOR=blue]If[/COLOR]
    [COLOR=blue]Next[/COLOR] ctl
[COLOR=blue]End[/COLOR] [COLOR=blue]Sub[/COLOR]

Now add a custom class module to the project (via Insert | Class Module) and rename it to clsCheckbox. Copy and paste in this code:

[COLOR=blue]Private[/COLOR] [COLOR=blue]WithEvents[/COLOR] fCheckbox [COLOR=blue]As[/COLOR] MSForms.CheckBox
 
[COLOR=blue]Public[/COLOR] [COLOR=blue]Property[/COLOR] [COLOR=blue]Set[/COLOR] CheckBox([COLOR=blue]ByRef[/COLOR] Value [COLOR=blue]As[/COLOR] MSForms.CheckBox)
    [COLOR=blue]Set[/COLOR] fCheckbox = Value
[COLOR=blue]End[/COLOR] [COLOR=blue]Property[/COLOR]
 
[COLOR=blue]Private[/COLOR] [COLOR=blue]Sub[/COLOR] fCheckbox_Click()
    [COLOR=blue]MsgBox[/COLOR] "You clicked on " & fCheckbox.Name & vbNewLine & "It's value is " & fCheckbox.Value
[COLOR=blue]End[/COLOR] [COLOR=blue]Sub[/COLOR]

Then run the userform and try ticking and unticking the checkboxes.


One caveat to note is that you should use VBA.TypeName() rather than TypeOf when doing this for ActiveX CheckBoxes.

Hope that helps...

 

[RoryA]

If you were feeling lazy, you could also make the click events public and use CallByName in a loop, but wouldn't the events already have run when you set the checkboxes to True anyway?

 

[kazlady]

Thanks for the input guys. Colin, I think that was more work than I wanted to put into this part of the project. I will play around with the code you gave me though so I know for another project.

Yes, Rory, the code would have run once, but the radio buttons change the attribute from High Definition to Standard Definition. When you click the HD button, It has to run them again to add the HD versions of the channels in those packs. When you click the SD button, it just looks for HD and unclicks the channel. Otherwise you're right, though
I'm extremely new to this and knd of learning as I go).

Here's what I did. It's a little slower than I like, but that's what I get for using Excel instead of writing the entire program

Here's what I did instead.

Dim i As Integer
 
For i=1 to 26
Range("L10").Select
If ActiveCell.Offset(i,0)=True Then
ActiveCell.Offset(i,0).FormulaR1C1=False
ActiveCell.Offset(i,0).FormulaR1C1=True
Else: End If
Next

Made the click events for those checkboxes fire which is already coded to add teh HD channels. I guess if I wanted, I could put this into an array, but again that's more work than I wanted to do. Thanks again guys. Stay tuned. I'm sure I'll post another thread when I actually debug my hyperlinks