我有一个包含选项按钮的分组框,我需要在VBA中找出哪一个被选中了。我已经在MSDN上浏览了几个小时,但是我找不到解决方案。
一定有一种方法可以找到选定的选项按钮。可能是通过名称查找分组,并逐个遍历每个选项按钮?
我有一个包含选项按钮的分组框,我需要在VBA中找出哪一个被选中了。我已经在MSDN上浏览了几个小时,但是我找不到解决方案。
一定有一种方法可以找到选定的选项按钮。可能是通过名称查找分组,并逐个遍历每个选项按钮?
Sub Grouped_into_UnitType()
Dim i!
'grouped into 'UnitType' Shape
For i = 1 To ActiveSheet.Shapes("UnitType").GroupItems.Count
With ActiveSheet.Shapes("UnitType").GroupItems(i).ControlFormat
If .Value = 1 Then
MsgBox "Chosen item: " & i
End If
End With
Next i
End Sub
编辑考虑以下图片,上面的代码将解决问题,如果我们有选项按钮,这些按钮按照我们在工作表中放置任何形状的方式进行分组。
图片下方的代码将查找哪个选项按钮被选中,如果它们位于GroupBox内,则检查OptionButton所在组的名称。
重要提示!在我关闭Excel并重新运行之前,下面的代码不起作用。
Sub Grouped_into_GroupBox_UnitType()
Dim OB As OptionButton
For Each OB In ActiveSheet.OptionButtons
'check if grouped into 'UnitType' Shape
If OB.GroupBox.Name = "UnitType" Then
If OB.Value = 1 Then
MsgBox "Chosen item: " & OB.Name & _
vbNewLine & _
"Alt text: " & OB.ShapeRange.AlternativeText
End If
End If
Next
End Sub
Dim ... As OptionButton
。这似乎是使.GroupBox
工作的关键)Function WhichOption(shpGroupBox As Shape) As OptionButton
Dim shp As OptionButton
Dim shpOptionGB As GroupBox
Dim gb As GroupBox
If shpGroupBox.FormControlType <> xlGroupBox Then Exit Function
Set gb = shpGroupBox.DrawingObject
For Each shp In shpGroupBox.Parent.OptionButtons
Set shpOptionGB = shp.GroupBox
If Not shpOptionGB Is Nothing Then
If shpOptionGB.Name = gb.Name Then
If shp.Value = 1 Then
Set WhichOption = shp
Exit Function
End If
End If
End If
Next
End Function
Sub test()
Dim shpOpt As OptionButton
Set shpOpt = WhichOption(Worksheets("Sheet1").Shapes("Group Box 1"))
Debug.Print shpOpt.Name
End Sub
检查是否为“开启”:
Dim opt As Shape
Set opt = Worksheets("Sheet1").Shapes("Option Button 1")
If opt.ControlFormat.Value = xlOn Then
Debug.Print "option is ""on"" value of 1"
Else
Debug.Print "option is ""off"" value of -4146"
End If
要获取其替代文本,请使用以下代码:
Debug.Print "Alternate Text is: " & opt.AlternativeText
对于大量选项,可以使用“FormControlType”属性:
Dim s as Shape
For Each s In Worksheets("Sheet1").Shapes
If s.FormControlType = xlOptionButton Then
If s.ControlFormat.Value = xlOn Then
Debug.Print "option is ""on"" value of 1"
Else
Debug.Print "option is ""off"" value of -4146"
End If
Debug.Print "Alternate Text is: " & s.AlternativeText
End If
Next
Dim s As Shape, o
For Each s In Worksheets("Sheet1").Shapes
If s.FormControlType = xlOptionButton Then
Set o = s.OLEFormat.Object
If o.GroupBox.Name = "Group Box 3" Then
If s.ControlFormat.Value = xlOn Then
Debug.Print "Option is ""on"" value of 1"
Else
Debug.Print "Option is ""off"" value of -4146"
End If
Debug.Print "Alternate Text is: " & s.AlternativeText
Debug.Print "Group: " & o.GroupBox.Name
End If
Set o = Nothing
End If
Next
Set o = s.OLEFormat.Object
并使用 o.GroupBox.Name
来获取名称。 - glh