分类:代码, 博客 标签:


Private WithEvents cmdHUP As MsForms.CommandButton

Private Sub UserForm_Click()
    Set cmdHUP = Controls.Add("Forms.CommandButton.1", _
                                "cmdHUP", True)

    With cmdHUP
        .Left = 10
        .Top = 100
        .Width = 175
        .Height = 20
        .Caption = "CnHUP"
    End With
End Sub

Public Sub cmdHUP_Click()
    MsgBox "welcome to http://www.cnhup.com"
End Sub




' 动态创建控件及事件的辅助类
' CButtonEvent
' http://www.cnhup.com
Private WithEvents m_CB As MSForms.CommandButton

Public Sub Init(ctl As MSForms.CommandButton)
    Set m_CB = ctl
End Sub

Private Sub m_CB_Click()
    MsgBox m_CB.Name
End Sub

Private Sub Class_Terminate()
    Set m_CB = Nothing
End Sub


Private colCB As New Collection

Private Sub CommandButton1_Click()
    Dim nCtr As MSForms.CommandButton
    Dim ctlCB As CButtonEvent
    Dim i As Integer
    For i = 1 To 3
        ' 添加按钮控件
        Set nCtr = Me.Controls.Add("Forms.CommandButton.1", "cmdTest" & i)
        ' 设置按钮控件标题和位置
        With nCtr
            .Caption = "CommandButton_" & i
            .Move 10, 30 + (i - 1) * 40, 80, 30
        End With
        ' 创建cCB类实例
        Set ctlCB = New CButtonEvent
        ' 将控件赋给类实例
        ctlCB.Init nCtr
        colCB.Add ctlCB
    Next i
End Sub

分类:代码, 博客 标签:

2 Responses to “VBA运行时动态创建控件并添加事件处理代码”

  1. fasling  on 十二月 6th, 2010


    • CnHUP  on 十二月 7th, 2010


      Sub NewControls()
          Dim frm As Form
          Dim ctlLabel As Control, ctlText As Control
          Dim intDataX As Integer, intDataY As Integer
          Dim intLabelX As Integer, intLabelY As Integer
          ' Create new form with Orders table as its record source.
          Set frm = CreateForm
          frm.RecordSource = "Orders"
          ' Set positioning values for new controls.
          intLabelX = 100
          intLabelY = 100
          intDataX = 1000
          intDataY = 100
          ' Create unbound default-size text box in detail section.
          Set ctlText = CreateControl(frm.Name, acTextBox, , "", "", _
              intDataX, intDataY)
          ' Create child label control for text box.
          Set ctlLabel = CreateControl(frm.Name, acLabel, , _
               ctlText.Name, "NewLabel", intLabelX, intLabelY)
          ' Restore form.
      End Sub


      Controls can be added to a Form in VBA using Application.CreateControl but only when the Form is opened in Design View.

      Therefore you would have to open the form in design view, add your control and then close the form. Too many instances of this would be a heavy load on the database.

      Normally you would just add all the controls you might need to the form and toggle them visible or invisible depending on your needs.”


You must be logged in to post a comment.