Visual Basic for Applications/Styling User Forms

Sub FormatAllLoadedUserForms()
    ' formats all open UserForms with uniform styles
    ' eg: colors, sizes and fonts ect
    ' Initially fonts are nominal
    
    Dim oCont As msforms.Control, oFrm As Object
    Dim Backshade As Long, ButtonShade As Long, BoxShade As Long
    Dim ButtonTextShade As Long, BoxTextShade As Long
    Dim FontNameLabels As String, FontNameData As String
    Dim FontSizeLabels As Integer, FontSizeData As String
    
    ' ----------------------------------------------------------------------
    '                       Set User Preferences Here
    ' ----------------------------------------------------------------------
    Backshade = RGB(31, 35, 44)          ' almost black - form background
    ButtonShade = RGB(0, 128, 128)       ' blue-green   - button background
    BoxShade = RGB(0, 100, 0)            ' middle green - box background
    ButtonTextShade = RGB(230, 230, 230) ' almost white - button text color
    BoxTextShade = RGB(255, 255, 255)    ' white        - box text color
    
    FontNameLabels = "Tahoma"         'labels, frames, check boxes, buttons
    FontNameData = "Tahoma"           'text boxes, combo boxes, list boxes
    FontSizeLabels = 8                'labels, frames, check boxes, buttons
    FontSizeData = 8                  'text boxes, combo boxes, list boxes
    
    ' -----------------------------------------------------------------------
    Load UserForm1
    
    ' Userform collection loop
    For Each oFrm In VBA.UserForms
        
        ' FORMAT TEXTBOXES, COMBOBOXES AND
        ' LISTBOXES
        For Each oCont In oFrm.Controls
            If _
                oCont.Name Like "TextBox*" Or _
                oCont.Name Like "ComboBox*" Or _
                oCont.Name Like "ListBox*" Then
                With oCont
                    .BackColor = BoxShade
                    .ForeColor = BoxTextShade
                    .Font.Name = FontNameData
                    .Font.Size = FontSizeData
                    .Font.Bold = False
                End With
            End If
        Next oCont
        
        ' FORMAT THE USERFORM
        ' set background color
        With oFrm
            .BackColor = Backshade
        End With
        
        ' FORMAT THE LABELS, CHECKBOXES,
        ' OPTIONBUTTONS AND FRAMES
        For Each oCont In oFrm.Controls
            If oCont.Name Like "Label*" Or _
                oCont.Name Like "CheckBox*" Or _
                oCont.Name Like "OptionButton*" Or _
                oCont.Name Like "Frame*" Then
                With oCont
                    .BackColor = Backshade
                    .ForeColor = ButtonShade
                    .Font.Name = FontNameLabels
                    .Font.Size = FontSizeLabels
                    .Font.Bold = False
                End With
            End If
        Next oCont
        
        ' FORMAT COMMANDBUTTONS
        For Each oCont In oFrm.Controls
            If oCont.Name Like "CommandButton*" Then
                With oCont
                    .BackColor = ButtonShade
                    .ForeColor = ButtonTextShade
                    .Font.Name = FontNameLabels
                    .Font.Size = FontSizeLabels
                    .Font.Bold = False
                End With
            End If
        Next oCont
    
        ' FORMAT SPINBUTTONS AND TOGGLEBUTTONS
        For Each oCont In oFrm.Controls
            If oCont.Name Like "SpinButton*" Or _
                oCont.Name Like "ToggleButton*" Then
                With oCont
                    .BackColor = ButtonShade
                    .ForeColor = ButtonTextShade
                End With
            End If
        Next oCont
    
    Next oFrm
    UserForm1.Show

End Sub

