Permanently Changing User Forms

Question

I am trying to permanently change a UserForm so that I only have to run the code once instead of it being run every time the button to open the UserForm is clicked.

I found that this works for some people. I am not sure what might be wrong for me.

Sub FormatUserForms(UF As UserForm)

UF.BackColor = RGB(51, 51, 102)
Dim ctrl As Control

With ThisWorkbook.VBProject.VBComponents("UFNewRequest").Designer
    For Each ctrl In UF.Controls
        Select Case TypeName(ctrl)
            Case "Label"
                ctrl.BackColor = RGB(51, 51, 102)
                ctrl.ForeColor = RGB(247, 247, 247)
            Case "CommandButton"
                ctrl.BackColor = RGB(247, 247, 247)
                ctrl.ForeColor = RGB(0, 0, 0)
            Case "TextBox"
                ctrl.BackColor = RGB(247, 247, 247)
                ctrl.ForeColor = RGB(0, 0, 0)
            Case "OptionButton"
                ctrl.BackColor = RGB(51, 51, 102)
                ctrl.ForeColor = RGB(247, 247, 247)
        End Select
    Next
End With
End Sub

Sub formatting()

FormatUserForms UFNewRequest

End Sub

Answer 1

You're mixing two things. It's the right track to use the ThisWorkbook.VBProject.VBComponents("UFNewRequest").Designer object, but you're not using it. Instead, your code works on the form passed as parameter - and this will work only at runtime.

Sub FormatUserForm(frm As Object)

Dim d As UserForm
Set d = frm.Designer

d.BackColor = RGB(51, 51, 102)

Dim ctrl As Control
For Each ctrl In d.Controls
    Select Case TypeName(ctrl)
            (...)
    End Select
Next ctrl

End Sub

You can call the function for all forms on your project:

Dim f As Object
For Each f In ThisWorkbook.VBProject.VBComponents
    If f.Type = 3 Then
       Call FormatUserForm(f)
    End If
Next f

I guess there is a way to use early binding for f, but you have to add a reference to VBE to your project and I'm not allowed to do this on my computer so I cannot test. Probably there is a Constant defined for the magic type number 3 (=forms) also