由ANY复选框触发的事件单击 [英] Event triggered by ANY checkbox click

问题描述

当我单击工作表上的任何一个复选框时，我都在疯狂地尝试寻找一种运行代码的方法。我看过多篇关于如何制作类模块的文章，但似乎无法使它正常工作。

I'm going crazy trying to find a way for code to run when I click on ANY of the checkboxes on my sheet. I've seen multiple articles talking about making a class module, but I can't seem to get it to work.

我有将填充B列以匹配C列的代码。即使C10是一个公式，我手动键入C10的内容也会填充到B10中：= D9。因此，我可以在D10中键入TRUE，C10中的公式将得出：TRUE，然后代码将B10填充为：TRUE。太棒了...诀窍是要有一个链接到D10的复选框。当我单击复选框时，D10表示TRUE，而C10中的公式表示TRUE，但这已尽其所能。 VBA代码无法识别复选框单击。如果我随后单击工作表（选择更改），那么代码将运行，因此我知道我需要其他事件。

I have code that will populate column B to match column C. Whatever I manually type into C10 will populate into B10, even if C10 is a formula: =D9. So, I can type TRUE into D10 and the formula in C10 will result in: TRUE and then the code populates B10 to say: TRUE. Awesome... the trick is to have a checkbox linked to D10. When I click the checkbox, D10 says TRUE and the formula in C10 says TRUE, but that is as far as it goes. The VBA code does not recognize the checkbox click. If I then click on the sheet (selection change), then the code will run, so I know I need a different event.

更改事件很容易到 Checkbox1_Click（），但我希望它适用于我单击的任何复选框。经过数天的搜索和尝试不同的事情，我没有任何运气。

It is easy enough to change the event to "Checkbox1_Click()", but I want it to work for ANY checkbox I click. I'm not having ANY luck after days of searching and trying different things.

这是我到目前为止正在运行的代码

here is the code I'm running so far

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim i As Long


For i = 3 To 11
    Range("B" & i).Value = Range("c" & i)
Next i
    End Sub

任何帮助，将不胜感激。

Any help would be appreciated.

推荐答案

这有效

' this goes into sheet code

Private Sub Worksheet_Activate()
    activateCheckBoxes
End Sub

。

' put all this code in class a module and name the class module "ChkClass"

Option Explicit

Public WithEvents ChkBoxGroup As MSForms.CheckBox

Private Sub ChkBoxGroup_Change()
    Debug.Print "ChkBoxGroup_Change"
End Sub

Private Sub ChkBoxGroup_Click()
    Debug.Print "ChkBoxGroup_Click"; vbTab;
    Debug.Print ChkBoxGroup.Caption; vbTab; ChkBoxGroup.Value
    ChkBoxGroup.TopLeftCell.Offset(0, 2) = ChkBoxGroup.Value

End Sub

。

' this code goes into a module

Option Explicit

Dim CheckBoxes() As New ChkClass
Const numChkBoxes = 20
'

Sub doCheckBoxes()
    makeCheckBoxes
    activateCheckBoxes
End Sub

Sub makeCheckBoxes()       ' creates a column of checkBoxes

    Dim sht As Worksheet
    Set sht = ActiveSheet

    Dim i As Integer
    For i = 1 To sht.Shapes.Count
    '    Debug.Print sht.Shapes(1).Properties
        sht.Shapes(1).Delete
        DoEvents
    Next i

    Dim xSize As Integer:    xSize = 2      ' horizontal size (number of cells)
    Dim ySize As Integer:    ySize = 1      ' vertical size

    Dim t As Range
    Set t = sht.Range("b2").Resize(ySize, xSize)

    For i = 1 To numChkBoxes
        sht.Shapes.AddOLEObject ClassType:="Forms.CheckBox.1", Left:=t.Left, Top:=t.Top, Width:=t.Width - 2, Height:=t.Height
        DoEvents
        Set t = t.Offset(ySize)
    Next i

End Sub

Sub activateCheckBoxes()       ' assigns all checkBoxes on worksheet to ChkClass.ChkBoxGroup

    Dim sht As Worksheet
    Set sht = ActiveSheet

    ReDim CheckBoxes(1 To 1)

    Dim i As Integer
    For i = 1 To sht.Shapes.Count

        ReDim Preserve CheckBoxes(1 To i)
        Set CheckBoxes(i).ChkBoxGroup = sht.Shapes(i).OLEFormat.Object.Object

    Next i

End Sub

这篇关于由ANY复选框触发的事件单击的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！