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

H

Hjemmet

Board Regular
Joined
Jun 20, 2018
Messages
207
i have this kind of Code in vba on my userform

Code: 
Sub resetLabels()
If Sheets("Cup 128").Range("E5").Value <> False Then Label1.Caption = Sheets("Cup 128").Range("E5").Value
End Sub

this part works fine But when i try to put the Code Below into my code then the label get Red
when Start userform and no data in Cell ("D5")

I have try to get this code into it for change the Color if ("D5") have the right Text ("Text")

Code: 
If Sheets("Cup 128").Range("D5").Text = "True" Then
        Me.Label1.BackColor = vbRed
        End If

but it aint work
 
Sort by date Sort by votes
Try
Code: 
If Sheets("Cup 128").Range("D5").Value Then Label1.BackColor = vbRed
 
Upvote 0

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
You could also replace your reset code with
Code: 
Sub resetLabels()
   With Sheets("Cup 128")
      For i = 5 To 34 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
      For i = 7 To 32 Step 8
         j = j + 1
         If .Range("I" & i).Value <> False Then Me.Controls("Label" & j).Caption = .Range("I" & i).Value
         If .Range("I" & i + 1).Value <> False Then Me.Controls("Label" & j + 1).Caption = .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
      For i = 11 To 28 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
Although you need to put the TRUE/FALSE formulae into cols H & L
 
Upvote 0
Thank you for the Help "Fluff" it seems to being your code that make my Labels on the user-form to Work

and thanks to steve To give A try to get it to work


And Now IT WORKS ON EVERY labels

YABBA DABBA DOHHHHHH
 
Last edited:
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0
Hey Fluff..

i have now try to copy all The from Userform13 to Userform 14 and Change Cell refference to All the Textbox and Labelcode

but it aint Work s it should

only on My Userform 13 where it works And i know i have to use it on each of my Userform from 13 to 21 one for each Round Before The Last Userform22
you own code But i have Now change it with the new (High lightet RED)
Code: 
Sub resetLabels()   With Sheets("Cup 128")
      For i = [B][COLOR=#ff0000]37 To 66[/COLOR][/B] 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
      For i = [B][COLOR=#ff0000]39 To 64[/COLOR] [/B]Step 8
         j = j + 1
         If .Range("I" & i).Value <> False Then Me.Controls("Label" & j).Caption = .Range("I" & i).Value
         If .Range("I" & i + 1).Value <> False Then Me.Controls("Label" & j + 1).Caption = .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
      For i = [B][COLOR=#ff0000]43 To 60[/COLOR][/B] 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
 
Upvote 0
it say's Could Not find Specified object

put a new Link for my File Here

Code: 
https://www.dropbox.com/s/csnipdo3tflywep/Total%20Changes.xlsm?dl=0
 
Last edited:
Upvote 0
Add this line as shown
Code: 
   With Sheets("Cup 128")
     [COLOR=#ff0000] j = 28[/COLOR]
      For i = 37 To 66 Step 4
But some of your labels are miss-named
 
Upvote 0
Thanks It Works Now

Yes i just Correct the Labels i find the Mistake After i have send Message to you.......

What will "J" be on userform 15 my guess is "56"
 
Last edited:
Upvote 0
Rather than having loads of userforms, you can just use 1.
Add a combobox
& use
Code: 
[COLOR=#ff0000]Dim Rw As Long[/COLOR]
Private Sub ComboBox1_Click()
   Select Case Me.ComboBox1.ListIndex
      Case 0: Rw = 5
      Case 1: Rw = 37
      Case 2: Rw = 69
      Case 3: Rw = 101
   End Select
   resetLabels
End Sub
Private Sub UserForm_Initialize()
   Dim i As Long
   For i = 1 To 8
      Me.ComboBox1.AddItem "Round " & i
   Next i
End Sub
The line in red must be at the very top of the module, before any code.
The for the Buttons use
Code: 
Private Sub CommandButton1_Click()
flash "F" & Rw, 1, Me.Label1, Me.Label1
CommandButton1.BackColor = RGB(0, 255, 0)
CommandButton2.BackColor = RGB(255, 0, 0)
resetLabels
End Sub


Private Sub CommandButton2_Click()
flash "F" & Rw, 2, Me.Label2, Me.Label2
CommandButton2.BackColor = RGB(0, 255, 0)
CommandButton1.BackColor = RGB(255, 0, 0)
resetLabels
End Sub


Private Sub CommandButton3_Click()
flash "F" & Rw + 4, 1, Me.Label3, Me.Label3
CommandButton3.BackColor = RGB(0, 255, 0)
CommandButton4.BackColor = RGB(255, 0, 0)
resetLabels
End Sub


Private Sub CommandButton4_Click()
flash "F" & Rw + 4, 2, Me.Label4, Me.Label4
CommandButton4.BackColor = RGB(0, 255, 0)
CommandButton3.BackColor = RGB(255, 0, 0)
resetLabels
End Sub

Private Sub CommandButton5_Click()
flash "F" & Rw + 8, 1, Me.Label5, Me.Label5
CommandButton5.BackColor = RGB(0, 255, 0)
CommandButton6.BackColor = RGB(255, 0, 0)
resetLabels
End Sub


Private Sub CommandButton6_Click()
flash "F" & Rw + 8, 2, Me.Label5, Me.Label6
CommandButton6.BackColor = RGB(0, 255, 0)
CommandButton5.BackColor = RGB(255, 0, 0)
resetLabels
End Sub
The Textboxes
Code: 
Private Sub TextBox1_Change()
    Range("E" & Rw).Value = TextBox1.Value ' specify the destination sheet and cell here
    resetLabels
End Sub
Private Sub TextBox2_Change()
    Range("E" & Rw + 1).Value = TextBox2.Value ' specify the destination sheet and cell here
    resetLabels
End Sub
Private Sub TextBox3_Change()
    Range("E" & Rw + 4).Value = TextBox3.Value ' specify the destination sheet and cell here
    resetLabels
End Sub
Private Sub TextBox4_Change()
    Range("E" & Rw + 5).Value = TextBox4.Value ' specify the destination sheet and cell here
    resetLabels
End Sub
and the reset sub
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
      For i = Rw + 2 To Rw + 27 Step 8
         j = j + 1
         If .Range("I" & i).Value <> False Then Me.Controls("Label" & j).Caption = .Range("I" & i).Value
         If .Range("I" & i + 1).Value <> False Then Me.Controls("Label" & j + 1).Caption = .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
      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
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
Userform inconsistent responses
Replies
1
Views
194
Candyman8019
C
P
  • Question Question
RUNTIME ERROR 91 for USERFORM CODE
Replies
1
Views
88
6StringJazzer
6StringJazzer
G
  • Question Question
VBA Userform Date Format
Replies
7
Views
184
gilly01625
G
M
  • Question Question
Modeless UserForm appears on wrong Excel workbook
Replies
5
Views
174
Jaafar Tribak
Jaafar Tribak
m_in_spain
  • Solved
Countdown timer in Userform
Replies
3
Views
385
m_in_spain
m_in_spain

Forum statistics

Threads
1,223,909
Messages
6,175,314
Members
452,634
Latest member
cpostell

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top