Excel VBA 复选框单击和更改事件相同吗？

Excel VBA checkbox click and change events the same?

提问by mikebarrera

I have 5 checkboxes to set some options for a macro, one of them is a Select/Unselect All checkbox. I want to create something similar to what you have on web-based mailboxes when you select mails to delete or mark as read, etc. When I check the Select/Unselect All checkbox, I turn the rest of the checkboxes's values to true and viceversa when I uncheck it. That's ok.

我有 5 个复选框来为宏设置一些选项，其中之一是 Select/Unselect All 复选框。当您选择要删除的邮件或标记为已读等时，我想创建类似于您在基于 Web 的邮箱上拥有的内容。当我选中 Select/Unselect All 复选框时，我将其余复选框的值设为 true，反之亦然当我取消选中它时。没关系。

The problem comes when I also want to validate that if everything is unchecked and one by one I check the other checkboxes, if in the end I check all, then the Select/Unselect All checkbox turns to checked. And viceversa, meaning that if eveything is checked and then I uncheck one of the four others, then I turn the "All" checkbox to false (unchecked).

当我还想验证是否所有内容都未选中并且我逐个选中其他复选框时出现问题，如果最后我选中所有，则选择/取消全选复选框变为选中状态。反之亦然，这意味着如果所有内容都被选中，然后我取消选中其他四个中的一个，那么我将“全部”复选框设置为 false（未选中）。

But it seems that even when I just set the value, for example Option1Checkbox.value = True, in the SelectAllCheckbox_Click event, it triggers both the Option1Checkbox_Click and Option1Checkbox_Change events.

但似乎即使我只是在 SelectAllCheckbox_Click 事件中设置值，例如 Option1Checkbox.value = True，它也会触发 Option1Checkbox_Click 和 Option1Checkbox_Change 事件。

Shouldn't it just trigger the Change event since I'm not actually clicking that checkbox?

它不应该只是触发 Change 事件，因为我实际上并没有点击那个复选框吗？

What happens is that I check the SelectAll, so it turns Option1 to checked, but by doing this, Option1 triggers also the click event so it unchecks it which triggers the click event again and then unchecks again the All checkbox which in the end leaves everything unchecked, like at the beginning. Hope this part is clear enough.

发生的情况是我检查了 SelectAll，因此它将 Option1 变为选中状态，但是通过这样做，Option1 也会触发 click 事件，因此它取消选中它再次触发 click 事件，然后再次取消选中最后留下所有内容的 All 复选框未经检查，就像在开始时一样。希望这部分足够清楚。

How can I avoid this behavior? How can I make sure only the Change event is triggered and not the Click event?

我怎样才能避免这种行为？如何确保仅触发 Change 事件而不触发 Click 事件？

Has anyone ever had such an arrangement of checkboxes and had a similar problem? Or how did you manage to do this without the behavior I'm getting?

有没有人有过这样的复选框排列并有类似的问题？或者你是如何在没有我得到的行为的情况下做到这一点的？

The checkboxes are not on a form but simply on a worksheet. They are ActiveX controls. And what I have is nothing complicated:

复选框不在表单上，​​而只是在工作表上。它们是 ActiveX 控件。我所拥有的并不复杂：

Private Sub SelectAll_Click()
    Option1Checkbox.Value = SelectAll.Value
    Option2Checkbox.Value = SelectAll.Value
    Option3Checkbox.Value = SelectAll.Value
    Option4Checkbox.Value = SelectAll.Value
End Sub

Then the options checkboxes click events look like this:

然后选项复选框单击事件如下所示：

Private Sub Option1Checkbox_Click()
    If Option1Checkbox.Value = True And Option2Checkbox.Value = True And Option3Checkbox.Value = True And Option4Checkbox.Value = True Then
        SelectAll.Value = True
    Else
        SelectAll.Value = False
    End If
End Sub

It's quite simple, the biggest problem I see is the calls to the click events when the checkbox is not actually click.

这很简单，我看到的最大问题是当复选框实际上没有点击时调用点击事件。

Thanks for your help.

谢谢你的帮助。

回答by SeanC

I would define a local variable (one defined outside the subs) and set/check that

我会定义一个局部变量（一个在 subs 之外定义的）并设置/检查

