Excel VBA:如何根据名称循环遍历复选框 [英] Excel VBA: How to loop through checkboxes based on name

问题描述

我想使用excel和vba进行调查.我有一张桌子，上面有问题和答案.在调查开始时，我的代码将通过覆盖表单上复选框的标签来列出答案.我将获取他们的答案，并使用复选框的True或False值将它们写在列中.

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.

变量"aRow"是每个问题的答案数. "lastAns"是最后一个答案的行号.根据答案的数量，一些复选框将被隐藏，如图所示. "CheckBox1"到"CheckBox4"是复选框的名称.

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...`

推荐答案

作为对我的评论的后续答复，以下是我认为您正在寻找的内容:

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

这篇关于Excel VBA:如何根据名称循环遍历复选框的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！