DisableControls

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