尝试将文本框动态添加到用户表单？ [英] Trying to add Textboxes to a userform dynamically?

问题描述

我在Excel工作簿中有代码，可以帮助我创建大量电子邮件，发送给各种程序的用户。我有一个弹出的用户表单，用户填写所有需要的信息。但这一次只适用于一款应用程序。是否有人可以与我共享代码，以便根据勾选的复选框将文本框动态添加到用户表单？

在第一个框架中，我有指示受影响的应用程序的复选框，在第二个框架中，我有选项按钮来描述事件的类型，然后我希望文本框根据勾选的内容显示。

非常感谢任何指导，因为我认为目前这对我来说太深刻了

我对此代码进行了反向工程，它添加了我想要的框，但我需要能够用单元格数据填充它们，然后在电子邮件中使用它：

Option Explicit

Dim SpnColct As Collection

Private Sub CommandButton2_Click()

    Dim cSpnEvnt As cControlEvent
    Dim ctlSB As Control
    Dim ctlTXT As Control
    Dim lngCounter As Long

    For lngCounter = 1 To 7
        Set ctlTXT = Me.Frame7.Controls.Add("Forms.TextBox.1", "Text" & lngCounter)
        ctlTXT.Name = "Text" & lngCounter
        ctlTXT.Left = 5
        ctlTXT.Height = 125: ctlTXT.Width = 280
        ctlTXT.Top = (lngCounter - 1) * 125 + 2

        Set cSpnEvnt = New cControlEvent
        Set cSpnEvnt.SP = ctlSB
        Set cSpnEvnt.TXT = ctlTXT
        SpnColct.Add cSpnEvnt

    Next lngCounter

    Me.Frame1.ScrollHeight = (lngCounter - 1) * 17 + 2

End Sub

这已添加到类模块：

Option Explicit

Public WithEvents SP As MSForms.SpinButton
Public WithEvents TXT As MSForms.TextBox

Private Sub SP_SpinDown()
    SP.Value = SP.Value - 1
    MsgBox "Spin Down to " & SP.Value
End Sub

Private Sub SP_SpinUp()
    SP.Value = SP.Value + 1
    MsgBox "Spin Up to " & SP.Value
End Sub

Private Sub TXT_Change()
    MsgBox "You changed the value."
End Sub

推荐答案

已更新这将是一个很长的一步，看您是否理解它。我已将其更改为在CheckBox_Click事件上创建文本框，但如果您愿意，也可以更改为命令按钮。如果超过这个范围，我认为您需要开始一个新问题。

我最近一直在做类似的事情，我发现出现问题的原因是由于加载对象的顺序。不幸的是，我现在找不到解释它的链接(如果可以的话，我会更新)，但为了能够实现这一点，你需要一个额外的Class来加载对象，否则Userform就看不到它们了。这就是我想出的那种解决方案(用你的例子)

用户表单：

Option Explicit
Private WithEvents cControls As EventController

Private Sub cControls_Click(ctrl As CheckBoxControl)
    Dim tBox As TextBoxControl
    Dim i As Long
    Dim NextTop As Long, FrameHeight As Long

    For i = 1 To cControls.GetControls.Count
        Debug.Print TypeName(cControls.GetControl(i))
        If TypeName(cControls.GetControl(i)) = "TextBoxControl" Then
            Set tBox = cControls.GetControl(i)
            If tBox.TXT.Parent Is Me.Frame7 Then
                NextTop = tBox.Top + tBox.Height
            End If
        End If
    Next i

    Set tBox = cControls.AddTextBox
    With tBox
        .Height = 125
        .Width = 280
        .Left = 5
        .Top = NextTop
        .TXT.Text = ctrl.cBox.Caption
        FrameHeight = NextTop + .Height
    End With
    If FrameHeight > Me.Frame7.InsideHeight Then
        With Me.Frame7
            .ScrollBars = fmScrollBarsVertical
            .ScrollHeight = FrameHeight
            .Scroll yAction:=6
        End With
    End If

End Sub

Private Sub UserForm_Initialize()
    Dim i As Long
    Dim cBox As CheckBoxControl
    Set cControls = New EventController
    ' This can be set to a userform or a frame
    Set cControls.UserForm = Me
    For i = 1 To 8
        Set cBox = cControls.AddCheckBox
        cBox.cBox.Left = 5
        With cBox.cBox
            .Top = 5 + (i - 1) * .Height
            .Caption = IIf(i = 8, "App Unknown", "App " & i)
        End With
    Next i

End Sub

Private Sub cControls_Change(ctrl As TextBoxControl)
    ' This can be handled in the class instead as you were - just doing it in the userform to show the exposing of the event
    MsgBox ctrl.TXT.Name & " Change"
End Sub

Private Sub cControls_SpinDown(ctrl As TextBoxControl)
    ' This can be handled in the class instead as you were - just doing it in the userform to show the exposing of the event
    With ctrl.SP
        If .Value >0 Then
            .Value = .Value - 1
        End If
    End With
    MsgBox ctrl.SP.Name & " Spin Down"
