Using checkboxes to create an array

Question

A background to why im trying to do this. I am creating a program for work, this is one of many way of doing this process, where users select test that they have carried out, around 50 possible test using a userform that I've made. From the selection it runs through each userform based off which checkboxes were ticked, without the need to choose another test.

i.e. if they select 1,2,5 then the program will eventually load UserForm1 -> UserForm2 -> NOT UserForm3 -> NOT UserForm4 -> UseeForm5

The idea is that the user selects the options they want then based off the options selected create an array of either 0 if they didn't select the option or (1,2,3,4...) if they did, this number depends upon which checkbox was selected. i.e CheckBox1 = 1 CheckBox2 = 2 etc.

From this array I think i'll be able to select the correct userform using the .find feature in excel with a for loop.

But I have an issue when I run my code below,

Sub List_Create()

Dim tests(5) As Integer

        If CheckBox1.Value = True Then
        tests(0) = 1
        Else: tests(0) = 0
        End If
        If CheckBox2.Value = True Then
        tests(1) = 2
        Else: tests(1) = 0
        End If
        If CheckBox3.Value = True Then
        tests(2) = 3
        Else: tests(2)= 0
        End If
        If CheckBox4.Value = True Then
        tests(3) = 4
        Else: tests(3) = 0
        End If
        If CheckBox5.Value = True Then
        tests(4) = 5
        Else: tests(4) = 0
        End If

End Sub

I get a

runtime 424 error object required.

Which when debugged is on the line If CheckBox1.Value = True Then

Where have i gone wrong with my code? Is this possible, im not sure if it is?

@Mat'sMug haha yeah i really need, i just use this format as i find it eaiser to indent it 4 time on VBA then paste in here than do each row seperetly. Im sure there an easier way to paste code here than my current method :P — CptGoodar, Dec 21 '17 at 15:38
@Mat'sMug like that Website though, seems to work really well. Will use it more often now. — CptGoodar, Dec 21 '17 at 15:39
FYI you can easily get correctly formatted code blocks by pasting here straight from the VBE editor, then selecting all the code and pressing Ctrl+K or clicking the `{}` button in the edit toolbar (will automatically add 4 leading spaces to every line) — Mathieu Guindon, Dec 21 '17 at 15:40
@Mat'sMug ive found that whenever i try and use the Ctrl+K method it never seems to work at all. Will try the `{}` method next — CptGoodar, Dec 21 '17 at 15:42
See this question for help https://stackoverflow.com/questions/42811509/object-required-error-when-trying-to-refer-to-checkbox-in-worksheet — mooseman, Dec 21 '17 at 15:42
Please confirm - are the checkboxes ActiveX controls on a worksheet? (in which case see @mooseman's link) Or they're MSForms controls on a UserForm and is this `List_Create()` procedure in the code-behind of *that* form? — Mathieu Guindon, Dec 21 '17 at 15:45
@Mat'sMug the check boxes are NOT ActiveX they are on a UserForm, yes the sub `List_Create` is in a module in the same workbook but not on behind the userform. Does that make sense? — CptGoodar, Dec 21 '17 at 15:48
@mooseman thank you for your comment, unfortunelty the checkboxes are not ActiveX control but rather on a UserForm so i don't think this will help. But thank you anyway — CptGoodar, Dec 21 '17 at 15:49
Don't you have to explicitly reference the userform to evaluate the checkbox? Like: If userformX.CheckBox1.Value = True — mooseman, Dec 21 '17 at 15:56
@mooseman That has wroked perfectly thank you ever so much, put that as an answer and i'll accept it. Thanks for all the help guys. — CptGoodar, Dec 21 '17 at 15:59

score 2 · Accepted Answer · answered Dec 21 '17 at 16:00

2

You need to explicitly reference the userform to evaluate the checkbox value.

 If userformX.CheckBox1.Value = True Then

answered Dec 21 '17 at 16:00

mooseman

1,997
2
17
29

Using checkboxes to create an array

1 Answers1