VBA Combobox/自动生成代码 [英] VBA Combobox / automatically generate code

问题描述

我有一个关于 Excel 中组合框的问题.

I've got a question concerning combobox in Excel.

我有一个 Excel 工作表，默认情况下包含两个组合框，它们的数量由变量 x 描述(默认情况下 x=2).每个组合框都被编写为在 subs 中以特定方式运行，例如我有:private sub ComboBox1_DropButtonClick().

I've got an excel sheet that by default contains two comboboxes and their number is described by a variable x (x=2 by default). Each combobox is scripted to behave in a particular way in subs, for example I've got: private sub ComboBox1_DropButtonClick().

尽管如此，有时我需要通过更改 X 的值来增加这些框的数量.我总共可能需要多达 10 个组合框.现在的问题是是否有任何方法可以设置无限数量的组合框的行为(例如在 DropButtonClick 事件中).我所做的是为每个组合框编写代码，因此我为 ComboBox1_DropButtonClick()、ComboBox2_DropButtonClick()、ComboBox3_DropButtonClick() 等编写了一个子代码.代码略有不同，但它是可重复的.所以这一切看起来都很愚蠢，我正在寻找一些更巧妙的解决方案.也许所有这些组合框都可以一次性编写脚本?如果有什么办法，请分享给我.

Nonetheless, sometimes I need to increase the number of these boxes by changing the value of X. I may need up to 10 comboboxes in total. Now the question is whether there's any way in which I can set the behaviour of an infinite number of comboboxes (for example in the event of DropButtonClick). What I did was to write a code for each of those comboboxes, so I've got a sub for ComboBox1_DropButtonClick(), ComboBox2_DropButtonClick(), ComboBox3_DropButtonClick(), etc.. The code varies a bit, but it's repeatable. So it all looks rather dumb and I'm searching for some more ingenious solution. Maybe all those comboboxes can be scripted in one go? If there's any way to do it, please share it with me.

谢谢，沃伊切赫.

我的代码的位置(以灰色标记):VBA 中的 VBA 编辑器截图

[edit] Location of my code (marked in grey): Screenshot from VBA editor in VBA

推荐答案

这里是一些动态添加控件到 Excel 用户窗体的代码，并添加后面的代码.添加的代码将使其在 ComboBox 收到 KeyDown 时显示一个 MessageBox.

Here is some code to dynamically add controls to an Excel Userform, and add the code behind. The code added will make it display a MessageBox when the ComboBox receives a KeyDown.

代码有些注释，但如果您有问题，请告诉我:)

The code is somewhat commented, but let me know if you have questions :)

Option Explicit

Sub CreateFormComboBoxes(NumberOfComboBoxes As Long)
    Dim frm         As Object
    Dim ComboBox    As Object
    Dim Code        As String
    Dim i           As Long

    'Make a blank form called 'UserForm1', or any name you want
    'make sure it has no controls or any code in it
    Set frm = ThisWorkbook.VBProject.VBComponents("UserForm1")

    With frm
        For i = 1 To NumberOfComboBoxes
            Set ComboBox = .designer.Controls.Add("Forms.ComboBox.1")
             'Set the properties of the new controls
             With ComboBox
                .Width = 100
                .Height = 20
                .Top = 20 + ((i - 1) * 40) 'Move the control down
                .Left = 20
                .Visible = True
                .ZOrder (1)
                .Name = "ComboBox" & i
            End With
            'Add your code for each module, you can add different code, by adding a if statement here
            'And write the code depending on the name, index, or something else
            Code = Code & vbNewLine & "Private Sub " & "ComboBox" & i & "_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)" & _
                   vbNewLine & "    MsgBox(""hi"")" & vbNewLine & "End Sub"
        Next

        'Add the code
        .CodeModule.InsertLines 2, Code
    End With

End Sub

'Run this
Sub Example()
    CreateFormComboBoxes 5
End Sub


**Edit**

<小时>

我想我还不如添加另一种将控件动态添加到 Excel 工作表的方法.我建议坚持使用 UserForms，但是，这里有一种方法可以在工作表中需要控件时提供帮助.

---

I figured I might as well add the other approach for adding controls dynamically to an Excel sheet. I'd recommend sticking to UserForms, but, here's a method that should help out when controls are needed in a Sheet.

Sub addCombosToExcelSheet(MySheet As Worksheet, NumberOfComboBoxes As Long, StringRangeForDropDown As String)
    Dim i           As Long
    Dim combo       As Shape
    Dim yPosition   As Long
    Dim Module      As Object

    yPosition = 20
    For i = 1 To NumberOfComboBoxes
        yPosition = (i - 1) * 50

        'Create the shape
        Set combo = MySheet.Shapes.AddFormControl(xlDropDown, 20, yPosition, 100, 20)

        ' Range where the values are stored for the dropDown
        combo.ControlFormat.ListFillRange = StringRangeForDropDown
        combo.Name = "Combo" & i
        Code = "Sub Combo" & i & "_Change()" & vbNewLine & _
               "    MsgBox(""hi"")" & vbNewLine & _
               "End Sub"
        'Add the code
        With ThisWorkbook
            'Make sure Module2 Exits and there is no other code present in it
            Set Module = .VBProject.VBComponents("Module2").CodeModule
            Module.AddFromString (Code)
        End With

        'Associate the control with the action, don't include the () at the end!
        combo.OnAction = "'" & ActiveWorkbook.Name & "'!Combo" & i & "_Change"
    Next

End Sub

Sub Example()
    Dim sht As Worksheet: Set sht = ThisWorkbook.Sheets(1)
    addCombosToExcelSheet sht, 10, "Sheet1!$A$1:$A$10"
End Sub

这篇关于VBA Combobox/自动生成代码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！