VBA运行时动态创建控件并添加事件处理代码

分类:代码, 博客 标签:

在VBA编写的程序中，如果可以在运行时动态创建控件无疑是非常有用的，因为有些控件是无法预先得知的，这样就更必须在运行时动态创建然后设置相应控件属性。这里CnHUP给出一个示例，可供参考： 

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 "
End Sub

注：不少人上面代码无法运行是因为没有注意到事件的名称必须与添加的控件的名称一致。

灵活性更好的使用VBA类的实现动态创建控件方式

首先创建类模块且指定类名称为CButtonEvent，类模块内部编写代码：

'================================
' 动态创建控件及事件的辅助类
' CButtonEvent
' 
'================================
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

原创文章如转载，请注明：
转载自水文工具集 [ ]
联系QQ：39515976

分类:代码, 博客 标签:

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

  1. fasling  on 十二月 6th, 2010

    请问在Access里，有办法实现动态创建控件吗？

    • CnHUP  on 十二月 7th, 2010

      Access不是很清楚哦，不过从网上查了些资料，好像是对于设计时创建才可以的，在MSDN找到一段代码：

      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.
    DoCmd.Restore
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.