Page 2 of 6 FirstFirst 1234 ... LastLast
Results 11 to 20 of 55

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

  1. #11
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,392
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

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

    Try
    Code:
    If Sheets("Cup 128").Range("D5").Value Then Label1.BackColor = vbRed
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  2. #12
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,392
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

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

    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
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #13
    Board Regular
    Join Date
    Jun 2018
    Location
    Denmark
    Posts
    170
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

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

    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 by Hjemmet; Jul 19th, 2019 at 02:47 PM. Reason: Missing info

  4. #14
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,392
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

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

    You're welcome & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #15
    Board Regular
    Join Date
    Jun 2018
    Location
    Denmark
    Posts
    170
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

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

    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 = 37 To 66 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 = 39 To 64 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 = 43 To 60 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

  6. #16
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,392
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

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

    In what way doesn't it work?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  7. #17
    Board Regular
    Join Date
    Jun 2018
    Location
    Denmark
    Posts
    170
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

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

    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 by Hjemmet; Jul 21st, 2019 at 01:11 PM.

  8. #18
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,392
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

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

    Add this line as shown
    Code:
       With Sheets("Cup 128")
          j = 28
          For i = 37 To 66 Step 4
    But some of your labels are miss-named
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  9. #19
    Board Regular
    Join Date
    Jun 2018
    Location
    Denmark
    Posts
    170
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

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

    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 by Hjemmet; Jul 21st, 2019 at 02:06 PM.

  10. #20
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,392
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

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

    Rather than having loads of userforms, you can just use 1.
    Add a combobox
    & use
    Code:
    Dim Rw As Long
    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
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •