Change Mulitple Userform Label Backcolors (Excel 2013)

[davis1118]

I finally have all the code for my userform working properly, and I am going back through to clean it up. When the userform initializes I am changing the backcolor on the Userform and then all the Frames and Labels. There has to be a cleaner way to write the code below. I always get good help from the experts on this forum and wanted to see what options I have. Any suggestions would be appreciated.



Dim color As String

color = RGB(166, 192, 214)

With EditProcessForm
.BackColor = color
.Frame1.BackColor = color
.Frame2.BackColor = color
.Frame3.BackColor = color
.Label1.BackColor = color
.Label2.BackColor = color
.Label3.BackColor = color
.Label4.BackColor = color
.Label5.BackColor = color
.Label6.BackColor = color
.Label7.BackColor = color
.Label8.BackColor = color
.Label9.BackColor = color
.Label10.BackColor = color
.Label11.BackColor = color
.Label12.BackColor = color
.Label13.BackColor = color
.Label14.BackColor = color
.Label15.BackColor = color
.Label16.BackColor = color
.Label17.BackColor = color
.Label18.BackColor = color
.Label19.BackColor = color
.Label20.BackColor = color
.Label21.BackColor = color
.Label22.BackColor = color
.Label23.BackColor = color
.Label24.BackColor = color
End With


Thanks for the help!

[Paul_Hossler]

Maybe something like this



Option Explicit

Private Sub UserForm_Initialize()

Dim oCtrl As Control
Dim color As Long ' Long, not String

color = RGB(166, 192, 214)

EditProcessForm.BackColor = color

For Each oCtrl In EditProcessForm.Controls
If TypeName(oCtrl) = "Frame" Then
oCtrl.BackColor = color
ElseIf TypeName(oCtrl) = "Label" Then
oCtrl.BackColor = color
End If
Next

End Sub

[davis1118]

Wow, I was not expecting a response 10 minutes after my post. Thank you very much Paul, it works perfectly! Thanks for the tip about changing the String to Long. I'm sure that shows my novice level at vba, but I appreciate the advice. I wish I could sit down with you and some of the other experts on this forum and get all the expert advice I needed. Thanks again for the help!

[Paul_Hossler]

No problem

Remember to mark this Solved

[Tom Jones]

Maybe something like this

Option Explicit

Private Sub UserForm_Initialize()

Dim oCtrl As Control
Dim color As Long ' Long, not String

color = RGB(166, 192, 214)

EditProcessForm.BackColor = color

For Each oCtrl In EditProcessForm.Controls
If TypeName(oCtrl) = "Frame" Then
oCtrl.BackColor = color
ElseIf TypeName(oCtrl) = "Label" Then
oCtrl.BackColor = color
End If
Next

End Sub




When I run your code, I get "variable not defined" and highlighted EditProcessForm

[Paul_Hossler]

I assumed from the first post that you had a UserForm named 'EditProcessForm'

The way I did the macro was to put it userform's code sheet in the Initialize event

[Tom Jones]

Thanks for the reply, Paul

I realized where I was wrong after I posted the message, but I could not delete it.
Thank you for your kindness.

[Paul_Hossler]

No problem

BTW, you can edit a recent post by clicking the [Edit Post] button at lower right, and add something like

"Edit -- Never mind, I figured it out" in the body