Label Change Color When Text in cell is "true" on userform

Hjemmet

Board Regular
Joined
Jun 20, 2018
Messages
180
I have now try to put my code where the Command Button is for Label70 and that works as Brilliant

and of course i figure out i miss some Part where i get the Value from 8 other Cell's witch tell me Who is the Referee at The Game for the last 16 Players

i have I set this Code for each NEW label's from 71-79 with this code and value is First show when i Click on label :(

Code:
Private Sub Label71_Click()Me.Label71.Caption = ThisWorkbook.Sheets("Cup 128").Range("E267")
[COLOR=#ff0000]resetLabels[/COLOR]
End Sub
as i self can see it because this line "Private Sub Label71_Click()" but cant figure out how to make it Auto update when Userform is Show

and i have try it with "resetLabels" but aint work....
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,721
Office Version
365
Platform
Windows
As this is now a different question, you will need to start a new thread.
Thanks
 

Hjemmet

Board Regular
Joined
Jun 20, 2018
Messages
180
Hey Fluff..

I have now Running my Userform through for mistakes and in first time nothing wrong But second Running through userform and Functions i ran into few mistakes



mistake was if example (Round8) was played Longer than another (Round's) and i start Userform and those (Round1) and None of those games where played Then i can see Players from (Round8)
and it is on all of them that give that Mistake

i have make a Sheet next to they Original file
Code:
https://www.dropbox.com/s/oo0jqtg9hjylg56/total%20changes%20with%20description.xlsm?dl=0
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,721
Office Version
365
Platform
Windows
That's because you only change the labels if <> false.
You also need to change clear the label if the cell value is false.
 

Hjemmet

Board Regular
Joined
Jun 20, 2018
Messages
180
im sorry to say BUT i aint figure it out what to change and where

i have try to change False to True and then the Value Aint Show on label only "False" show's in label and that might because the "Formula in Cell where Label has the Reference to Give False Result

Hope you could or will Help me with that Part too...

Only the Label Function of course
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,721
Office Version
365
Platform
Windows
Do it like
Code:
      For i = Rw + 2 To Rw + 27 Step 8
         J = J + 1
         Me.Controls("Label" & J).Caption = IIf(.Range("I" & i).Value = False, "", .Range("I" & i).Value)
         Me.Controls("Label" & J + 1).Caption = IIf(.Range("I" & i + 1).Value = False, "", .Range("I" & i + 1).Value)
         Me.Controls("Label" & J).BackColor = IIf(.Range("H" & i).Value, vbRed, vbWhite)
         Me.Controls("Label" & J + 1).BackColor = IIf(.Range("H" & i + 1).Value, vbRed, vbWhite)
         J = J + 1
      Next i
 

Hjemmet

Board Regular
Joined
Jun 20, 2018
Messages
180
i dont know what i am doing wrong But i can get it to work

so i am near to give up my Project
this is the Code Before new Change's

Code:
Sub resetLabels()   With Sheets("Cup 128")
      For i = Rw To Rw + 29 Step 4
         J = J + 1
         If .Range("E" & i).Value <> False Then Me.Controls("Label" & J).Caption = .Range("E" & i).Value
         If .Range("E" & i + 1).Value <> False Then Me.Controls("Label" & J + 1).Caption = .Range("E" & i + 1).Value
         Me.Controls("Label" & J).BackColor = IIf(.Range("D" & i).Value, vbRed, vbWhite)
         Me.Controls("Label" & J + 1).BackColor = IIf(.Range("D" & i + 1).Value, vbRed, vbWhite)
         J = J + 1
      Next i
[COLOR=#ff0000]      For i = Rw + 2 To Rw + 27 Step 8[/COLOR]
[COLOR=#ff0000]         J = J + 1[/COLOR]
[COLOR=#ff0000]         If .Range("I" & i).Value <> False Then Me.Controls("Label" & J).Caption = .Range("I" & i).Value[/COLOR]
[COLOR=#ff0000]         If .Range("I" & i + 1).Value <> False Then Me.Controls("Label" & J + 1).Caption = .Range("I" & i + 1).Value[/COLOR]
[COLOR=#ff0000]         Me.Controls("Label" & J).BackColor = IIf(.Range("H" & i).Value, vbRed, vbWhite)[/COLOR]
[COLOR=#ff0000]         Me.Controls("Label" & J + 1).BackColor = IIf(.Range("H" & i + 1).Value, vbRed, vbWhite)[/COLOR]
[COLOR=#ff0000]         J = J + 1[/COLOR]
[COLOR=#ff0000]      Next i[/COLOR]
      For i = Rw + 6 To Rw + 23 Step 16
         J = J + 1
         If .Range("M" & i).Value <> False Then Me.Controls("Label" & J).Caption = .Range("M" & i).Value
         If .Range("M" & i + 1).Value <> False Then Me.Controls("Label" & J + 1).Caption = .Range("M" & i + 1).Value
         Me.Controls("Label" & J).BackColor = IIf(.Range("L" & i).Value, vbRed, vbWhite)
         Me.Controls("Label" & J + 1).BackColor = IIf(.Range("L" & i + 1).Value, vbRed, vbWhite)
         J = J + 1
      Next i
   End With
End Sub
so code look's like this
Code:
Sub resetLabels()   With Sheets("Cup 128")
      For i = Rw To Rw + 29 Step 4
         J = J + 1
         If .Range("E" & i).Value <> False Then Me.Controls("Label" & J).Caption = .Range("E" & i).Value
         If .Range("E" & i + 1).Value <> False Then Me.Controls("Label" & J + 1).Caption = .Range("E" & i + 1).Value
         Me.Controls("Label" & J).BackColor = IIf(.Range("D" & i).Value, vbRed, vbWhite)
         Me.Controls("Label" & J + 1).BackColor = IIf(.Range("D" & i + 1).Value, vbRed, vbWhite)
         J = J + 1
      Next i
      [COLOR=#ff0000]For i = Rw + 2 To Rw + 27 Step 8
         J = J + 1
         Me.Controls("Label" & J).Caption = IIf(.Range("I" & i).Value = False, "", .Range("I" & i).Value)
         Me.Controls("Label" & J + 1).Caption = IIf(.Range("I" & i + 1).Value = False, "", .Range("I" & i + 1).Value)
         Me.Controls("Label" & J).BackColor = IIf(.Range("H" & i).Value, vbRed, vbWhite)
         Me.Controls("Label" & J + 1).BackColor = IIf(.Range("H" & i + 1).Value, vbRed, vbWhite)
         J = J + 1
      Next i[/COLOR]
[COLOR=#00ff00]      For i = Rw + 6 To Rw + 23 Step 16[/COLOR]
[COLOR=#00ff00]         J = J + 1[/COLOR]
[COLOR=#00ff00]         If .Range("M" & i).Value <> False Then Me.Controls("Label" & J).Caption = .Range("M" & i).Value[/COLOR]
[COLOR=#00ff00]         If .Range("M" & i + 1).Value <> False Then Me.Controls("Label" & J + 1).Caption = .Range("M" & i + 1).Value[/COLOR]
[COLOR=#00ff00]         Me.Controls("Label" & J).BackColor = IIf(.Range("L" & i).Value, vbRed, vbWhite)[/COLOR]
[COLOR=#00ff00]         Me.Controls("Label" & J + 1).BackColor = IIf(.Range("L" & i + 1).Value, vbRed, vbWhite)[/COLOR]
[COLOR=#00ff00]         J = J + 1[/COLOR]
[COLOR=#00ff00]      Next [/COLOR]i
   End With
End Sub
i have try to change for the next Part But wont work that part i try to changes is in Green the Red Part Works
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,721
Office Version
365
Platform
Windows
You just need to change the first two lines of the green section, the same way I did for the red.
 

Watch MrExcel Video

Forum statistics

Threads
1,102,439
Messages
5,486,900
Members
407,570
Latest member
cannotquitexcel

This Week's Hot Topics

Top