通过 VBA 检测复选框的变化
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1007164/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Detecting changes to checkboxes via VBA
提问by graham.reeds
Following on from my previous question.
继我之前的问题之后。
A requirement from the customer is to have checkboxes on a report to disable rows of information on another sheet. The rows are defined as named ranges, formated by P_XXXXXX. The XXXXXX is a unique identifier that is also a field on the row so I can easily generate the range names on the fly.
客户的要求是在报告上设置复选框以禁用另一个工作表上的信息行。行被定义为命名范围，由 P_XXXXXX 格式化。XXXXXX 是一个唯一标识符，它也是行上的一个字段，因此我可以轻松地动态生成范围名称。
The problem I am having is:
我遇到的问题是：
- After clicking on the items and then closing the form Excel asks if we want to save. This is undersirable.
- 单击项目然后关闭表单后，Excel 会询问我们是否要保存。这是不可取的。
I need someway of registering a change event happening on my generated checkboxes. So if one or more changes I can run through and hide/unhide the relevant ranges.
我需要以某种方式注册发生在我生成的复选框上的更改事件。因此，如果一个或多个更改，我可以运行并隐藏/取消隐藏相关范围。
My code for adding the checkboxes looks like:
我添加复选框的代码如下所示：
' For each row...
' check box in column 17(=Q).
Dim lCenter As Long
lCenter = rngCurrent.Width / 4 ' not actual centre but close enough
With ActiveSheet.CheckBoxes.Add(rngCurrent.Left + lCenter, rngCurrent.Top - 2, rngCurrent.Width, rngCurrent.Height)
.Interior.ColorIndex = xlNone
.Caption = ""
End With
So how do you link a change in a checkbox with a sub/function?
那么如何将复选框中的更改与子/功能联系起来呢？
回答by Dick Kusleika
Set the OnAction property of the Checkboxes object to the name of a sub you want to run whenever the checkbox is checked or unchecked.
将 Checkboxes 对象的 OnAction 属性设置为您想要在选中或取消选中复选框时运行的子的名称。
Sub MakeCB()
With ActiveSheet.CheckBoxes.Add(ActiveCell.Left + 0, ActiveCell.Top - 2, ActiveCell.Width, ActiveCell.Height)
.Interior.ColorIndex = xlNone
.Caption = ""
.OnAction = "CheckboxChange"
End With
End Sub
Sub CheckboxChange()
MsgBox "change"
End Sub回答by Ant
I don't think there are any events available with the Excel.Checkbox control. Try using the MSForms checkbox instead. You'll need a reference to 'Microsoft Forms 2.0 Object Library' - it's not redistributeable, but if you're using VBA, then that's fine.
我认为 Excel.Checkbox 控件没有可用的任何事件。尝试改用 MSForms 复选框。您需要引用“Microsoft Forms 2.0 Object Library”——它不可再分发，但如果您使用的是 VBA，那就没问题了。
You can then do something like this, and handle the event in the usual way:
然后你可以做这样的事情，并以通常的方式处理事件：
''class level
Private WithEvents m_Checkbox as MSForms.CheckBox
Public Sub MakeCheckbox()
Set m_Checkbox = Activesheet.OLEObjects.Add("Forms.Checkbox.1")
End Sub
Private Sub m_Checkbox_Click()
''Do stuff
End Sub
Obviously, you'll only be able to handle a set number of checkboxes this way - I would recommend creating a class to hold each checkbox.
显然，您只能以这种方式处理一定数量的复选框 - 我建议创建一个类来保存每个复选框。
