USERFORM llabel boxes, make the backcolor change when Initialized

[amxtomzo]

Hello and thanks for looking

on a Userform in Initialize

I would like the label boxes to change color based on the cell value in Column F
Values would be "Day Shift", "NIGHT SHIFT", or an emtpy cell
or stay the same back color as the userform if there is nothing in column F

Cells where the value is are in range F3:F41
Label boxes are named Lb3 to Lb41

in the Userform it does a couple things before this step...they are
1st) puts the contents of cells F3:F41 in label boxes 3 to 41 ( same row , same box number )
2nd) formats the label boxes for zip codes
3rd ) darkens 2 of the label boxes, ( we don't use them any more )

the next step to happen should be the color changing
this is what I started with, but nothing happens, no changes or errors

Dim sh1 As Worksheet
Set sh1 = Sheets("ListPage")
  ' makes label boxes Yellow for Day Shift
  ' and a shade of Red for Night Shift
  ' and the same color as the userform if not assigned
For j = 3 To 41

If sh1.Cells(j, 6).Value = "" Then
    UFZipAssigned.Controls("Lb" & j).BackColor = &HFFFF80
End If
Next j
For j = 3 To 41
If sh1.Cells(j, 6).Value = "Day Shift" Then
    UFZipAssigned.Controls("Lb" & j).BackColor = vbYellow
End If
Next j
For j = 3 To 41
If sh1.Cells(j, 6).Value = "Night Shift" Then
    UFZipAssigned.Controls("Lb" & j).BackColor = &H8080FF
End If
Next j

as always Thank you

Thomas

 

[Norie]

Thomas

Where have you put that code?

 

[amxtomzo]

that portion is/will be at the end, after the other 3 steps

 

[Norie]

What sub is the code located in?

 

[amxtomzo]

thanks for helping Norie, and good morning

Its in Initialize on the Userform

here is the whole code, where I am at now, if it helps in any way

Private Sub UserForm_Initialize()
Dim sh1 As Worksheet
Set sh1 = Sheets("ListPage")

' puts in value of "listPage"cell K3
lb201.Caption = sh1.Cells(3, 11).Value
lb201.Caption = Format(lb201.Caption, "###,##0")

' puts contents of column F in teh label boxes
For i = 3 To 41
    With UFZipAssigned.Controls("Lb" & i)
        .Caption = CStr(sh1.Cells(i, 4).Value)
        UFZipAssigned.Controls("Cbx" & i).Enabled = (.Caption <> vbNullString)
    End With
Next i

' formats the labels as Zip Codes
For j = 3 To 41
    With UFZipAssigned.Controls("Lb" & j)
        .Caption = Format(.Caption, "00000")
    End With
Next j
' Darkens the labels boxes with 2 zips we do not do or uses
If lb4.Caption = "01002" Then
    cbx4.Enabled = False
    lb4.BackColor = &H404040
    End If
    
If lb16.Caption = "00000" Then
    cbx16.Enabled = False
    lb16.BackColor = &H404040
    End If

  ' makes label boxes Yellow for Day Shift
  ' and a shade of Red for Night Shift
  ' and the same color as the userform if not assigned

For j = 3 To 41

If sh1.Cells(j, 6).Value = "" Then
    UFZipAssigned.Controls("Lb" & j).BackColor = &HFFFF80
End If
Next j
For j = 3 To 41
If sh1.Cells(j, 6).Value = "Day Shift" Then
    UFZipAssigned.Controls("Lb" & j).BackColor = vbYellow
End If
Next j
For j = 3 To 41
If sh1.Cells(j, 6).Value = "Night Shift" Then
    UFZipAssigned.Controls("Lb" & j).BackColor = &H8080FF
End If
Next j

End Sub

Thomas

 

[amxtomzo]

Hello
Though I would try again with this question.
I do think I am close,
just not sure where/what I am missing to make this happen in the Userform

Thomas

 

[Norie]

Thomas

The code looks fine to me, have you tried stepping through it?

You can do that by opening the userform in design mode, double clicking to show it's code module and pressing F8.

 

[amxtomzo]

Thank you Norie

Your not gonna believe this, the code was looking down column E
Should have been looking down Column F......AAAAHHHH
But its is doing what I was hoping it would do.

Sorry to have taken up everyone's time

Thanks as always

Thomas