Sub AutoFormat(vA As Variant, Optional bTranspose As Boolean = False)
    ' Takes array vA of say, 4 columns of data and
    ' displays on textbox in tabular layout.
    ' Needs a userform called ViewVars and a textbox
    ' called Textbox1.  Code will adjust layout.
    ' Transpose2DArr used only to return data to (r, c) format.
    
    Dim vB As Variant, vL As Variant, vR As Variant
    Dim r As Long, c As Long, m As Long, sS As String
    Dim nNumPadSp As Long, TxtLab As Control, MaxFormWidth As Long
    Dim sAccum As String, sRowAccum As String, bBold As Boolean
    Dim nLineLen As Long, BoxFontSize As Long, BoxFontName As String
    Dim sLabAccum As String, nLabPadSp As Long, oUserForm As Object
    Dim Backshade As Long, BoxShade As Long, BoxTextShade As Long
    Dim ButtonShade As Long, ButtonTextShade As Long
    Dim Lb1 As Long, Ub1 As Long, Lb2 As Long, Ub2 As Long
    Dim TextLength As Long, bItalic As Boolean
    
    ' decide to transpose input or not
    If bTranspose = True Then
        Transpose2DArr vA, vR
        vA = vR
    End If
        
    ' get bounds of display array
    Lb1 = LBound(vA, 1): Ub1 = UBound(vA, 1)
    Lb2 = LBound(vA, 2): Ub2 = UBound(vA, 2)
    
    ReDim vL(Lb2 To Ub2) ' make labels array
    ReDim vB(Lb2 To Ub2) ' dimension column width array
    
    '--------------------------------------------------------------
    '                   SET USER OPTIONS HERE
    '--------------------------------------------------------------
    ' set the name of the userform made at design time
    Set oUserForm = ViewVars
    
    ' set limit for form width warning
    MaxFormWidth = 800
    
    ' make column labels for userform - set empty if not needed
    vL = Array("Variable", "Procedure", "Module", "Project")
    
    ' colors
    Backshade = RGB(31, 35, 44)          'almost black -   used
    ButtonShade = RGB(0, 128, 128)       'blue-green - not used
    BoxShade = RGB(0, 100, 0)            'middle green -   used
    ButtonTextShade = RGB(230, 230, 230) 'near white - not used
    BoxTextShade = RGB(255, 255, 255)    'white -          used
    ' Font details are to be found below
    '--------------------------------------------------------------
    
    ' find maximum width of array columns
    ' taking account of label length also
    For c = Lb2 To Ub2
        m = Len(vL(c)) 'label
        For r = Lb1 To Ub1
            sS = vA(r, c) 'value
            If Len(sS) >= m Then
                m = Len(sS)
            End If
        Next r
        'exits with col max array
        vB(c) = m
        m = 0
    Next c
   
   ' For testing only
   ' shows max value of each column
'   For c = LB2 To UB2
'       MsgBox vB(c)
'   Next c
    
    For r = Lb1 To Ub1
        For c = Lb2 To Ub2
            If c >= Lb2 And c < Ub2 Then
                ' get padding for current element
                nNumPadSp = vB(c) + 2 - Len(vA(r, c))
            Else
                ' get padding for last element
                nNumPadSp = vB(c) - Len(vA(r, c))
            End If
                ' accumulate line with element padding
            sAccum = sAccum & vA(r, c) & Space(nNumPadSp)
                ' get typical line length
            If r = Lb1 Then
                sRowAccum = sRowAccum & vA(Lb1, c) & Space(nNumPadSp)
                nLineLen = Len(sRowAccum)
            End If
        Next c
                ' accumulate line strings
                sAccum = sAccum & vbNewLine
    Next r

    ' accumulate label string
    For c = Lb2 To Ub2
        If c >= Lb2 And c < Ub2 Then
            ' get padding for current label
            nLabPadSp = vB(c) + 2 - Len(vL(c))
        Else
            ' get padding for last element
            nLabPadSp = vB(c) - Len(vL(c))
        End If
        ' accumulate the label line
        sLabAccum = sLabAccum & vL(c) & Space(nLabPadSp)
    Next c
        
    ' load user form
    Load oUserForm
    
    '================================================================
    '       SET FONT DETAILS HERE. THESE AFFECT ALL AUTOSIZING.
    '================================================================
    BoxFontSize = 12         'say between 6 to 20 points
    bBold = True             'True for bold, False for regular
    bItalic = False          'True for italics, False for regular
    BoxFontName = "Courier"  'or other monospaced fonts eg; Consolas
    '================================================================
      
    ' make the labels textbox
    Set TxtLab = oUserForm.Controls.Add("Forms.TextBox.1", "TxtLab")
    
    ' format the labels textbox
    With TxtLab
        .WordWrap = False
        .AutoSize = True 'extends to fit text
        .Value = ""
        .font.Name = BoxFontName
        .font.SIZE = BoxFontSize
        .font.Bold = bBold
        .font.Italic = bItalic
        .ForeColor = BoxTextShade
        .Height = 20
        .Left = 20
        .Top = 15
        .Width = 0
        .BackStyle = 0
        .BorderStyle = 0
        .SpecialEffect = 0
    End With
    
    'apply string to test label to get length
    TxtLab.Value = sLabAccum & Space(2)
    TextLength = TxtLab.Width
    'MsgBox TextLength
    
    'format userform
    With oUserForm
        .BackColor = Backshade
        .Width = TextLength + 40
        .Height = 340
        .Caption = "Redundant variables list..."
    End With
      
    ' check user form is within max width
    If oUserForm.Width > MaxFormWidth Then
        MsgBox "Form width is excessive"
        Unload oUserForm
        Exit Sub
    End If
    
    'format the data textbox
    With oUserForm.TextBox1
        .ScrollBars = 3
        .WordWrap = True
        .MultiLine = True
        .EnterFieldBehavior = 1
        .BackColor = BoxShade
        .font.Name = BoxFontName
        .font.SIZE = BoxFontSize
        .font.Bold = bBold
        .font.Italic = bItalic
        .ForeColor = BoxTextShade
        .Height = 250
        .Left = 20
        .Top = 40
        .Width = TextLength
        .Value = sAccum
    End With
    
    'show the user form
    oUserForm.Show

End Sub