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

问题描述

首先让我声明我是一名医科学生，而不是一名程序员.我写了一个电子表格，可以作为一个有 50 个问题的考试.每个问题有 15 个多项选择答案，我使用 ActiveX 选项按钮分组到每个问题编号.

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.

我使用 Activex 而不是表单选项按钮，因为我将考试中的原始数据转储到工作表的隐藏区域，并且每个选项按钮标题引用原始数据中的一个单元格以显示不同的答案选择.这样，我只需添加一个问题和答案选项表即可轻松创建新考试.我看不到如何使用表单选项按钮执行此操作.

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.

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

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.

查看您的工作簿后，我确定您可以根据 OptionButton 的名称派生索引.

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

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

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