VBA代码问题：循环执行6次而非1次，未勾选ToggleButton仍传输全部数据

问题根源 #

1. 三重嵌套循环逻辑错误：你当前使用三层For Each循环，会遍历所有Toggle状态、所有输入框值和所有按钮标题的组合——只要有任意一个Toggle被勾选，所有标题和输入值都会被反复写入，导致数据大量重复且完全不符合预期。
2. 数组未按关联关系配对：每个ToggleButton、它的标题（Caption）和对应的输入框值是一一对应的，但原代码没有将三者绑定，而是独立遍历，彻底违背了“勾选对应按钮才传输对应数据”的需求。

修正后的代码 #

Private Sub SubmitCSR_Click()
    Dim LastRow As Long
    Dim ws As Worksheet: Set ws = Sheets("GP count")
    
    ' 定义三组关联数组，索引一一对应每个ToggleButton的相关数据
    Dim ToggleBtns(1 To 6) As Object
    Dim StockCaptions(1 To 6) As String
    Dim ReceivedValues(1 To 6) As String
    
    ' 按索引绑定每个ToggleButton、其标题和对应输入框值
    Set ToggleBtns(1) = ToggleButton6
    StockCaptions(1) = ToggleButton6.Caption
    ReceivedValues(1) = R1.Value
    
    Set ToggleBtns(2) = ToggleButton2
    StockCaptions(2) = ToggleButton2.Caption
    ReceivedValues(2) = R2.Value
    
    Set ToggleBtns(3) = ToggleButton4
    StockCaptions(3) = ToggleButton4.Caption
    ReceivedValues(3) = R3.Value
    
    Set ToggleBtns(4) = ToggleButton5
    StockCaptions(4) = ToggleButton5.Caption
    ReceivedValues(4) = R4.Value
    
    Set ToggleBtns(5) = ToggleButton1
    StockCaptions(5) = ToggleButton1.Caption
    ReceivedValues(5) = R5.Value
    
    Set ToggleBtns(6) = ToggleButton3
    StockCaptions(6) = ToggleButton3.Caption
    ReceivedValues(6) = R6.Value
    
    ' 按索引循环，仅处理已勾选的ToggleButton
    Dim i As Integer
    For i = 1 To 6
        If ToggleBtns(i).Value = True Then
            ' 写入对应标题到C列
            LastRow = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row
            ws.Range("C" & LastRow + 1).Value = StockCaptions(i)
            
            ' 写入对应输入值到E列
            LastRow = ws.Cells(ws.Rows.Count, "E").End(xlUp).Row
            ws.Range("E" & LastRow + 1).Value = ReceivedValues(i)
        End If
    Next i
End Sub

修正说明 #

• 用索引循环替代嵌套遍历：通过For i=1 To 6确保每组数据（ToggleButton、标题、输入值）严格对应，不会出现交叉配对的错误。
• 精准判断Toggle状态：仅当对应索引的ToggleButton被勾选时，才写入对应的标题和输入值，完全匹配你“只传输勾选按钮对应数据”的需求。
• 优化数据关联：将ToggleButton对象存入数组，更直观地管理各元素的对应关系，避免索引错位。

内容的提问来源于stack exchange，提问作者Anthony