CODEWITHSUNDEEP
excelvbaloopscheckboxuserform
Doug
Doug

Reputation: 265

Excel VBA: How to loop through checkboxes based on name

I want to build a survey using excel and vba. I have a table with questions and answers. As the survey starts, my code will list the answers by overwriting the checkboxes' labels on a form. I will fetch their answer and write them in a column by using True or False value of a checkbox.

Variable "aRow" is the number of the answers for each question. "lastAns" is the row number of the last answer. Depending on the number of the answer, some checkboxes will be hidden, shown. "CheckBox1" to "CheckBox4" are the names of the checkboxes.

The following code works, but it is too long and I want to have a better method to loop through the checkboxes and changing their labels each time. Please show me how to do it! Thanks you so much!

     `lastAns = Cells(qRow, 5).End(xlDown).Row + 1
       aRow = lastAns - qRow
        If aRow >= 1 Then
            Me.CheckBox1.Visible = True
            Me.CheckBox1.Caption = Cells(qRow, 5)
            Else: Me.CheckBox1.Visible = False
        End If
        If aRow >= 2 Then
            Me.CheckBox2.Visible = True
            Me.CheckBox2.Caption = Cells(qRow + 1, 5)
            Else: Me.CheckBox2.Visible = False
            End If
        If aRow >= 3 Then
            Me.CheckBox3.Visible = True
            Me.CheckBox3.Caption = Cells(qRow + 2, 5)
            Else: Me.CheckBox3.Visible = False
            End If
        If aRow >= 4 Then
            Me.CheckBox4.Visible = True
            Me.CheckBox4.Caption = Cells(qRow + 3, 5)
            Else: Me.CheckBox4.Visible = False
            End If
               .....SAME CODE CONTINUES TILL 7...`

Upvotes: 0

Views: 3641

Answers (1)

Vincent G
Vincent G

Reputation: 3188

As a follow up answer to my comment, here is what I think you are looking for:

    arow = lastAns - qRow
    Dim i As Long, ctl As Control
    For i = 1 To 4
        Set ctl = Me.Controls("CheckBox" & i)
        If i <= arow Then
            ctl.Visible = True
            ctl.Caption = Cells(qRow + i - 1, 5)
        Else
            ctl.Visible = False
        End If
    Next i

Upvotes: 2

Related Questions