Creating Form Skeletons with VBA
In an earlier article, I covered four approaches to creating forms with a consistent user interface.
- The built-in "Form template" setting in Microsoft Access
- Making copies of existing forms
- Creating form "skeletons" via VBA
- Generating entire forms via VBA
Let's explore the function I use to create form "skeletons" in my own applications.
DesignNewForm(): The Code
While you can certainly copy and paste this code as is, I'd recommend that you think of this instead as merely a good starting point.
If you've been developing Access applications, you've likely developed your own style: preferred fonts and sizes, colors, etc. Incorporate those into this function.
There are several different techniques I use to:
- Customize default control properties
- Create different kinds of forms based on the routine's arguments
- Work with both .accdb and .mdb file formats
- Add expressions to certain event properties
- Add code to the code-behind module
This should be enough to get you started and thinking about some of the possibilities.
Additional Dependencies
The code below can be copied and pasted into your project and it will compile and run.
Be aware, though, that if you use the code as-is, it includes several event property expression calls to functions that are not included in this code. Some of them are functions that I previously published; I include URLs in the code comments for those. Others are functions that I may publish in the future; use the site's search feature to check if you are reading this after its original publication date. Any remaining functions you'll either have to implement yourself or, more likely, simply remove.
Remember, the main purpose of this code is to inspire you to make it your own.
' ----------------------------------------------------------------
' Procedure : DesignNewForm
' Date : 2/22/2023
' Author : Mike Wolfe
' Source : https://nolongerset.com/designnewform/
' Purpose : Enforce a consistent UI design when creating new forms.
' ----------------------------------------------------------------
Sub DesignNewForm(Optional ByVal IsContinuous As Boolean = False, _
Optional ByVal IsUnbound As Boolean = False, _
Optional ByVal IsPreview As Boolean = False, _
Optional ByVal IsGetForm As Boolean = False, _
Optional ByVal IsLookup As Boolean = False, _
Optional ByVal IsUnboundAddForm As Boolean = False, _
Optional ByVal InsertCode As Boolean = True) 'vv
Const TwipsPerInch As Long = 1440, LightBlue As Long = 16759739, VeryLightBlue As Long = 15854048
Dim VeryLightGray As Long: VeryLightGray = RGB(250, 250, 250)
Dim RatherLightGray As Long: RatherLightGray = RGB(245, 245, 245)
Dim Frm As Form, Txt As TextBox
Set Frm = CreateForm
With Frm
.GridX = 16
.GridY = 16
.Width = 4 * TwipsPerInch
.Section(acDetail).Height = 2 * TwipsPerInch
If Not (IsPreview Or IsGetForm) Then
DoCmd.RunCommand acCmdFormHdrFtr
.Section(acHeader).BackColor = LightBlue
.Section(acFooter).BackColor = LightBlue
.DividingLines = True
End If
If IsAccdbFormat() Then
.Section(acDetail).BackColor = VeryLightBlue
Else
.Section(acDetail).BackColor = vbButtonFace
End If
Dim BtnFont As String, BtnFsize As Integer
Dim LblFont As String, LblFsize As Integer
Dim TbFont As String, TbFsize As Integer
Dim DataEntryFont As String, DataEntryFSize As Integer
'Set control font defaults
BtnFont = "Verdana"
BtnFsize = "10"
LblFont = "Segoe UI"
LblFsize = 10
TbFont = "Calibri"
TbFsize = 10
DataEntryFont = "Consolas"
DataEntryFSize = 10
'Set Control Defaults
With .DefaultControl(acCommandButton)
.FontSize = BtnFsize
.FontName = BtnFont
.ForeColor = 0
If IsAccdbFormat() Then
.ForeColor = RGB(64, 64, 64)
.Properties("HoverForeColor") = 0
.Properties("PressedForeColor") = 0
.BackColor = 9359785 'olive
.Properties("Gradient") = 12
.Properties("HoverColor") = 5618045 'darker olive
.Properties("PressedColor") = 5618045 'darker olive
.Properties("PictureCaptionArrangement") = 5 'icon to the left; text to the right
.Properties("CursorOnHover") = 1 'use the Hand icon when hovering
End If
End With
With .DefaultControl(acToggleButton)
.FontSize = BtnFsize
.FontName = BtnFont
.ForeColor = 0
End With
With .DefaultControl(acLabel)
.FontSize = LblFsize
.FontName = LblFont
.ForeColor = 3355443
.Height = 210
End With
With .DefaultControl(acTextBox)
.FontSize = TbFsize
.FontName = TbFont
.ForeColor = 0
.Height = 270 ' 0.2292 * TwipsPerInch
.LabelX = -0.0625 * TwipsPerInch
.LabelAlign = 3 'Right
.SpecialEffect = 2 'Sunken
.BorderStyle = 0 'Transparent
End With
With .DefaultControl(acComboBox)
.FontSize = TbFsize
.FontName = TbFont
.ForeColor = 0
.Height = 270 '0.2292 * TwipsPerInch
.LabelX = -0.0625 * TwipsPerInch
.LabelAlign = 3 'Right
.SpecialEffect = 2 'Sunken
End With
With .DefaultControl(acCheckBox)
.Height = 202
.LabelX = -0.0625 * TwipsPerInch
.LabelAlign = 3
.LabelY = -38
End With
With .DefaultControl(acListBox)
.SpecialEffect = 2 'Sunken
End With
With .DefaultControl(acSubform)
.BorderStyle = 0 'Transparent
End With
If IsContinuous Or IsLookup Then
.DefaultView = 1 'continuous
If Not IsAccdbFormat() Then
'The MS Access vertical scroll bar bug is fixed as of Access 2007+
.AfterDelConfirm = "=SetScrollBarDisplay([Form])"
.OnResize = "=SetScrollBarDisplay([Form])"
.AfterInsert = "=SetScrollBarDisplay([Form])"
End If
With .DefaultControl(acTextBox)
.AddColon = False
.LabelAlign = 1 'Left
.LabelX = -1 * TwipsPerInch
End With
With .DefaultControl(acComboBox)
.AddColon = False
.LabelAlign = 1 'Left
.LabelX = -1 * TwipsPerInch
End With
With .DefaultControl(acCheckBox)
.AddColon = False
.LabelAlign = 1
.LabelX = -1 * TwipsPerInch
End With
End If
If IsUnbound Or IsPreview Or IsUnboundAddForm Then
.RecordSelectors = False
.NavigationButtons = False
.ScrollBars = 0 'Neither
End If
If IsPreview Then
.MinMaxButtons = 0
.ControlBox = False
.CloseButton = False
.Width = 2.5 * TwipsPerInch
With .Section(acDetail)
.BackColor = LightBlue
.Height = 2.75 * TwipsPerInch
End With
With CreateControl(.Name, acCommandButton)
.Left = 0.125 * TwipsPerInch
.Top = 2 * TwipsPerInch
.Width = 1.0625 * TwipsPerInch
.Height = 0.5625 * TwipsPerInch
.Caption = "&Preview Report"
.OnClick = "[Event Procedure]"
.Name = "PreviewRptBtn"
End With
With CreateControl(.Name, acCommandButton)
.Left = 1.3125 * TwipsPerInch
.Top = 2 * TwipsPerInch
.Width = 1.0625 * TwipsPerInch
.Height = 0.25 * TwipsPerInch
.Caption = "E&xport to Excel"
.OnClick = "[Event Procedure]"
.Name = "ExportToExcelBtn"
End With
With CreateControl(.Name, acCommandButton)
.Left = 1.3125 * TwipsPerInch
.Top = 2.3125 * TwipsPerInch
.Width = 1.0625 * TwipsPerInch
.Height = 0.25 * TwipsPerInch
.Caption = "&Close"
.OnClick = "=CloseForm([Form])" 'A simple function to close the passed form
.Name = "CloseBtn"
End With
End If
If Not IsPreview Then
.OnOpen = "[Event Procedure]"
.HasModule = True
If InsertCode Then
'The weResizer class module is our take on a form resizer
' I'm leaving this section in place so that you
' can use it as inspiration; on its own, it won't compile
' (unless of course you have a weResizer class in your project)
.Module.InsertLines .Module.CountOfLines, vbCrLf & _
"Dim Resizer As New weResizer" & vbCrLf & vbCrLf & _
"Private Sub Form_Open(Cancel As Integer)" & vbCrLf & _
vbTab & "Set Resizer.Form = Me.Form" & vbCrLf & _
"End Sub"
End If
End If
If IsGetForm Then
.MinMaxButtons = 0
.ControlBox = False
.CloseButton = False
.Width = 2 * TwipsPerInch
With .Section(acDetail)
.BackColor = LightBlue
.Height = 1.875 * TwipsPerInch
End With
With CreateControl(.Name, acTextBox)
.Width = Frm.Width - (2 * 0.125 * TwipsPerInch)
.Height = 0.5 * TwipsPerInch
.Left = 0.125 * TwipsPerInch
.ControlSource = "=Forms!Global!Message" 'Set the text based on a global form field
.BackStyle = 0 'Transparent
.SpecialEffect = 0 'Flat
.BorderStyle = 0 'Transparent
.Locked = True
.Enabled = False
End With
With CreateControl(.Name, acCommandButton)
.Width = 0.75 * TwipsPerInch
.Height = 0.25 * TwipsPerInch
.Left = 0.125 * TwipsPerInch
.Top = 1.5 * TwipsPerInch
.Caption = "&OK"
.OnClick = "[Event Procedure]"
.Name = "OKBtn"
End With
With CreateControl(.Name, acCommandButton)
.Width = 0.75 * TwipsPerInch
.Height = 0.25 * TwipsPerInch
.Left = 1.125 * TwipsPerInch
.Top = 1.5 * TwipsPerInch
.Caption = "&Cancel"
.OnClick = "=CloseForm([Form])" 'A simple function to close the passed form
.Name = "CancelBtn"
End With
.HasModule = True
.OnLoad = "[Event Procedure]"
If InsertCode Then
.Module.InsertLines .Module.CountOfLines, vbCrLf & vbCrLf & _
"Private Sub Form_Load()" & vbCrLf & _
vbTab & "'Load control defaults, etc." & vbCrLf & _
"End Sub"
.Module.InsertLines .Module.CountOfLines, vbCrLf & vbCrLf & _
"Private Sub OKBtn_Click()" & vbCrLf & vbCrLf & _
vbTab & "Me.Visible = False 'hide form to resume when form opened as acDialog" & vbCrLf & _
"End Sub"
End If
End If
If IsLookup Then
If IsAccdbFormat Then .Section(acDetail).Properties("AlternateBackColor") = RatherLightGray
.AllowAdditions = False
.AllowDeletions = False
With .DefaultControl(acTextBox)
'.Enabled = False 'Cannot set default for .Enabled and .Locked properties
'.Locked = True
.BackStyle = 0
.SpecialEffect = 0
'Text boxes are disabled and locked so we will style them like labels
.FontName = LblFont
.FontSize = LblFsize
End With
With CreateControl(.Name, acTextBox)
.Width = 0.5 * TwipsPerInch
.Height = 0.5 * TwipsPerInch
.Left = 0
.Top = 0
.Name = "Hiliter"
.BackStyle = 1
If IsAccdbFormat() Then
.BackColor = VeryLightGray
.ForeColor = VeryLightGray
Else
.BackColor = vbButtonFace
.ForeColor = vbButtonFace
End If
.Enabled = False
.Locked = True
.Tag = "HGrow=1"
End With
'See https://nolongerset.com/highlightrow/
.OnCurrent = "=HighlightRow([Hiliter])"
With CreateControl(.Name, acCommandButton)
.Width = 0.5 * TwipsPerInch
.Height = 0.5 * TwipsPerInch
.Left = 0.5
.Top = 0
.Name = "InvisiBtn"
.Transparent = True
.OnDblClick = "=ShowForm("""")" 'Replace with your own custom form-opening function
.Tag = "HGrow=1"
End With
End If
If IsUnboundAddForm Then
'Use fixed-width font for data entry
With .DefaultControl(acTextBox)
.FontSize = DataEntryFSize
.FontName = DataEntryFont
.Height = 285
End With
With .DefaultControl(acComboBox)
.FontSize = DataEntryFSize
.FontName = DataEntryFont
.Height = 285
End With
End If
DoCmd.Restore
End With
End Sub
Private Function IsAccdbFormat() As Boolean
Select Case Right(Application.CurrentProject.Name, "6")
Case ".accdb", ".accde": IsAccdbFormat = True
Case Else: IsAccdbFormat = False
End Select
End FunctionReferenced articles
5 Ways to Build Consistent Form User Interfaces in Microsoft Access
Make your Access application feel more professional with a consistent user interface design. Form templates make it easy.
Cover image created with Microsoft Designer