从一组ActiveX选项按钮返回索引值 [英] Returning an index value from a group of ActiveX Option Buttons

问题描述

让我先说说我是医学生，而不是程序员。我已经写了一个电子表格，作为考试有50个问题。每个问题有15个多项选择答案，我使用ActiveX选项按钮分组到每个问题编号。

我使用ActiveX而不是Form Option按钮，因为我将原始数据从检查工作表的隐藏区域，每个选项按钮标题引用原始数据中的单元格以显示不同的答案选择。这样，我可以通过添加一个问题表和答案选项轻松地创建一个新的考试。我没有看到如何使用表单选项按钮。

但ActiveX选项按钮似乎无法将索引值返回到单个单元格。所以，我为每个按钮写了以下内容：

  Sub OptionButton1_Click（）
 Range（B21）= A
 End Sub 
 
 Sub OptionButton2_Click（）
 Range（B21）=B
 End Sub 
 
 Sub OptionButton3_Click（）
范围（B21）=C
结束子
  

...等等。每个问题十五个按钮。五十个问题。我的工作表是非常慢的加载和崩溃在较慢的计算机上所有的时间。必须有一个更简单的方法。有什么建议么？理想情况下，我想要一小段代码，只要选择该组中的任何选项按钮，都可以将索引值从每个组返回到特定的单元格。任何帮助将不胜感激。

另一方面，另一个帮助将基本上让我在那里会有，如果有人可以帮助我找出如何更改表单选项按钮的标题以引用给定的单元格。这也将解决我的问题。

解决方案

创建组事件的方法是使用自定义类来包装控件你想要分组和一个模块级别的集合来保持包装器类的引用活动。

在查看了你的工作簿之后，我确定你可以根据OptionButton的名称导出一个索引。

类：OptionWrapper

  Option Explicit 
 
 Public WithEvents MyOptionButton As MSForms.OptionButton 
 
 Private Sub MyOptionButton_Click（）
 Dim letter（）
 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 
 
单元格（lRow，B）=字母（lAnswer）
 
 End Sub 
  

考试工作表代码模块

 私人选项收藏作为集合
 
 Private Sub Worksheet_Activate（）
 Dim obj As OLEObject 
 Dim wrap As OptionWra pper 
 
设置OptionsCollection =新集合
 
对于每个obj在ActiveSheet.OLEObjects 
 
如果TypeOf obj.Object是MSForms.OptionButton然后
 Set wrap = New OptionWrapper 
 Set wrap.MyOptionButton = obj.Object 
 OptionsCollection.Add wrap 
 End If 
 
下一个
 End Sub 
  

Let me start off by stating that I am a medical student, not a programmer. I have written a spreadsheet that will work as an exam with 50 questions. Each question has 15 multiple choice answers for which I use ActiveX Option Buttons grouped to each question number.

I use Activex instead of Form Option Button because I dump raw data from an exam into a hidden area of the worksheet and each option button caption references a cell in the raw data to display a different answer choice. This way I can easily create a new exam just by adding a table of questions and answer choices. I don't see how to do this with form option button.

But the ActiveX option buttons can't seem to return an index value to a single cell. So, I have written the following for each button:

Sub OptionButton1_Click()
  Range("B21") = "A"
End Sub

Sub OptionButton2_Click()
  Range("B21") = "B"
End Sub

Sub OptionButton3_Click()
  Range("B21") = "C"
End Sub

... and so on. Fifteen buttons per question. Fifty questions. My worksheet is terribly slow to load and crashes on slower computers all the time. There has to be an easier way. Any suggestions? Ideally, I would like a small snippet of code that could return an index value from each group to a particular cell whenever any option button in that group is selected. Any help would be greatly appreciated.

On the other hand... another piece of help that would basically get me there would be if someone could help me figure out how to change the caption of a form option button to reference a given cell. That would ALSO solve my problem.

解决方案

The way to create a group event is to have a custom class to wrap the controls that you want to group and a module level collection to keep the wrapper class references alive.

After reviewing your workbook I determined that you can derive an index based on the OptionButton's Name.

Class: 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

Exam Worksheet Code Module

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

这篇关于从一组ActiveX选项按钮返回索引值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！