Formatting User Form Controls

Question

I am trying to format controls on each of my User Forms similarly, but what I have formats all controls on a User Form instead of just the labels or just the textboxes.

Here is what I have:

Private Sub UserForm_Initialize()

FormatUserForms UFNewRequest

End Sub

Sub FormatUserForms(UF As UserForm)

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

For Each Label In UF.Controls
    Label.BackColor = RGB(51, 51, 102)
    Label.ForeColor = RGB(247, 247, 247)
Next

For Each Button In UF.Controls
    Button.BackColor = RGB(247, 247, 247)
    Button.ForeColor = RGB(0, 0, 0)
Next

For Each TextBox In UF.Controls
    TextBox.BackColor = RGB(247, 247, 247)
    TextBox.ForeColor = RGB(0, 0, 0)
Next

End Sub

This sort of does what I want, but each For Each overwrites the last. Is there anyway to delineate Controls in a User Form so that they are not overwritten?

Also, every time I open my and then close my User Forms with this, I get an "out of memory" error. Help with that would be appreciated as well! Thanks.

Answer 1

You are looping over all Controls three times, so every call changes color for all controls. Naming a variable Textbox doesn't filter the controls for only textboxes. You have to separate the Controls types by using TypeName(ctrl):

For Each ctrl In u.Controls
    If TypeName(ctrl) = "TextBox" Then
        ctrl.BackColor = ...
    ElseIf TypeName(ctrl) = "Label" Then
        ctrl.BackColor = ...
    Else
        ...
    End If
next ctrl

As far as I see, this works only if the form is open, so put a call to your function into the UserForm_Initialize-Event.

Answer 2

I didn't check, but the "out of memory" error is most likely related to using the default instance of the UserForm in its own Initialize procedure:

FormatUserForms UFNewRequest

You shouldn't do that (and don't need to). You can always get a reference to the form in its code-behind with Me. I'd remove that entirely. As for the controls, you can call TypeName to figure out what type of control it is, and then use a Select Case to format them accordingly:

Private Sub UserForm_Initialize()
    FormatUserForms
End Sub

Sub FormatUserForms()
    Me.BackColor = RGB(51, 51, 102)
    Dim current As Control

    For Each current In Me.Controls
        Select Case TypeName(current)
            Case "Label"
                current.BackColor = RGB(51, 51, 102)
                current.ForeColor = RGB(247, 247, 247)
            Case "CommandButton"
                current.BackColor = RGB(247, 247, 247)
                current.ForeColor = RGB(0, 0, 0)
            Case "TextBox"
                current.BackColor = RGB(247, 247, 247)
                current.ForeColor = RGB(0, 0, 0)
    Next
End Sub