创建组事件的方法是使用自定义类来包装要分组的控件,并使用模块级集合来保持包装器类引用处于活动状态。
查看您的工作簿后,我确定您可以根据选项按钮的名称派生索引。
类:OptionWrapper
Option Explicit
Public WithEvents MyOptionButton As MSForms.OptionButton
Private Sub MyOptionButton_Click()
Dim Letters()
Dim lRow As Long, lAnswer As Long, ID As Long
Letters = Array("O", "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N")
ID = Replace(MyOptionButton.Name, "OptionButton", "")
lRow = Int(ID / 15 + 1) * 21
lAnswer = ID Mod 15
Cells(lRow, "B") = Letters(lAnswer)
End Sub
考试工作表代码模块
Private OptionsCollection As Collection
Private Sub Worksheet_Activate()
Dim obj As OLEObject
Dim wrap As OptionWrapper
Set OptionsCollection = New Collection
For Each obj In ActiveSheet.OLEObjects
If TypeOf obj.Object Is MSForms.OptionButton Then
Set wrap = New OptionWrapper
Set wrap.MyOptionButton = obj.Object
OptionsCollection.Add wrap
End If
Next
End Sub