Project Perfect
PROJECT  PERFECT
                 Project Management Software
                          Specialists in Project Infrastructure 		Microsoft Access Development Tips

Home - Microsoft Access Development - Microsoft Access Tips

Sample Microsoft Access VBA

Setting Font Size Dynamically in a Label

The following are some samples of VBA code that may prove useful.

Sometimes you want to set the font size of a label in a form or report depending on the length of the text to be displayed. For example, you may have a report that prints a person's name in a fixed length label in the header. As names can vary in length, you want to use bigger font for shorter names and smaller for larger names.

The example below lists a sample set of font sizes for names ranging from under 27 characters to over 42 characters. The comments at the top list the font size for forms and the font size for reports. You will need to experiment for your own particular situation. First create the table at the top, then use those figures in the if statements.

To use the function store it in a module.

Assume you have a label called lblName in a form or report. Use the following in the form or report OnOpen event.

funSetFontSize Me.lblName.Caption            ' Get the font size for the Name


Me.lblName.FontSize = lngTitleFontSize       ' Set the font size for the Name

Public Function funSetFontSize(strName As String)
' Set the font size of the names in screens and reports

Dim lngTextLength As Long        ' Length of the text to be displayed
' The next two public values can be used anywhere in the application
Public lngTitleFontSize as Long
Public lngRptTitleFontSize as Long

' Max Chars -Form Font - Report Font
' Under 27 - 20 - 14
' 29 - 18 - 14
' 32 - 16 - 14
' 36 - 14 - 12
' 39 - 13 - 12
' 42 - 12 - 12
' Over 42 - 10 - 10

On Error GoTo Error_funSetFontSize

lngTextLength = Len(strName)

If lngTextLength < 27 Then
    lngTitleFontSize = 20
    lngRptTitleFontSize = 14
End If

If lngTextLength >= 27 And lngTextLength < =28 Then
    lngTitleFontSize = 16
    lngRptTitleFontSize = 14
End If

If lngTextLength >= 30 And lngTextLength < =32 Then
    lngTitleFontSize = 14
    lngRptTitleFontSize = 14
End If

If lngTextLength >= 33 And lngTextLength < =36 Then
    lngTitleFontSize = 13
    lngRptTitleFontSize = 12
End If

If lngTextLength >= 37 And lngTextLength < =39 Then
    lngTitleFontSize = 12
    lngRptTitleFontSize = 12
End If

If lngTextLength >= 40 And lngTextLength < =42 Then
    lngTitleFontSize = 12
    lngRptTitleFontSize = 12
End If

If lngTextLength >= 43 Then
    lngTitleFontSize = 10
    lngRptTitleFontSize = 8
End If

Exit_funSetFontSize:
    Exit Function

Error_funSetFontSize:
    MsgBox "Error in funSetFontSize: " & Err.Number & " - " & Err.Description
    Resume Exit_funSetFontSize

End Function

Return to the top