动态将复选框和事件处理程序添加到工作表 [英] Dynamically add checkboxes and event handler to a worksheet

问题描述

在BI列的工作表中，组件的名称为
。在AI列中，希望动态添加复选框，因为B列的名称列表会随着时间的推移而增加

我需要能够执行以下操作：
-处理事件（onclick），方法是勾选/取消选中复选框隐藏/取消隐藏另一张工作表中的行
-返回状态每个复选框在我循环浏览另一个模块中的复选框时（从命令箱单击），并根据状态执行操作或不执行操作
-修改每个复选框的状态，因为我有1个命令框将其全部打勾和1个命令框完全取消它们

到目前为止，我有一个可以正常工作的原型，但是我有两个问题：
1）复选框未链接到A列中的单元格，它们只是在我创建它们
时定位。2）复选框不是动态的，我手动创建了它们，并且必须为每个复选框（> 50个复选框）编写一个事件处理程序（onclick）

我有试图创建一个代码以动态添加复选框并创建一个用于处理事件的类模块，但是我真的很坚持。
我复制并修改了一些原本打算用于用户窗体的代码，并设法使其起作用

这里是类模块代码（名为：clsBoxEvent）

 选项显式
 Public WithEvents cBox作为MSForms.CheckBox 
 
 Private Sub cBox_Click（）
 MsgBox cBox.Name 
 End Sub 
  

这是我作为模块编写的代码。我计划将其放置在命令按钮中的事件（单击）中，该按钮我打算单击以更新复选框列表。除非这不是必须的，否则可以使用一种方法在B列中的单元格不为空白时立即创建复选框？

我感谢您的输入。

  Dim chkBoxEvent as clsBoxEvent 
 Dim chkBox As MSForms.CheckBox 
 Dim chkBoxColl As Collection 
 
 Private Sub chkBox_update（）
 Dim i As Integer 
 Set chkBoxColl = New Collection 
 
 For i = 1 To 5 
'我只是写代码添加5个复选框作为测试。稍后，我需要将其调整为实际所需的复选框数量（B列中的产品数量）
 
 Set chkBox = Controls.Add（ Forms.CheckBox.1， ChkBox& ; i）
和chkBox 
'我的职位是我不知道如何将其链接到A栏的单元格
 .Left = 126 
 .Height = 16 
 .Top = 6 +（（i-1）* 16）
以
结尾
设置chkBoxEvent =新的clsBoxEvent 
设置chkBoxEvent.cBox =控件（chkBox。名称）
 chkBoxColl.Add chkBoxEvent 
 
 Next i 
 
 End Sub 
  

解决方案

我的回答：

  Sub AddCheckBoxes（）
 
 Dim cb As CheckBox 
 Dim myRange As Range，cel当范围
昏暗wks作为工作表
 
设置wks = Sheets（ Sheet1）
 
设置myRange = wks.Range（ A1：A1000）
 
 myRange中的每个cel 
 
 Set cb = wks.CheckBoxes.Add（cel.Left，cel.Top，30，6）
 
 With cb 
 .Caption = 
 .OnAction = ProcessCheckBox 
结尾为
 
下一个
 
结束子
 
 Sub ProcessCheckBox（）
 Dim cb as CheckBox 
 With Sheets（ Sheet1）
 Set cb = .CheckBoxes（Application.Caller）
如果不是cb则无cb .TopLeftCell = IIf（cb.Value = 1，已清除，）
结尾为
结束子
  

In my sheet in column B I have names of components In column A I would like to have checkboxes which are dynamically added, as the list of names in column B will increase over time

I need to be able to do the following: - handle the events (onclick) as ticking/unticking the checkboxes hides/unhides rows in another sheet - return the status of each checkbox as I cycle through the checkboxes in another module (onclick from a commandbox) and depending on the status an action follows or not - modify the status of each checkbox as I have 1 commandbox to tick them all and 1 commandbox to untick them all

So far I have a working prototype, but I have 2 problems: 1) the checkboxes are not linked to the cells in column A, they are just positioned when I created them 2) the checkboxes are not dynamic, I created them manually and had to write an event handler (onclick) for each checkbox (> 50 checkboxes)

I have tried to create a code to dynamically add checkboxes and create a class module to handle the events, but I am really stuck.. I copied and modified some code that was originally intended for a userform and I managed to make it work on a userform, but I'd rather have everything on the worksheet as i described above.

Here is the class module code (named: clsBoxEvent)

Option Explicit
Public WithEvents cBox As MSForms.CheckBox

Private Sub cBox_Click()
MsgBox cBox.Name
End Sub

Here is the code I wrote as a module. I plan to put it in an event (onclick) from a command button which I plan to click to update the list of checkboxes. Unless this is not necessary as there is a way that the checkboxes are created as soon as the cell in column B isn't blank ?

I thank you for your input.

Dim chkBoxEvent As clsBoxEvent
Dim chkBox As MSForms.CheckBox
Dim chkBoxColl As Collection

Private Sub chkBox_update()
Dim i As Integer
Set chkBoxColl = New Collection

For i = 1 To 5
    ' I wrote the code just to add 5 checkboxes as a test. Later I will need to adapt this to the actual required number of checkboxes (the number of products in column B)  

    Set chkBox = Controls.Add("Forms.CheckBox.1", "ChkBox" & i)
    With chkBox
       ' I work with the position as I did not know how to link it to the cells in colums A
        .Left = 126
        .Height = 16
        .Top = 6 + ((i - 1) * 16)
    End With

Set chkBoxEvent = New clsBoxEvent
Set chkBoxEvent.cBox = Controls(chkBox.Name)
chkBoxColl.Add chkBoxEvent

Next i

End Sub

解决方案

My answer to: Excel VBA script to insert multiple checkboxes linked to cell with yes and no instead of true and false seems like it will work nicely for you.

Sub AddCheckBoxes()

    Dim cb As CheckBox
    Dim myRange As Range, cel As Range
    Dim wks As Worksheet

    Set wks = Sheets("Sheet1")

    Set myRange = wks.Range("A1:A1000")

    For Each cel In myRange

        Set cb = wks.CheckBoxes.Add(cel.Left, cel.Top, 30, 6)

        With cb
            .Caption = ""
            .OnAction = "ProcessCheckBox"
        End With

    Next

End Sub

Sub ProcessCheckBox()
    Dim cb As CheckBox
    With Sheets("Sheet1")
        Set cb = .CheckBoxes(Application.Caller)
        If Not cb Is Nothing Then cb.TopLeftCell = IIf(cb.Value = 1, "Cleared", "")
    End With
End Sub

这篇关于动态将复选框和事件处理程序添加到工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！