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

One thought on “DisableControls

  1. I’ve never used tags before but this is a brilliant idea which I’m going to implement everywhere I can think of now – thanks!

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published. Required fields are marked *