help with Label on userform

H

Hjemmet

Board Regular
Joined
Jun 20, 2018
Messages
203
Hey i got some Labels on my userform

labels read value for cell's and that part works fine and if no value still ok

but if cell text is "FALSE" then my Labels show that " and I didn want it to do that..
this i the code i have on my userform
9ncB5q4

Code: 
https://ibb.co/9ncB5q4


Code: 
Private Sub UserForm_Activate()  Call start_Labels
End Sub




Sub start_Labels()
  Me.Label71.Caption = ThisWorkbook.Sheets("Cup 128").Range("E267")
  Me.Label72.Caption = ThisWorkbook.Sheets("Cup 128").Range("E273")
  Me.Label73.Caption = ThisWorkbook.Sheets("Cup 128").Range("E279")
  Me.Label74.Caption = ThisWorkbook.Sheets("Cup 128").Range("E285")
  Me.Label75.Caption = ThisWorkbook.Sheets("Cup 128").Range("E291")
  Me.Label76.Caption = ThisWorkbook.Sheets("Cup 128").Range("E297")
  Me.Label77.Caption = ThisWorkbook.Sheets("Cup 128").Range("E303")
  Me.Label78.Caption = ThisWorkbook.Sheets("Cup 128").Range("E309")
End Sub


Private Sub CommandButton40_Click() '1
flash "F264", 1, Me.Label40, Me.Label40
CommandButton40.BackColor = RGB(0, 255, 0)
CommandButton41.BackColor = RGB(255, 0, 0)
resetLabels
End Sub




Private Sub CommandButton41_Click() '2
flash "F264", 2, Me.Label41, Me.Label41
CommandButton41.BackColor = RGB(0, 255, 0)
CommandButton40.BackColor = RGB(255, 0, 0)
resetLabels
End Sub




Private Sub CommandButton42_Click() '3
flash "F270", 1, Me.Label42, Me.Label42
CommandButton42.BackColor = RGB(0, 255, 0)
CommandButton43.BackColor = RGB(255, 0, 0)
resetLabels
End Sub




Private Sub CommandButton43_Click() '4
flash "F270", 2, Me.Label43, Me.Label43
CommandButton43.BackColor = RGB(0, 255, 0)
CommandButton42.BackColor = RGB(255, 0, 0)
resetLabels
End Sub


Private Sub CommandButton44_Click() '5
flash "F276", 1, Me.Label44, Me.Label44
CommandButton44.BackColor = RGB(0, 255, 0)
CommandButton45.BackColor = RGB(255, 0, 0)
resetLabels
End Sub




Private Sub CommandButton45_Click() '6
flash "F276", 2, Me.Label45, Me.Label45
CommandButton45.BackColor = RGB(0, 255, 0)
CommandButton44.BackColor = RGB(255, 0, 0)
resetLabels
End Sub




Private Sub CommandButton46_Click() '7
flash "F282", 1, Me.Label46, Me.Label46
CommandButton46.BackColor = RGB(0, 255, 0)
CommandButton47.BackColor = RGB(255, 0, 0)
resetLabels


End Sub




Private Sub CommandButton47_Click() '8
flash "F282", 2, Me.Label47, Me.Label47
CommandButton47.BackColor = RGB(0, 255, 0)
CommandButton46.BackColor = RGB(255, 0, 0)
resetLabels
End Sub


Private Sub CommandButton48_Click() '9
flash "F288", 1, Me.Label48, Me.Label48
CommandButton48.BackColor = RGB(0, 255, 0)
CommandButton49.BackColor = RGB(255, 0, 0)
resetLabels
End Sub




Private Sub CommandButton49_Click() '10
flash "F288", 2, Me.Label49, Me.Label49
CommandButton49.BackColor = RGB(0, 255, 0)
CommandButton48.BackColor = RGB(255, 0, 0)
resetLabels
End Sub




