Changing userform checkbox value dependent on array index value

Question

I am looking to iterate through an array and change the visibility of a userform checkbox based on the array index value i.e. if the checkbox caption is equal to the array index value, then the checkbox visibility is set accordingly. By defualt, the visibility of checkboxes is true, and I want to hide checkboxes whose caption value do not appear in an array. The problem I am having is that all the checkboxes are visible despite the conditions set. I have checked the values of all iteration variables and the array values, and all seems to be ok. I am concerned whether I am initialising the userform incorrectly or in the wrong location? Any help will be greatly appreciated.

With XrayFile

'populates array with values in variable worksheet range

    Dim Xrayrange As Integer

    lastpos = Sheets(Ship).Cells(Rows.Count, "A").End(xlUp).Row - 1

    Xrayrange = lastpos - 6

    'create array with variable dimensions based on worksheet range

    ReDim X_ray_pos(Xrayrange) As String

    Dim j As Integer

    'iterate through worksheet range and set array index to cell value

    For j = LBound(X_ray_pos) To UBound(X_ray_pos)

        X_ray_pos(j) = Sheets(Ship).Range("A7").Offset(j).Value2

    Next j


    'userform1 is where the checkboxes are located.  I chose to initialize the userform here thinking that it would matter for the iteration and change of the default state of the checkboxes

    userform1.Show

    userform1.Hide

    Dim num As Variant

    Dim i As Long

    'iterates through checkboxes (named "CB1", "CB2" etc) and compares checkbox caption to array index value

    For i = 0 To 55

        Set c = Reject_list.Controls("CB" & i)

            For Each num In X_ray_pos

                If c.Caption Like num Then

                    c.Visible = True

                    Exit For

                Else

                    c.Visible = False

                End If
            Next num
    Next i

Your code looks kind of weird. First of all,, your form, is it userfrom1 or is it userform1? Are 2 different forms? Second, your Next num}, i think it should be without the }. And third, num is supossed to be the index of your array? — Foxfire And Burns And Burns
– Foxfire And Burns And Burns, Commented Mar 9, 2018 at 0:37
Yeah. Sorry. some typos arose when trying to make the code more readable on the forum (as opposed to using my designated names). Also my fat fingers make typing on my mobile error prone... I will edit the code shortly. Yes num is the indexing variable to the array. — akal
– akal, Commented Mar 9, 2018 at 7:45
I now know the issue. the checkbox visibility wa\s changed during every iteration.... for every loop. So I ultimately changed ALL checkbox's to being visible or not. I also needed to swap the order of iterations - from array into checkbox, to checkbox into array. I also needed to exit the For Loop when a condition is met. code changed. — akal
– akal, Commented Mar 9, 2018 at 10:14

akal · Accepted Answer · 2018-03-09 10:20:41Z

0

From this incorrect code:

For Each num In X_ray_pos  

    For i = 0 To 55

        Set c = Reject_list.Controls("CB" & i)

                If c.Caption Like num Then

                    c.Visible = True

                Else

                    c.Visible = False

                End If
            Next i
    Next num

To this solution:

For i = 0 To 55

        Set c = Reject_list.Controls("CB" & i)

            For Each num In X_ray_pos

                If c.Caption Like num Then

                    c.Visible = True

                    Exit For

                Else

                    c.Visible = False

                End If
            Next num
    Next i

answered Mar 9, 2018 at 10:20

akal

11 bronze badge

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Changing userform checkbox value dependent on array index value

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related