如何在VBA中将自动生成的单选按钮设置为true? [英] How to set an automatically generated radio button to true in VBA?

问题描述

我正在创建一个Excel工作表，其中将根据特定参数的值自动生成单选按钮.请参阅此内容以获得清晰的理解:

I am creating an Excel sheet in which the radio buttons are automatically generated based on the value of specific parameter. Please refer this for clear understanding:

一组单选按钮被复制n次.其中，n是引用参数的行数.

A group of radio buttons are copied n number of times. where n is the number of rows that refers to a parameter.

应根据条件检查此自动生成的矩阵中的每个单选按钮，并在与条件匹配的一组中将十二个单选按钮之一设置为True.这里的主要复杂之处在于，每组单选按钮会根据需求复制到所需的行，因此，单选按钮会生成n * 12次，并且我不知道如何对自动生成的每个单选按钮进行编程.

Each radio buttons in this auto-generated matrix should be checked against a condition and one of the twelve radio button should be set to True in one group that matches the condition. The main complication here is that, each group of radio buttons are copied to required rows based on the requirement and so, the radio buttons are generated n*12 times and i don't know how to program each radio button that is generated automatically.

我需要知道可以使用哪个功能来满足我的要求.

I need to know, which Function can I use to fulfill my requirement.

我用以下代码创建了矩阵:

I have created the matrix with the following code:

    Dim n, m, i, j, x, k, a As Integer
n = (Sheets("ALLO").Range("E4").Value) * 2       
x = Sheets("ALLO").Range("E3").Value
m = (Sheets("ALLO").Range("E5").Value) + 1
a = m

For i = 2 To n Step 2                               
Sheets("Dummy_Result").Range("A2:M2").Copy Destination:=Sheets("Results").Range("A" & i)
Next i

For j = 3 To n Step 2
Sheets("Dummy_Result").Range("A3:M3").Copy Destination:=Sheets("Results").Range("A" & j)
Next j

For k = n + 1 To m Step 1
Sheets("Dummy_Result").Range("A3:M3").Copy Destination:=Sheets("Results").Range("A" & k)
Next k

End Sub

我更新的程序，用于根据'm'的值自动生成范围的按钮.生成的按钮数量应与"m"的值成正比.当我使用动态范围而不是固定范围时(如Mr.JosephC所建议的那样)，此程序不起作用

My updated Program, for generating the buttons automatically for the range dependent on the value of 'm'. The number of buttons generated should be directly proportional to the value of 'm'. This Program doesn't work when I use the dynamic range instead of Fixed range (As suggested by Mr.JosephC)

Sub Test()
    Dim n, m, i, j, x, k, a As Integer
    n = (Sheets("ALLO").Range("E4").Value) * 2       'No of Tack stations
    x = Sheets("ALLO").Range("E3").Value
    m = (Sheets("ALLO").Range("E5").Value) + 1
    a = m

    For i = 2 To n Step 2                               'Correct
    Sheets("Dummy_Result").Range("A2").Copy Destination:=Sheets("Results_1").Range("A" & i)
    Call AddOptionButtons(Sheets("Results_1").Range("B & m: M & m"))
    Next i

    For j = 3 To n Step 2
    Sheets("Dummy_Result").Range("A3").Copy Destination:=Sheets("Results_1").Range("A" & j)
Call AddOptionButtons(Sheets("Results_1").Range("B & m: M & m"))

    Next j

    For k = n + 1 To m Step 1
    Sheets("Dummy_Result").Range("A3").Copy Destination:=Sheets("Results_1").Range("A" & k)
Call AddOptionButtons(Sheets("Results_1").Range("B & m: M & m"))
    Next k


End Sub

Private Sub AddOptionButtons(ByRef TargetRange As Range)

    Dim oCell As Range
    For Each oCell In TargetRange
        oCell.RowHeight = 20
        oCell.ColumnWidth = 6
        Dim oOptionButton As OLEObject
        Set oOptionButton = TargetRange.Worksheet.OLEObjects.Add(ClassType:="Forms.OptionButton.1", Left:=oCell.Left + 1, Top:=oCell.Top + 1, Width:=15, Height:=18)
        oOptionButton.Name = "ob" & oCell.row & "_" & oCell.Column 
        'oOptionButton.Object.Caption = "Button"             oOptionButton.Object.GroupName = "grp" & oCell.Top
    Next
End Sub**strong text**

请在此处引用虚拟结果

推荐答案

这会将选项按钮添加到目标范围内的每个单元格.它将稍微调整单元格的大小，以尝试为其留出足够的空间(您可以随意调整选项按钮的位置和单元格的大小).它将根据在它们中设置的行号和列号来命名选项按钮的索引"值. ob2_4是第2行第4列(D)中的选项按钮.还将同一行中所有选项按钮的组名设置为相同.

This will add option buttons to each cell in the target range. It will resize the cells a bit to try and make enough space for them (you can fiddle with placement of the option buttons and size of the cells as you see fit). It will name the option buttons with their "index" values based on the row and column numbers they are set in ie. ob2_4 is option button in row 2, column 4 (D). It will also set the group name to be the same for all option buttons on the same row.

Sub Test()
    Call AddOptionButtons(Sheet1.Range("B5:D7"))
End Sub

Private Sub AddOptionButtons(ByRef TargetRange As Range)

    Dim oCell As Range
    For Each oCell In TargetRange
        oCell.RowHeight = 20
        oCell.ColumnWidth = 6
        Dim oOptionButton As OLEObject
        Set oOptionButton = TargetRange.Worksheet.OLEObjects.Add(ClassType:="Forms.OptionButton.1", Left:=oCell.Left + 1, Top:=oCell.Top + 1, Width:=15, Height:=18)
        oOptionButton.Name = "ob" & oCell.Row & "_" & oCell.Column 'Name them to make it easier if you need to access them later
        'oOptionButton.Object.Caption = "Caption" ' If you want to add text to the buttons
        oOptionButton.Object.GroupName = "grp" & oCell.Top
    Next
End Sub

个人笔记: 顺便说一句，请为变量使用有意义的名称. :)唯一应该使用单个字符变量的时间是，如果您对代码占用的空间有硬件要求.

Personal note: As an aside, please use meaningful names for your variables. :) The only time you should use single character variables is if you have hardware requirements on the footprint of your code.

这篇关于如何在VBA中将自动生成的单选按钮设置为true?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！