End Sub

Private Sub cControls_SpinUp(ctrl As TextBoxControl)
' This can be handled in the class instead as you were - just doing it in the userform to show the exposing of the event
    With ctrl.SP
        .Value = .Value + 1
    End With
    MsgBox ctrl.SP.Name & " Spin Up"

End Sub

类-需要以粗体命名

事件控制

Option Explicit

Private CtrlCollection As Collection
Private cUserForm As UserForm1

Public Event SpinDown(ctrl As TextBoxControl)
Public Event SpinUp(ctrl As TextBoxControl)
Public Event Change(ctrl As TextBoxControl)
Public Event Click(ctrl As CheckBoxControl)
Public Property Set UserForm(v As UserForm1)
    Set cUserForm = v
End Property
Public Property Get UserForm() As UserForm1
    Set UserForm = cUserForm
End Property
Public Function AddTextBox() As TextBoxControl
    Dim tBox As TextBoxControl
    Set tBox = New TextBoxControl

    tBox.Initialize Me

    CtrlCollection.Add tBox

    Set AddTextBox = tBox

End Function
Public Function AddCheckBox() As CheckBoxControl
    Dim cBox As New CheckBoxControl
    cBox.Initalize Me
    CtrlCollection.Add cBox

    Set AddCheckBox = cBox
End Function
Public Function GetControl(Index As Long)
    Set GetControl = CtrlCollection(Index)
End Function
Public Function GetControls() As Collection
    Set GetControls = CtrlCollection
End Function
Private Sub Class_Initialize()
    Set CtrlCollection = New Collection
End Sub
Public Sub SpinDown(ctrl As TextBoxControl)
    RaiseEvent SpinDown(ctrl)
End Sub
Public Sub SpinUp(ctrl As TextBoxControl)
    RaiseEvent SpinUp(ctrl)
End Sub
Public Sub Change(ctrl As TextBoxControl)
    RaiseEvent Change(ctrl)
End Sub
Public Sub Click(ctrl As CheckBoxControl)
    RaiseEvent Click(ctrl)
End Sub

CheckBox Control

Option Explicit

Public WithEvents cBox As MSForms.CheckBox
Private cParent As EventController
Public Property Set Parent(v As EventController)
    Set cParent = v
End Property
Public Property Get Parent() As EventController
    Set Parent = cParent
End Property
Public Sub Initalize(Parent As EventController)
    Set Me.Parent = Parent
    Set cBox = Parent.UserForm.Frame1.Controls.Add("Forms.CheckBox.1")
End Sub

Private Sub cBox_Click()
    Parent.Click Me
End Sub

TextBoxControl

Option Explicit

Public WithEvents SP As MSForms.SpinButton
Public WithEvents TXT As MSForms.TextBox
Private cParent As EventController
Public Sub Initialize(Parent As EventController)
    Set Me.Parent = Parent
    With Parent.UserForm.Frame7.Controls
        Set SP = .Add("Forms.SpinButton.1")
        Set TXT = .Add("Forms.TextBox.1")
    End With
End Sub
Public Property Set Parent(v As EventController)
    Set cParent = v
End Property
Public Property Get Parent() As EventController
    Set Parent = cParent
End Property
Public Property Let Left(v As Single)
    TXT.Left = v
    SP.Left = TXT.Left + TXT.Width
End Property
Public Property Get Left() As Single
    Left = TXT.Left
End Property
Public Property Let Top(v As Single)
    TXT.Top = v
    SP.Top = v
End Property
Public Property Get Top() As Single
    Top = TXT.Top
End Property
Public Property Let Height(v As Single)
    TXT.Height = v
    SP.Height = v
End Property
Public Property Get Height() As Single
    Height = TXT.Height
End Property
Public Property Let Width(v As Single)
    TXT.Width = v - SP.Width
    SP.Left = TXT.Left + TXT.Width
End Property
Public Property Get Width() As Single
    Width = TXT.Width + SP.Width
End Property

Public Sub SP_SpinDown()
    Parent.SpinDown Me
'    SP.Value = SP.Value - 1
'    MsgBox "Spin Down to " & SP.Value
End Sub
' The commented out lines below you can either leave in here, or handle in the Userform
Public Sub SP_SpinUp()
    Parent.SpinUp Me
'    SP.Value = SP.Value + 1
'    MsgBox "Spin Up to " & SP.Value
End Sub

Public Sub TXT_Change()
    Parent.Change Me
'    MsgBox "You changed the value."
End Sub

问题源于加载Userform时未加载控件，因此Userform没有注册它们是具有Event的对象。通过使用中间类，Userform识别出该类具有Event，并且我们在Userform的初始化时静态地加载它。然后，我们可以向Class添加任何我们想要的Controls，Userform将处理它们。

演示：

这篇关于尝试将文本框动态添加到用户表单？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！