使用VBA批量修改Access窗体：如何增删命令按钮控件

我有一个包含数百个窗体的旧Access数据库前端，已通过VBA在设计视图打开窗体并修改字体、颜色等属性，但无法实现创建或删除命令按钮这类控件，需要相关技术方案。现有属性修改代码如下：

Public Sub ChgFormProps(Optional FormName As String, Optional ControlName As String)
Dim db As Database, Con As Container, Doc As Document
Dim frm As Form, ctl As Control, FormChgd As Boolean

Dim proNum As String, EM As errInfo: proNum = "001"
On Error GoTo ErrorHandler:

FormChgd = False

Debug.Print "Changed Forms:"
Set db = CurrentDb
For Each Con In db.Containers
    For Each Doc In Con.Documents
        If Con.Name = "Forms" Then
            If FormName = Doc.Name Or FormName = "" Then
                DoCmd.OpenForm Doc.Name, acDesign
                Set frm = Screen.ActiveForm

                    For Each ctl In frm.Controls

                        If ControlName = ctl.Name Or ControlName = "" Then
                            Select Case ctl.ControlType
                                Case acComboBox
                                    ctl.FontName = "Segoe UI"
                                    FormChgd = True
                                Case acImage
            
                                Case acTabCtl
                                    ctl.FontName = "Segoe UI"
                                    FormChgd = True
                                Case acListBox
                                    ctl.FontName = "Segoe UI"
                                    FormChgd = True
                                Case acTextBox
                                    ctl.FontName = "Segoe UI"
                                    FormChgd = True
                                    
                                    If ctl.Locked = True Then
                                        ctl.BackColor = -2147483626
                                        FormChgd = True
                                    Else
                                        ctl.BackColor = WHITE
                                        FormChgd = True
                                    End If
                         
                                Case acCommandButton, acToggleButton
                                    ctl.FontName = "Segoe UI"
                                    If ctl.Name = "HelpCmd" Or ctl.Name = "CloseCmd" Then
                                        'do nothing
                                    Else
                                        ctl.FontName = "Segoe UI"
                                        ctl.ForeColor = BLACK
                                        ctl.BackColor = WHITE
                                        ctl.HoverColor = BLUE
                                        ctl.HoverForeColor = WHITE
                                    End If

                                    FormChgd = True
                                    
                                Case acCheckBox
                                
                                Case acLabel
                                    ctl.FontName = "Segoe UI"
                                    ctl.ForeColor = 0
                                    FormChgd = True
                                    
                                    If ctl.Caption = "DELETE" Then
                                        Stop
                                    End If
   
                                Case acLine

                            End Select

                        End If
                    Next
       

                
                If FormChgd Then
                    DoCmd.Close acForm, Doc.Name, acSaveYes
                    Debug.Print Doc.Name
                    FormChgd = False
                Else
                    DoCmd.Close acForm, Doc.Name, acSaveNo
                End If
            End If
        End If
    Next
Next

ExitHere:
    Exit Sub

ErrorHandler:
    Select Case Err
        Case 2462
            Resume Next
        Case Else
            EM = ErrMsg(Err, objNum, proNum)
            fMsgBox EM.Msg, vbCritical, EM.Title
            Resume ExitHere
    End Select

End Sub

解决方案 #

1. 删除指定命令按钮 #

可在遍历控件后单独处理删除（避免遍历集合时修改集合引发错误）：

' 放在遍历完frm.Controls的循环之后
Dim ctlToDelete As Control
' 处理控件不存在的情况
On Error Resume Next
Set ctlToDelete = frm.Controls("ObsoleteCmd") ' 替换为要删除的按钮名称
On Error GoTo ErrorHandler
If Not ctlToDelete Is Nothing Then
    frm.Controls.Remove ctlToDelete.Name
    FormChgd = True
    Set ctlToDelete = Nothing
End If

2. 创建新的命令按钮 #

使用CreateControl函数在设计视图窗体上添加按钮，可自定义位置、样式及事件：

' 放在遍历完frm.Controls的循环之后
Dim newCmd As Control
' 避免重复创建同名控件
On Error Resume Next
Set newCmd = frm.Controls("NewActionCmd") ' 新按钮的名称
On Error GoTo ErrorHandler
If newCmd Is Nothing Then
    ' 参数：窗体名、控件类型、父容器区域、左坐标、上坐标、宽度、高度
    Set newCmd = CreateControl(Doc.Name, acCommandButton, acDetail, , , 1000, 1000, 1500, 400)
    ' 设置按钮属性
    newCmd.Name = "NewActionCmd"
    newCmd.Caption = "执行操作"
    newCmd.FontName = "Segoe UI"
    newCmd.ForeColor = BLACK
    newCmd.BackColor = WHITE
    ' 绑定点击事件（可选，可直接指定VBA代码）
    newCmd.OnClick = "MsgBox ""按钮被触发"""
    FormChgd = True
End If
Set newCmd = Nothing

关键注意事项 #

• 所有控件操作必须在设计视图下执行（已通过DoCmd.OpenForm Doc.Name, acDesign实现）
• 操作后需将FormChgd设为True，确保窗体修改被保存
• 加入错误处理，避免因控件不存在、权限不足等问题中断批量任务
• 创建控件时需指定正确的父容器（示例中为窗体的acDetail主体区域）

内容的提问来源于stack exchange，提问作者Kwixster