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
'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
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