Daily Dose of Excel

Many times when I’m working with UserForm controls, I need to disable certain controls based on the state of the userform. This sub allows me to disable controls based on their Tag property without having to hard code the control names.

Sub DisableControls(ByRef ufForm As UserForm, _
    Optional ByVal sTag As String, _
    Optional ByVal sType As String, _
    Optional ByVal bEnableAll As Boolean = False)
    
    ‘Disables controls on a userform based on thier
    ‘tag information.
    ‘ufForm is the userform that contains the controls.
    ’sTag is the Tag for the controls that will be disabled.
    ‘    All other controls will be enabled.  If sTag is omitted,
    ‘    all controls will be enabled.
    ’sType is the TypeName of the controls to be disabled.  If
    ‘    omitted, all types of controls will be subject to disabling.
    ‘bEnableAll enables all controls regardless of other arguments
    
    Dim oCtl As Control
    
    If Len(sTag) = 0 Then sTag = “*”
    If Len(sType) = 0 Then sType = “*”
    
    For Each oCtl In ufForm.Controls
        oCtl.Enabled = True
        If Not bEnableAll Then
            If oCtl.Tag Like sTag And TypeName(oCtl) Like sType Then
                oCtl.Enabled = False
            End If
        End If
    Next oCtl
    
End Sub

Some usage examples (the Me keyword is used to refer to the userform where this code resides):

DisableControls Me, , , True ‘enables all
DisableControls Me ‘disables all
DisableControls Me, “MyState” ‘disables all controls with a Tag of MyState
DisableControls UserForm1, “MyState”, “CommandButton” ‘disables commandbuttons
                                                      ‘with a Tag of MyState