如何在 VBA 中动态创建的 ComboBox 上创建 Sub? [英] How to create Sub on dynamically created ComboBox in VBA?

问题描述

我对 Excel 编程和 VBA 非常陌生.我被困在我有随机数量的动态创建的组合框(ComboBox1、ComboBox2 .... ComboBoxN)的地方.我需要实现一个功能，如果我在 ComboBox[i] 中选择一个值(其中 i 可以是 1 到 N 之间的任何随机数)，那么它应该触发一个事件，该事件将填充 ComboBox[i+1] 中的值.

I am very new to excel programming and VBA. I am stuck at a point where I have random number of dynamically created combo boxes (ComboBox1, ComboBox2.... ComboBoxN). I need to implement a functionality where if I select a value in the ComboBox[i] (where i can be any random number between 1 to N), then it should trigger an event that will populate values in ComboBox[i+1].

我如何为此编写一个子程序?如果不在 Sub 中，还有其他方法可以实现吗?

How do I write a Sub for this? Is there any other way to implement this if not in a Sub?

推荐答案

为了创建一个组事件，你需要一个自定义类来捕获事件( ObjectListener )，公共变量来保持该类引用活动(通常是一个集合或数组 - ComboListener )和一个宏来填充集合( AddListeners_ComboBoxes ).

In order to create a group events you'll need a custom class to capture the events ( ObjectListener ), public variable to keep the class references alive (usually a collection or array - ComboListener ) and a Macro to fill the collection ( AddListeners_ComboBoxes ).

从 Workbook_Open() 调用 AddListeners_ComboBoxes 宏.如果代码中断，您将需要再次调用 AddListeners_ComboBoxes.

Call the AddListeners_ComboBoxes Macro from the Workbook_Open(). You will need call AddListeners_ComboBoxes again if the code breaks.

Public ComboListener As Collection

Sub AddListeners_ComboBoxes()
    Dim ws As Worksheet
    Dim obj As OLEObject
    Dim listener As ObjectListener

    Set ComboListener = New Collection

    For Each ws In Worksheets
        For Each obj In ws.OLEObjects
            Select Case TypeName(obj.Object)
            Case "ComboBox"
                Set listener = New ObjectListener
                Set listener.Combo = obj.Object

                ComboListener.Add listener
            End Select
        Next
    Next
End Sub

Option Explicit

Public WithEvents Combo As MSForms.ComboBox

Private Sub Combo_Change()
    MsgBox Combo.Name
    Select Case Combo.Name
        Case "ComboBox2"
        ActiveSheet.OLEObjects("ComboBox3").Object.ListIndex = 1

    End Select

End Sub

这篇关于如何在 VBA 中动态创建的 ComboBox 上创建 Sub?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！