时间戳

首页 > 解决方案 > 如何最好地分组 RadioButtons VBA Excel

excel - 如何最好地分组 RadioButtons VBA Excel

问题描述

我想我可以通过为我想要组合在一起的那些设置不同的 LinkedCell 属性来对单选按钮进行分组，但是，在我的代码中，所有按钮最终都转到同一个 LinkedCell。我需要动态地对 RadioButtons 进行分组，因为组中的按钮数量会有所不同。我正在研究一个考试程序，需要将每个问题的 RadioButtons 组合在一起，完成此任务的最简单方法是什么？

- 第二天我发现我没有增加 RadioButtons 的名称，所以每个按钮都有相同的名称。我还把按钮变小了，这样它们就不会重叠了，当我手动放置 GroupBox 时，一切都可以工作了，现在我必须从 VBA 中添加它们并交叉手指。

For c = 1 To ExamData(i, 7)
    ws.Range("C3").Offset(rOff + z, cOff).Value = ExamData(i, a)    'write answer
    rbCapt = CaptSelect(c)                                    'Set choice letter as caption
    Set t = ws.Cells(rOff + v, 2)
    Set rb = ws.OptionButtons.Add(t.Left + 20, t.Top, t.Width, t.Height)
    With rb
        .caption = rbCapt
        .Name = "Btn" & Trim(Str(b))
        .LinkedCell = "A" + Trim(Str(myRow)) '<- When myRow changes all Buttons change
    End With

标签： excelvbaradio-buttongrouping

解决方案

好吧，我完成了，添加 GroupBoxes 工作。这是最终代码：

For i = 1 To UBound(ExamData)
'write question number
    ws.Range("B3").Offset(rOff, cOff).Value = Trim(Str(i)) + "."
    myRow = ws.Range("B3").Offset(rOff, cOff - 1).Row                   'row number of question
'write question
    ws.Range("C3").Offset(rOff, cOff).Value = ExamData(i, 1)
'write answers & Radio Buttons
    For c = 1 To ExamData(i, 7)
        ws.Range("C3").Offset(rOff + z, cOff).Value = ExamData(i, a)    'write answer
        rbCapt = CaptSelect(c)                                          'Get choice letter for radio button
        Set t = ws.Cells(rOff + v, 2)
        Set rb = ws.OptionButtons.Add(t.Left + 11, t.Top + 12, t.Width - 22, t.Height - 20)
        With rb
            .caption = rbCapt
            .Name = "Btn" & Trim(Str(i)) & "-" & Trim(Str(b))
            .LinkedCell = "D" + Trim(Str(myRow))
        End With
        v = v + 1
        a = a + 1
        b = b + 1
        z = z + 1
    Next c
'add GroupBox
    pp = (myRow + 1 + ExamData(i, 7) - 1)
    gbRange = "B" + Trim(Str(myRow + 1)) + ":B" + Trim(Str(pp))
    Set vvv = ws.Range(gbRange)
    Set gb = ws.GroupBoxes.Add(vvv.Left, vvv.Top, vvv.Width, vvv.Height)
    With gb
        .caption = ""
        .Name = "gb" + Trim(Str(i))
    End With
    rOff = rOff + (ExamData(i, 7) + 2)
    v = 4
    z = 1
    a = 2
Next i

推荐阅读