VBA/Excel/Access/Word/Forms/Form Control

change control back ground color

 
Sub Form_Current()
    Dim ctl as Control
    For Each ctl In Controls
        If TypeOf ctl Is TextBox Then
            If IsNull(ctl.Value) Then
                ctl.BackColor = vbCyan
            Else
                ctl.BackColor = vbWhite
            End If
        End If
    Next ctl
End Sub

Change fonts of controls on a form

 
    Sub FormFonts (strFont As String)
       Dim frmCurrent As Form
       Dim ctlControl As Control
    
       For Each frmCurrent In Forms
          For each ctlControl In frmCurrent.Controls
             ctlControl.FontName = strFont
          Next
        Next
     
     End Sub

Controls and Errors

 
    Sub FormFonts(strFont As String)
       On Error GoTo FormFonts_Err
       Dim frmCurrent As Form
       Dim ctlControl As Control
    
       For Each frmCurrent In Forms
          For Each ctlControl In frmCurrent.Controls
             ctlControl.FontName = strFont
           Next
        Next
     
FormFonts_Exit:
        Exit Sub
     
FormFonts_Err:
        If Err.Number = 438 Then
           Resume Next
        Else
           MsgBox Err.Description
           Resume FormFonts_Exit
        End If
End Sub

Control Types

 
Sub main()
   For Each frmCurrent In Forms
      For Each ctlControl In frmCurrent.Controls
         If ctlControl.Type <> acCheckBox Then
            ctlControl.font = strFont
         End If
      Next
   Next
End Sub

Determining the Type of a Control

 
Sub FlipEnabled(frmAny As Form, ctlAny As Control)
    Dim ctl As Control
    ctlAny.Enabled = True
    ctlAny.SetFocus
    For Each ctl In frmAny.Controls
        If ctl.ControlType = acCommandButton Then
            If ctl.Name <> ctlAny.Name Then
                ctl.Enabled = Not ctl.Enabled
            End If
        End If
    Next ctl
End Sub

Intrinsic Constant Type of Control

 
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

Manipulating a Single Control

 
Sub FormCaptions()
    Dim frm As Form
    For Each frm In Forms
        frm.Caption = frm.Caption & " - " & CurrentUser
    Next frm
End Sub

The For Each...Next statement executes a group of statements on each member of an array or collection

 
Private Sub cmdForEachNext_Click()
    Dim ctl As Control
    For Each ctl In Controls
        ctl.FontSize = 8
    Next ctl
End Sub

The With...End With statement executes a series of statements on a single object or user-defined type

 
Private Sub cmdWithEndWith_Click()
   With Me.txtAge " Your Text Field"s name
      .BackColor = 16777088
      .ForeColor = 16711680
      .Value = "Hello World"
      .FontName = "Arial"
   End With
End Sub

With...End With construct is often used along with the For Each...Next construct

 
Private Sub cmdForEachWith_Click()
    Dim ctl As Control
    For Each ctl In Controls
        With ctl
            .ForeColor = 16711680
            .FontName = "Arial"
            .FontSize = 14
        End With
    Next ctl
End Sub