Dynamically Setting userform Label ForeColor

Question

I have a userform display that is Dynamic and updates itself as I enter in information on a separate worksheet.

What I would like it to do, is set forecolor to red for any negative values that occur under the Deadlift section. I have tried using this code and a few others, but, so far, I do not get errors, nor do I get the font to change color

Sub updateForm2()

Dim lLoop As Long
For lLoop = 163 To 312

If myForm.Controls("Label" & lLoop).Value < 0 Then
    myForm.Controls("Label" & lLoop).ForeColor = RGB(255, 0, 0)
Else
  myForm.Controls("Label" & lLoop).ForeColor = RGB(0, 0, 0) 
End If

Next

End Sub

I have an updateForm, that dynamically updates all the labels as positions change based on the amount lifted

Sub updateForm()
Dim wks As Worksheet
Set wks = Sheets("DeadGenerator")

'Update label values here
myForm.Label1.Caption = wks.Range("c4").Text
myForm.Label2.Caption = wks.Range("c5").Text

to

myForm.Label162.Caption = wks.Range("f41").Text

Then labels

myForm.Label163.Caption = wks.Range("g12").Value

to

myForm.Label312.Caption = wks.Range("k41").Value

are the labels that correspond to the 5 columns under the Deadlift Heading

Any help is appreciated, I am still very new to using Userforms

Thanks

Answer 1

Hope this Help. I replied your form in a really easy way. Just added 3 Labels and linked the caption to cells A1,B1 and C1 (those values are 0,-1,-2).

Ok, First of all, to make this work, you must select ALL the labels you want to have the chance of getting a red backcolor. Then, once you have selected all, use the property TAG (read more here)(in my example, I selected just Label2 and Label3 tho have this chance of red backcolor. Label1 will never ever be red even if caption is below 0). Tag value must be "RedOne" (or adapt the code and tag value to your needs)

Ok, now the code. This code will check EVERY control in your userform. And then, if the TAG value is "RedOne" it will check the caption. And if the caption is below 0, then it will make the backcolor red.

 Dim MyLabel As control

For Each MyLabel In Me.Controls
    If MyLabel.Tag = "RedOne" Then
        If MyLabel.Caption < 0 Then
            MyLabel.BackColor = vbRed
        Else
            MyLabel.BackColor = vbWhite
        End If
    End If
Next MyLabel

And now, let's test it. Note that only label2 and 3 are red because values are both below 0.

Let's test it with Label1.Caption below 0 also. But Label1.Tag is blank, so backcolor will remain the sam, even with values below 0.

And last test. Let's make Label2 value below 0, but label3 value is over 0. Now, only Label2 gets red backcolor, because it's the only label that meets both conditions (Tag="RedOne" and Value<0).

Hope this helps, and I hope you can adapt it to your needs.

Just 1 advice. To set the Tag value, just select ALL the labels you want (Labels under deadlift section) and then type the Tag Value. You don't need to select one by one and type the Tag value one by one.

Let me know if this worked.