Private Sub CommandButton50_Click() '11
flash "F294", 1, Me.Label50, Me.Label50
CommandButton50.BackColor = RGB(0, 255, 0)
CommandButton51.BackColor = RGB(255, 0, 0)
resetLabels
End Sub




Private Sub CommandButton51_Click() '12
flash "F294", 2, Me.Label51, Me.Label51
CommandButton51.BackColor = RGB(0, 255, 0)
CommandButton50.BackColor = RGB(255, 0, 0)
resetLabels
End Sub


Private Sub CommandButton52_Click() '13
flash "F300", 1, Me.Label52, Me.Label52
CommandButton52.BackColor = RGB(0, 255, 0)
CommandButton53.BackColor = RGB(255, 0, 0)
resetLabels
End Sub




Private Sub CommandButton53_Click() '14
flash "F300", 2, Me.Label53, Me.Label53
CommandButton53.BackColor = RGB(0, 255, 0)
CommandButton52.BackColor = RGB(255, 0, 0)
resetLabels
End Sub




Private Sub CommandButton54_Click() '15
flash "F306", 1, Me.Label54, Me.Label54
CommandButton54.BackColor = RGB(0, 255, 0)
CommandButton55.BackColor = RGB(255, 0, 0)
resetLabels
End Sub




Private Sub CommandButton55_Click() '16
flash "F306", 2, Me.Label55, Me.Label55
CommandButton55.BackColor = RGB(0, 255, 0)
CommandButton54.BackColor = RGB(255, 0, 0)
resetLabels
End Sub


Private Sub CommandButton56_Click() '17
flash "J267", 1, Me.Label56, Me.Label56
CommandButton56.BackColor = RGB(0, 255, 0)
CommandButton57.BackColor = RGB(255, 0, 0)
resetLabels
End Sub




Private Sub CommandButton57_Click() '18
flash "J267", 2, Me.Label57, Me.Label57
CommandButton57.BackColor = RGB(0, 255, 0)
CommandButton56.BackColor = RGB(255, 0, 0)
resetLabels
End Sub




Private Sub CommandButton58_Click() '19
flash "J279", 1, Me.Label58, Me.Label58
CommandButton58.BackColor = RGB(0, 255, 0)
CommandButton59.BackColor = RGB(255, 0, 0)
resetLabels
End Sub




Private Sub CommandButton59_Click() '20
flash "J279", 2, Me.Label59, Me.Label59
CommandButton59.BackColor = RGB(0, 255, 0)
CommandButton58.BackColor = RGB(255, 0, 0)
resetLabels
End Sub


Private Sub CommandButton60_Click() '21
flash "J291", 1, Me.Label60, Me.Label60
CommandButton60.BackColor = RGB(0, 255, 0)
CommandButton61.BackColor = RGB(255, 0, 0)
resetLabels
End Sub




Private Sub CommandButton61_Click() '22
flash "J291", 2, Me.Label61, Me.Label61
CommandButton61.BackColor = RGB(0, 255, 0)
CommandButton60.BackColor = RGB(255, 0, 0)
resetLabels
End Sub




Private Sub CommandButton62_Click() '23
flash "J303", 1, Me.Label62, Me.Label62
CommandButton62.BackColor = RGB(0, 255, 0)
CommandButton63.BackColor = RGB(255, 0, 0)
resetLabels
End Sub




Private Sub CommandButton63_Click() '24
flash "J303", 2, Me.Label63, Me.Label63
CommandButton63.BackColor = RGB(0, 255, 0)
CommandButton62.BackColor = RGB(255, 0, 0)
resetLabels
End Sub


Private Sub CommandButton64_Click() '25
flash "N273", 1, Me.Label64, Me.Label64
CommandButton64.BackColor = RGB(0, 255, 0)
CommandButton65.BackColor = RGB(255, 0, 0)
resetLabels
End Sub




