将相同的代码添加到多个组合框 [英] Add identical code to multiple combo boxes

问题描述

我有一个带有960个组合框的工作表。我需要它们都附加相同的代码：

I have a worksheet with 960 combo boxes. I need them to all have the same code attached:

Private Sub ComboBox1_DropButtonClick()
ActiveSheet.Range("a2").Select
End Sub

是否可以将此代码附加到每个组合自动在工作表上显示框，而无需一个接一个的繁琐任务？如果有问题，我附上此代码的原因是，当选择组合框时，在/除非用户单击任何单元格，否则工作表上的超链接和代码将不起作用。如果有一个可以解决此问题的属性设置，那么我宁愿这样做。

is there a way to attach this code to each combo box on the sheet automatically without the tedious task of doing it one by one? In case it matters, the reason that I have attached this code is because when the combo box is selected, the hyperlinks and code on the sheet won't work until/unless the user clicks any cell. If there is a Properties setting that takes care of this, then I would rather do that.

推荐答案

创建ComboBox集合

您需要

Create a ComboBox Collection

You'll need

• 自定义类 ComboWrapper 保留对您的引用的组合框
  
  
  
  
  • 使用WithEvents您将捕获按钮Click事件
  
  
  • Custom class ComboWrapper to hold a reference to you combobox
    • Using WithEvents you'll capture the buttons Click event
    
    
    • 使用 Worksheet_Activate（）实例化集合
    
    
    • Use the Worksheet_Activate() to instantiate the collection

    插入课程

    重命名 ComboWrapper

    将此代码插入 ComboWrapper 类

    Public WithEvents combo As MSForms.ComboBox
    
    Private Sub combo_Change()
    
        Range("A2").Select
    
    End Sub
    

    将此代码插入工作表代码模块

    Public ComboCollection As Collection
    
    Private Sub Worksheet_Activate()
        Dim o As OLEObject
        Dim wrapper As ComboWrapper
        Set ComboCollection = New Collection
    
        For Each o In ActiveSheet.OLEObjects
            On Error Resume Next
    
            If o.progID = "Forms.ComboBox.1" Then
                Set wrapper = New ComboWrapper
                Set wrapper.combo = o.Object
    
                ComboCollection.Add wrapper
            End If
    
            On Error GoTo 0
        Next
    
    End Sub
    

    这篇关于将相同的代码添加到多个组合框的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！