VBA Userform：根据ComboBox选择禁用/恢复控件并修改背景色

问题根源 #

你当前代码的问题主要有三点：

• 数组类型声明错误：用了String类型数组存储控件对象，导致后续操作报错
• 直接给数组整体设置属性：VBA不支持对控件数组批量赋值属性，必须逐个操作
• 未实现控件属性恢复逻辑：选择"System"时没有对应代码还原控件状态

修正后的完整代码 #

Private Sub cboOrderType_Change()
    Dim selectedType As String
    Dim controlArr As Variant
    Dim ctrl As Control
    
    selectedType = Me.cboOrderType.Value
    ' 把需要批量操作的控件存入数组，注意每个控件前加Me.确保是当前窗体的控件
    controlArr = Array(Me.txtApprovedQuote, Me.txtPMAssign, Me.txtSOATeam, Me.txtFinance, _
                       Me.txtApproved, Me.txtSOACust, Me.txtSOAE10, Me.txtInvPaid, _
                       Me.txtShipReq, Me.txtBOL, Me.txtQuote, Me.txtBMTH, Me.txtTransOrdNum, _
                       Me.txtDiamond, Me.cboTE, Me.txtPM, Me.txtEE, Me.cboSCodeDes, _
                       Me.txtSCode, Me.txtSys, Me.cboShipVia, Me.cboShipType, _
                       Me.cboShipChrgs, Me.txtShipInst, Me.txtCost, Me.txtMargin, Me.txtLT)
    
    Select Case selectedType
        Case "Service", "Repair"
            ' 遍历数组，逐个禁用控件并设置灰化背景
            For Each ctrl In controlArr
                ctrl.Enabled = False
                ctrl.BackColor = &HE0E0E0
            Next ctrl
        Case "System"
            ' 遍历数组，恢复控件启用状态和默认背景色
            For Each ctrl In controlArr
                ctrl.Enabled = True
                ' 白色是TextBox和ComboBox的默认背景色，若你的控件默认色不同可替换
                ctrl.BackColor = &HFFFFFF
            Next ctrl
        Case Else
            ' 其他选项默认恢复控件正常状态，可根据需求调整
            For Each ctrl In controlArr
                ctrl.Enabled = True
                ctrl.BackColor = &HFFFFFF
            Next ctrl
    End Select
End Sub

关键修改说明 #

• 数组类型调整：改用Variant类型数组存储控件对象，这是VBA中存储对象集合的正确方式
• 逐个操作控件：通过For Each循环遍历数组里的每个控件，分别设置属性，解决"invalid qualifier"错误
• 简化条件判断：用Select Case替代多个If...ElseIf，逻辑更清晰
• 恢复默认状态：选择"System"时，将控件设为启用状态，并还原背景色为默认白色

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