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.

感谢Wojciech。

Thanks, Wojciech.

我的代码的位置（用灰色标记）：
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屋！