由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屋!