VBA/Excel/Access/Word/Forms/Form Control
Содержание
- 1 change control back ground color
- 2 Change fonts of controls on a form
- 3 Controls and Errors
- 4 Control Types
- 5 Determining the Type of a Control
- 6 Intrinsic Constant Type of Control
- 7 Manipulating a Single Control
- 8 The For Each...Next statement executes a group of statements on each member of an array or collection
- 9 The With...End With statement executes a series of statements on a single object or user-defined type
- 10 With...End With construct is often used along with the For Each...Next construct
change control back ground color
<source lang="vb">
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
</source>
Change fonts of controls on a form
<source lang="vb"> 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 </source>
Controls and Errors
<source lang="vb"> 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
</source>
Control Types
<source lang="vb">
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
</source>
Determining the Type of a Control
<source lang="vb">
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
</source>
Intrinsic Constant Type of Control
<source lang="vb">
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
</source>
Manipulating a Single Control
<source lang="vb">
Sub FormCaptions()
Dim frm As Form For Each frm In Forms frm.Caption = frm.Caption & " - " & CurrentUser Next frm
End Sub
</source>
The For Each...Next statement executes a group of statements on each member of an array or collection
<source lang="vb">
Private Sub cmdForEachNext_Click()
Dim ctl As Control For Each ctl In Controls ctl.FontSize = 8 Next ctl
End Sub
</source>
The With...End With statement executes a series of statements on a single object or user-defined type
<source lang="vb">
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
</source>
With...End With construct is often used along with the For Each...Next construct
<source lang="vb">
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
</source>