Private Sub CommandButton65_Click() '26
flash "N273", 2, Me.Label65, Me.Label65
CommandButton65.BackColor = RGB(0, 255, 0)
CommandButton64.BackColor = RGB(255, 0, 0)
resetLabels
End Sub




Private Sub CommandButton66_Click() '27
flash "N297", 1, Me.Label66, Me.Label66
CommandButton66.BackColor = RGB(0, 255, 0)
CommandButton67.BackColor = RGB(255, 0, 0)
resetLabels
End Sub




Private Sub CommandButton67_Click() '28
flash "N297", 2, Me.Label67, Me.Label67
CommandButton67.BackColor = RGB(0, 255, 0)
CommandButton66.BackColor = RGB(255, 0, 0)
resetLabels
End Sub


Private Sub CommandButton68_Click() '29
flash "S285", 1, Me.Label69, Me.Label69
CommandButton68.BackColor = RGB(0, 255, 0)
CommandButton69.BackColor = RGB(255, 0, 0)
Me.Label70.Caption = ThisWorkbook.Sheets("Cup 128").Range("M285")


resetLabels
End Sub




Private Sub CommandButton69_Click() '30
flash "S285", 2, Me.Label68, Me.Label68
CommandButton69.BackColor = RGB(0, 255, 0)
CommandButton68.BackColor = RGB(255, 0, 0)
Me.Label70.Caption = ThisWorkbook.Sheets("Cup 128").Range("M285")
resetLabels
End Sub




Sub flash(strRange As String, iValue As Integer, lblOrig As MSForms.Label, lblFlash As MSForms.Label)




Sheets("Cup 128").Range(strRange).Value = iValue




Dim X As Integer
Dim OrigColor As Long: OrigColor = lblOrig.BackColor
Dim NewColor As Long: NewColor = RGB(255, 0, 100)       ' 1st cell color




OrigC = ActiveCell.Interior.ColorIndex ' ## this is not declared
 
Do Until X = 5                                          ' Flash 20 times
    DoEvents
    Start = Timer                                       ' Set timer for 1st flash rate
    Delay = Start + 0.1                                 ' Set delay for 1st cell color
    
    Do Until Timer > Delay                              ' Do until delay is exceeded = start+1
        DoEvents
        lblFlash.BackColor = NewColor                   ' Changes cell color to
    Loop
    
    Start = Timer                                       ' Set timer for 2nd flash rate
    Delay = Start + 0.1                                 ' Set delay for 2nd cell color
    
    Do Until Timer > Delay
        DoEvents
        lblFlash.BackColor = OrigColor                  ' Changes cell color to
    Loop
    
    X = X + 1                                           ' Loop increment
Loop




End Sub


Private Sub Label71_Click()
Me.Label71.Caption = ThisWorkbook.Sheets("Cup 128").Range("E267")
resetLabels
End Sub


Private Sub Label72_Click()
Me.Label72.Caption = ThisWorkbook.Sheets("Cup 128").Range("E273")
resetLabels
End Sub


Private Sub Label73_Click()
Me.Label73.Caption = ThisWorkbook.Sheets("Cup 128").Range("E279")
resetLabels
End Sub


Private Sub Label74_Click()
Me.Label74.Caption = ThisWorkbook.Sheets("Cup 128").Range("E285")
resetLabels
End Sub


Private Sub Label75_Click()
Me.Label75.Caption = ThisWorkbook.Sheets("Cup 128").Range("E291")
resetLabels
End Sub


Private Sub Label76_Click()
Me.Label76.Caption = ThisWorkbook.Sheets("Cup 128").Range("E297")
resetLabels
End Sub


Private Sub Label77_Click()
Me.Label77.Caption = ThisWorkbook.Sheets("Cup 128").Range("E303")
resetLabels
End Sub


Private Sub Label78_Click()
Me.Label78.Caption = ThisWorkbook.Sheets("Cup 128").Range("E309")
resetLabels
End Sub






Private Sub UserForm_Initialize()


resetLabels
End Sub


 