Option Explicit
Dim ImChangingStuff As Boolean

Private Sub SelectAll_Click()
    ImChangingStuff = True
    Option1Checkbox.Value = SelectAll.Value
    Option2Checkbox.Value = SelectAll.Value
    Option3Checkbox.Value = SelectAll.Value
    Option4Checkbox.Value = SelectAll.Value
    ImChangingStuff = False
End Sub

then your click routines would look like this:

那么您的点击例程将如下所示：

Private Sub Option1Checkbox_Click()
    If ImChangingStuff Then Exit Sub
    If Option1Checkbox.Value = True And Option2Checkbox.Value = True And Option3Checkbox.Value = True And Option4Checkbox.Value = True Then
        SelectAll.Value = True
    Else
        SelectAll.Value = False
    End If
End Sub

回答by user5910731

To solve this problem,

为了解决这个问题，

1. in your main userform (in this example it is called Mainform) add this code:

   Private Sub Mainform_Initialize() stateCheckbox = True End Sub

2. Create a module to store your global variables (eg. globalVariables), and add the following, which will hold the state of the checkbox:

   Public stateCheckbox as Boolean

3. In the _click event of your checkbox, wrap your code like this:

   if stateCheckbox = false then { whatever your code is } Else: stateCheckbox = false end if

1. 在您的主用户表单（在本例中称为 Mainform）中添加以下代码：

   Private Sub Mainform_Initialize() stateCheckbox = True End Sub

2. 创建一个模块来存储您的全局变量（例如globalVariables），并添加以下内容，这将保存复选框的状态：

   公共 stateCheckbox 作为布尔值

3. 在复选框的 _click 事件中，像这样包装你的代码：

   如果 stateCheckbox = false then { 不管你的代码是什么 } 否则： stateCheckbox = false end if

回答by DaveD

I have had the same issue as described above. I recognized that each change of a Checkbox, no matter if by click or by changing Checkbox.Value, each time first fires the Checkbox_Changeevent and then the Checkbox_Clickevent , provided that any of these events are declared. I would thus recommend to use only one event for each checkbox, p.e. the Checkbox_Changeevent, and to check if the checkbox has been changed by click or by declaration at the beginning of the Sub. This can be done by comparing the ActiveControl.Namewith the Checkbox.Name:

我遇到了与上述相同的问题。我认识到 Checkbox 的每次更改，无论是通过单击还是通过更改Checkbox.Value，每次都会首先触发Checkbox_Change事件，然后是Checkbox_Click事件，前提是声明了这些事件中的任何一个。因此，我建议对每个复选框只使用一个事件，peCheckbox_Change事件，并检查复选框是否已通过单击或Sub. 这可以通过将ActiveControl.Name与进行比较来完成Checkbox.Name：

Private Sub CheckBox1_Change()
On Error Goto Err:
If ActiveControl.Name = CheckBox1.Name Then 
    On Error Goto 0        
    'Commands for click
    Exit Sub
Else
    On Error Goto 0 
    'Commands for change from within the Userform
    Exit Sub
Err:On Error Goto 0 
    'Commands for change from outside the Userform
End Sub    

回答by RestitutorOrbis

I would write a sub checking if Option1 to Option4 are all checked in an If statement. In a second if statemement, I'd check to see if they are all false:

如果 Option1 到 Option4 都在 If 语句中检查，我会写一个子检查。在第二个 if 语句中，我会检查它们是否都是假的：

Sub Are_O1_to_O4_All_Checked ()
  If Option1Checkbox.Value = True And _ 
     Option2Checkbox.Value = True And _
     Option3Checkbox.Value = True And _
     Option4Checkbox.Value = True Then
        Option5Checkbox.Value  = True      ' select/unselect checkbox
  End If

  If Option1Checkbox.Value = False And _ 
     Option2Checkbox.Value = False And _
     Option3Checkbox.Value = False And _
     Option4Checkbox.Value =  False Then
        Option5Checkbox.Value  = False      ' select/unselect checkbox
  End If

End Sub

结束子

Then I would Call this sub at the end of each OnClick event for the check boxes.

然后我会在复选框的每个 OnClick 事件结束时调用这个子程序。