使用 VBA 将控件添加到 Excel 用户窗体中的框架

Question

我需要动态创建标签和按钮，然后将它们添加到用户窗体内的框架中。 我该怎么做呢？ 看起来应该比实际情况更容易。

Answer 1

下面的代码演示了如何使用控件动态填充用户窗体中的框架...

在我使用的窗体中，我有一个名为 Frame1 的框架控件，因此在 UserForm_Initialize 中，您可以调用 Frame1.Controls.Add 将控件嵌入到框架中。 您可以设置返回到您在 UserForm 代码模块中定义的 WithEvents 控制变量的控件，以便您可以响应您想要的任何控件上的事件...

因此，使用此方法，您需要预先编写您想要的任何事件代码想要您创建的任何控件...

另请注意，即使顶部、左侧、宽度和高度属性不一定出现在智能感知中，您也可以定位控件并调整其大小...

Private WithEvents Cmd As MSForms.CommandButton
Private WithEvents Lbl As MSForms.Label

Private Sub UserForm_Initialize()
    Set Lbl = Frame1.Controls.Add("Forms.Label.1", "lbl1")
    Lbl.Caption = "Foo"
    Set Cmd = Frame1.Controls.Add("Forms.CommandButton.1", "cmd1")
End Sub

Private Sub Cmd_Click()
    Cmd.Top = Cmd.Top + 5
End Sub

Private Sub Lbl_Click()
    Lbl.Top = Lbl.Top + 5
End Sub

Answer 2

添加方法

要将控件添加到用户窗体或框架，您可以使用添加方法。

SetControl = object.Add(ProgID [, Name [, Visible ]] )

第一个参数将引用您要添加的控件类型，它是 ProgID其定义为

程序化标识符。 不带空格的文本字符串，用于标识对象类。 ProgID 的标准语法是...ProgID 映射到类标识符 (CLSID)。

功能性解决方案

为了使这个过程更容易，让我们使用枚举来帮助我们管理各种控件。

' List of all the MSForms Controls.
Public Enum MSFormControls
    CheckBox
    ComboBox
    CommandButton
    Frame
    Image
    Label
    ListBox
    MultiPage
    OptionButton
    ScrollBar
    SpinButton
    TabStrip
    TextBox
    ToggleButton
End Enum

有了这个枚举，我们现在可以轻松创建一个函数来获取所有控件的 ProgID 字符串。

' Gets the ProgID for each individual control. Used to create controls using `Object.add` method.
' @see https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/add-method-microsoft-forms
Public Function GetMSFormsProgID(control As MSFormControls) As String
    Select Case control
      Case MSFormControls.CheckBox:       GetMSFormsProgID = "Forms.CheckBox.1"
      Case MSFormControls.ComboBox:       GetMSFormsProgID = "Forms.ComboBox.1"
      Case MSFormControls.CommandButton:  GetMSFormsProgID = "Forms.CommandButton.1"
      Case MSFormControls.Frame:          GetMSFormsProgID = "Forms.Frame.1"
      Case MSFormControls.Image:          GetMSFormsProgID = "Forms.Image.1"
      Case MSFormControls.Label:          GetMSFormsProgID = "Forms.Label.1"
      Case MSFormControls.ListBox:        GetMSFormsProgID = "Forms.ListBox.1"
      Case MSFormControls.MultiPage:      GetMSFormsProgID = "Forms.MultiPage.1"
      Case MSFormControls.OptionButton:   GetMSFormsProgID = "Forms.OptionButton.1"
      Case MSFormControls.ScrollBar:      GetMSFormsProgID = "Forms.ScrollBar.1"
      Case MSFormControls.SpinButton:     GetMSFormsProgID = "Forms.SpinButton.1"
      Case MSFormControls.TabStrip:       GetMSFormsProgID = "Forms.TabStrip.1"
      Case MSFormControls.TextBox:        GetMSFormsProgID = "Forms.TextBox.1"
      Case MSFormControls.ToggleButton:   GetMSFormsProgID = "Forms.ToggleButton.1"
    End Select
End Function

最后，让我们创建一个使用新函数添加到表单或框架的函数。

' Easly add control to userform or a frame.
' @returns {MSForms.control} The control that was created
Public Function AddControl(userformOrFrame As Object _
                         , control As MSFormControls _
                         , Optional name As String = vbNullString _
                         , Optional visible As Boolean = True _
                        ) As MSForms.control
    Set AddControl = userformOrFrame.Controls.Add(GetMSFormsProgID(control), name, visible)
End Function

使用这样的枚举的美妙之处在于，我们现在对所有控件都有智能感知，而不必记住它们。

演示

为了演示它，我们可以通过循环枚举将每个控件添加到空白用户窗体。

Private Sub UserForm_Initialize()
    demoAddingControlsToUserform
End Sub

Private Sub demoAddingControlsToUserform()
    ' Offset used to prevent controls
    ' overlapping as well as provide
    ' a height for the scrollbars
    Dim offsetHeight As Double
    
    ' Add each control to the userform
    ' and set top to make sure they are not overlapping
    ' (Although this looks odd, you can actually loop enums this way.)
    Dim control As MSFormControls
    For control = CheckBox To ToggleButton
        With AddControl(Me, control)
            .Top = offsetHeight
            offsetHeight = offsetHeight + .Height
        End With
    Next
    
    ' Show scrollbars and adjust the height to show
    ' all the added controls.
    With Me
        .ScrollBars = fmScrollBarsVertical
        .ScrollHeight = offsetHeight + 20
    End With
End Sub

Answer 3

我对上述主题的变体。 但这仅适用于 4x4 的按钮阵列。 创建一个用户表单并将其添加到其代码中。 相同的概念可以用于您的标签（或参见前面的答案）：

Private cmdLots(20) As MSForms.CommandButton

Private Sub UserForm_Initialize()
For i = 1 To 4
For j = 1 To 4
    k = i + (4 * j)
    Set cmdLots(k) = UserForm2.Controls.Add("Forms.CommandButton.1", "cmd1")
    With cmdLots(k)
        .Top = i * 25
        .Left = (j * 80) - 50
        .BackColor = RGB(50 * i, 50 * j, 0)
        .Caption = "i= " & i & "  j= " & j
    End With
Next j
Next i
End Sub