Sub resetLabels()
   With Sheets("Cup 128")
        j = 39
      For i = 264 To 307 Step 6
         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 = 267 To 304 Step 12
         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 = 273 To 298 Step 24
         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
      For i = 285 To 286 Step 2
         j = j + 1
         If .Range("R" & i).Value <> False Then Me.Controls("Label" & j).Caption = .Range("R" & i).Value
         If .Range("R" & i + 1).Value <> False Then Me.Controls("Label" & j + 1).Caption = .Range("R" & i + 1).Value
         Me.Controls("Label" & j).BackColor = IIf(.Range("Q" & i).Value, vbRed, vbWhite)
         Me.Controls("Label" & j + 1).BackColor = IIf(.Range("Q" & i + 1).Value, vbRed, vbWhite)
         j = j + 1
      Next i
   
   End With
 
Last edited:

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Sort by date Sort by votes
Each time you are going to update the label you should ask if the cell = False, for example:

Code: 
  Me.Label70.Caption = IIf(Sheets("Cup 128").Range("M285") = False, "", Sheets("Cup 128").Range("M285"))
 
Upvote 0
can i put another Quick Ask in here on this code

i have try to get those lables to Be sure that it is taken Value from the specific cell's from the Right Sheets's Sheets("Ark3")
but couldne get it to work

Code: 
'chose players in order of CupgamePrivate Sub UserForm_Initialize() 'add column of data from spreadsheet to your userform ComboBox


ComboBox1.List = Sheets("Ark3").Range("A1:A127").Value
End Sub


Private Sub ComboBox1_Change()
'Modified  8/5/2019  3:41:36 AM  EDT
Dim ans As Long
ans = ComboBox1.ListIndex + 1
    With Me    'Spiller 1
        .Controls("Label1").Caption = Cells(ans, 2).Value
        .Controls("Label3").Caption = Cells(ans, 2).Value
        .Controls("Label5").Caption = Cells(ans, 2).Value
        .Controls("Label7").Caption = Cells(ans, 2).Value
               'Spiller 2
        .Controls("Label2").Caption = Cells(ans, 3).Value
        .Controls("Label4").Caption = Cells(ans, 3).Value
        .Controls("Label6").Caption = Cells(ans, 3).Value
        .Controls("Label8").Caption = Cells(ans, 3).Value
               'Tavleføre
        .Controls("Label9").Caption = Cells(ans, 6).Value
               'Klub navn
        .Controls("Label10").Caption = Cells(ans, 7).Value
               'Klubnavn Spiller 1
        .Controls("Label11").Caption = Cells(ans, 4).Value
               'Klubnavn Spiller 2
        .Controls("Label12").Caption = Cells(ans, 5).Value
    End With
End Sub
 
Last edited:
Upvote 0
Im not sure, but try this

Code: 
[COLOR=#333333].Controls("Label1").Caption = [/COLOR][COLOR=#333333]Sheets("Ark3").[/COLOR][COLOR=#333333]Cells([/COLOR][COLOR=#ff0000]ans[/COLOR][COLOR=#333333], [/COLOR][COLOR=#ff0000]2[/COLOR][COLOR=#333333]).Value[/COLOR]

Note: cells(ans is the row, 2 is the column)
 
Upvote 0
Im glad to help you and thanks for the feedback.
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question
Check two different statements on the Exit Routine
Replies
3
Views
226
Micron
M
P
  • Solved
VBYes/No Responses
Replies
5
Views
344
Pacman52
P
P
  • Solved
Textbox BackColor Problem
Replies
5
Views
246
rlv01
R
M
  • Question
ComboBox List selection to trigger code
Replies
1
Views
223
MichaelRSnow
M
Phil Payne
  • Solved
Changing rectangle shape colour using VBA.
Replies
2
Views
757
Phil Payne
Phil Payne

Forum statistics

Threads
1,214,965
Messages
6,122,499
Members
449,089
Latest member
Raviguru

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