Monday 12 October 2015

Conditional formatting by control type

Ever wanted to apply formatting to all the controls of a form at the same time? Easy! All the examples shown are running from the form's Open event, but could just as easily be applied to the Current event if you want different formatting for different records. The first example simply disables all the controls by setting their Enabled properties to False:

Private Sub Form_Open(Cancel As Integer)
On Error Resume Next 'In case some controls without the relevant property are considered
Dim ctl As Control
For Each ctl In Me.Controls
    ctl.Enabled = False
Next ctl
End Sub

This is fine but has an ugly method of handling those controls that don't support the property you want to modify, i.e. it justs skips the error. Wouldn't it be nicer to just reformat relevant controls? The ControlType property lets you do this, as in the following example which disables all controls except Labels:

Private Sub Form_Open(Cancel As Integer)
Dim ctl As Control
For Each ctl In Me.Controls
    'ControlType constants you can use
    'acLabel - Label
    'acRectangle - Rectangle
    'acLine - Line
    'acImage - Image
    'acCommandButton - Command button
    'acOptionButton - Option button
    'acCheckBox - Check box
    'acOptionGroup - Option group
    'acBoundObjectFrame - Bound object frame
    'acTextBox - Text box
    'acListBox - List box
    'acComboBox - Combo box
    'acSubform - SubForm / SubReport
    'acObjectFrame - Unbound object frame or chart
    'acPageBreak - Page break
    'acPage - Page
    'acCustomControl - ActiveX (custom) control
    'acToggleButton - Toggle button
    'acTabCtl - Tab
    ctl.Enabled = (ctl.ControlType <> acLabel)
Next ctl
End Sub

Which is all well and good except what happens if you want to format an eclectic selection of controls of different types? Well you could use the control's Name property to uniquely identify each one and scroll through them all but how laborious is that if you have lots of controls to do this for? Plus if you make changes to the form, adding or removing controls, you have to change your code too. I prefer to make use of each control's Tag property, as in the following example which disables all controls with a Tag property of "X":

Private Sub Form_Open(Cancel As Integer)
Dim ctl As Control
For Each ctl In Me.Controls
    ctl.Enabled = (ctl.Tag <> "X")
Next ctl

No comments:

Post a Comment