如何在VBA中动态创建ComboBox创建Sub？

问题描述

我很擅长编程和VBA。我被困在一个点，我有随机数的动态创建的组合框（ComboBox1，ComboBox2 .... ComboBoxN）。
我需要实现一个功能，如果我在ComboBox [i]中选择一个值（我可以是1到N之间的任何随机数），那么它应该触发一个事件，它将填充ComboBox [i +1]。

如何为此写一个Sub？有没有办法实现这个，如果不是在一个子？

解决方案

为了创建一个组事件，您需要一个自定义类来捕获事件（ ObjectListener ），public变量保持类引用有效（通常是一个集合或数组 - ComboListener ）和一个宏来填充集合（ AddListeners_ComboBoxes ）。

从 Workbook_Open（）调用 AddListeners_ComboBoxes 。如果代码中断，您将再次调用 AddListeners_ComboBoxes

标准模块

 公共ComboListener作为集合
 
 Sub AddListeners_ComboBoxes（）
 Dim ws As Worksheet 
 Dim obj As OLEObject 
 Dim listener作为ObjectListener 
 
设置ComboListener =新集合
 
对于每个ws在工作表
对于每个obj在ws.OLEObjects 
选择Case TypeName（ obj.Object）
案例ComboBox
设置listener =新建ObjectListener 
设置listener.Combo = obj.Object 
 
 ComboListener.Add listener 
结束选择
下一个
下一个
 End Sub 
  

Class ObjectList ener

  Option Explicit 
 
公共WithEvents组合作为MSForms.ComboBox 
 
 Private Sub Combo_Change（）
 MsgBox Combo.Name 
选择案例Combo.Name 
案例ComboBox2
 ActiveSheet.OLEObjects（ComboBox3）。Object.ListIndex = 1 
 
结束选择
 
 End Sub 
  

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].

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

解决方案

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 ).

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

Standard Module

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

Class ObjectListener

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？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！