excel - 如何根据 VBA 中另一个范围内的值将自动生成的选项按钮设置为 'True'？

Question

我借助 How to set an automatically generated radio button to true in VBA? 的答案生成了单选按钮.

我的要求是当有值时将自动生成的选项按钮设置为“真” x 在另一张纸上。

图 1:检查值的来源。


图 2:标记 的工作表x 应反射(reflect)为 True。


对于 2 行和 2 列中的选项按钮，生成的单选按钮与 OB2_2 一样被索引。

这是代码

Private Sub AddOptionButtons(ByRef TargetRange As Range)

Dim m As Variant
m = Sheets("ALLO").Range("D23").Value + 1

Sheets("Final").Range("A2:A" & m).Copy Destination:=Sheets("Int_Result").Range("A2:A" & m)

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.GroupName = "grp" & oCell.Top

Next
Call OB2_Click(oCell)

End Sub

Sub OB2_Click(oCell)

Dim col, ro, m As Variant
Dim Shap As Shape
m = Sheets("ALLO").Range("D23").Value + 1

For Each Shap In Sheets("Int_Result").Shapes
    For ro = 2 To m Step 1
        For col = 1 To 13 Step 1
            If Sheets("Final").Cells(ro, col).Value = "" Then
               Sheets("Int_Result").Shapes(ro, col).ControlFormat.Value = False
            Else
               Sheets("Int_Result").Shapes(ro, col).ControlFormat.Value = True
            End If
        Next col
    Next ro
Next Shap

End Sub

我明白了

"Object variable or With block variable not set" or "Wrong number of arguments or Invalid Property assignment".

在这条线上

Sheets("Int_Result").Shapes(ro, col).ControlFormat.Value = False 

如何访问自动生成的单选按钮？

Answer 1

你需要使用

 Sheets("Int_Result").OLEObjects("OB2_2").Object.Value = True

不是为形状设置循环，而是为最后一行和最后一列设置循环。

例如:

Dim oCell As Range
Dim LastCell 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.GroupName = "grp" & oCell.Top
    Set LastCell = oCell

Next
Call OB2_Click(LastCell)

Sub OB2_Click(oCell as Range)

Dim col As Long, ro As Long
dim m as long, k as long

col = oCell.Column
ro = oCell.Row

For m = 2 to ro
    For k = 2 to col
         If Sheets("Final").Cells(m, k).Value = "" Then
             Sheets("Int_Result").OLEObjects("OB" & m & "_" & k).Object.Value = False
         Else
             Sheets("Int_Result").OLEObjects("OB" & m & "_" & k).Object.Value = True
         End If
    Next k
Next m
End sub