VBA/Excel/Access/Word/Forms/Form
Содержание
- 1 Adding Controls to a UserForm at Design Time
- 2 Cancels a cancelable event (an event that has a Cancel parameter).
- 3 Change caption
- 4 Close a form
- 5 Creating UserForms Programmatically
- 6 Display form in Modal mode
- 7 Format fonts
- 8 Form count
- 9 Loading a Form into Memory Prior to Displaying It
- 10 Open a form, format the fonts and close it
- 11 Output form to html file
- 12 To remove the Userform from the computer"s memory, you must use the Unload statement; otherwise, the Userform is only hidden.
- 13 Using the Show Method to Display a Form
Adding Controls to a UserForm at Design Time
<source lang="vb">
Sub RunTimeButton()
Dim Butn As CommandButton Set Butn = UserForm1.Controls.Add("Forms.rumandButton.1") With Butn .Caption = "Added at runtime" .Width = 100 .Top = 10 End With UserForm1.Show
End Sub
</source>
Cancels a cancelable event (an event that has a Cancel parameter).
<source lang="vb">
Private Sub Form_BeforeUpdate(Cancel As Integer)
If MsgBox("Are you sure you want to save changes to this record?", _ vbYesNo Or vbInformation, "Confirm Upate") = vbNo Then DoCmd.CancelEvent End If
End Sub
</source>
<source lang="vb">
Sub ChangeCaption(frmAny As Form)
If IsNull(frmAny.Caption) Then frmAny.Caption = "Form For - " & CurrentUser Else frmAny.Caption = frmAny.Caption & " - " & CurrentUser End If
End Sub
</source>
Close a form
<source lang="vb">
Private Sub cmdClose_Click()
DoCmd.Close acForm, Me.Name
End Sub
</source>
Creating UserForms Programmatically
<source lang="vb">
Sub MakeForm()
Dim TempForm As Object Dim NewButton As Msforms.rumandButton Dim Line As Integer Application.VBE.MainWindow.Visible = False Set TempForm = ThisWorkbook.VBProject. _ VBComponents.Add(3) "vbext_ct_MSForm With TempForm .Properties("Caption") = "Form" .Properties("Width") = 200 .Properties("Height") = 100 End With Set NewButton = TempForm.Designer.Controls _ .Add("forms.rumandButton.1") With NewButton .Caption = "Click Me" .Left = 60 .Top = 40 End With With TempForm.CodeModule Line = .CountOfLines .InsertLines Line + 1, "Sub CommandButton1_Click()" .InsertLines Line + 2, " MsgBox ""Hello!""" .InsertLines Line + 3, " Unload Me" .InsertLines Line + 4, "End Sub" End With VBA.UserForms.Add(TempForm.name).Show ThisWorkbook.VBProject.VBComponents.Remove TempForm
End Sub
</source>
Display form in Modal mode
<source lang="vb">
Sub cmdGetUserForm_Click()
frmStats.Show vbModal
End Sub
</source>
Format fonts
<source lang="vb">
Sub PermanentFormFonts (strFont As String)
On Error GoTo PermanentFormFonts_Err Dim objAO As AccessObject Dim objCP As Object Dim ctlControl As Control Set objCP = Application.CurrentProject For Each objAO In objCP.AllForms DoCmd.OpenForm objAO.Name, acDesign, , , , acHidden For Each ctlControl In objAO.Controls ctlControl.FontName = strFont Next DoCmd.Close acForm, objAO.Name, acSaveYes Next
PermanentFormFonts_Exit:
Exit Sub
PermanentFormFonts_Err:
If Err.Number = 438 Then Resume Next Else MsgBox Err.Description Resume PermanentFormFonts_Exit End If
End Sub
</source>
Form count
<source lang="vb">
Sub exaFormsContainer()
Dim db As Database Dim frm As Form Dim doc As Document Set db = CurrentDb Debug.Print "Opened form count: " & Forms.Count For Each frm In Forms Debug.Print frm.Name Next Debug.Print "Saved form count: " & db.Containers!Forms.Documents.Count For Each doc In db.Containers!Forms.Documents Debug.Print doc.Name Next
End Sub
</source>
Loading a Form into Memory Prior to Displaying It
<source lang="vb">
Sub ModifySimpleForm()
Dim sNewCaption As String Load frmSimpleForm sNewCaption = InputBox("Enter a caption for the form.") frmSimpleForm.Caption = sNewCaption frmSimpleForm.Show MsgBox "OK - same form again except with default caption", vbOKOnly frmSimpleForm.Show
End Sub
</source>
Open a form, format the fonts and close it
<source lang="vb">
Sub PermanentFormFonts (strFont As String)
Dim objAO As AccessObject Dim objCP As Object Dim ctlControl As Control Set objCP = Application.CurrentProject For Each objAO In objCP.AllForms DoCmd.OpenForm objAO.Name, acDesign, , , , acHidden For Each ctlControl In objAO.Controls ctlControl.FontName = strFont Next DoCmd.Close acForm, objAO.Name Next
End Sub
</source>
Output form to html file
<source lang="vb">
Sub OutputToDAP()
DoCmd.OutputTo acOutputForm, "frmComplaints", acFormatDAP, "c:\P.html"
End Sub
</source>
<source lang="vb">
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
Unload Me "Removes Userform named frmStats from memory.
End Sub
</source>
Using the Show Method to Display a Form
<source lang="vb">
Sub SimpleFormExample()
ShowSimpleForm True MsgBox "OK - Same form now, but modeless.", vbOKOnly ShowSimpleForm False MsgBox "Exiting the SimpleFormExample procedure.", vbOKOnly
End Sub Private Sub ShowSimpleForm(bModal As Boolean)
If bModal Then frmSimpleForm.Show vbModal Else frmSimpleForm.Show vbModeless End If
End Sub
</source>