Solved: show, either conditional formatting, icon sets, or change font colour of userform Lbl

[gringo287]

Hi Guys,


I have a Userform that i would like to be able to have targetted font colour changes of labels. Im finding this Quite difficult to word, but, essentially i want to find out if its possible to have the font colour of a label, change, dependant on some kind of conditional formatting.

Ive searched for this one and cant find what i need. Ive acheived something similar on another userform, where i had label presenting, conditional words in seoperate labels, dependant on the targets.

Is there a way of changing the font colour of a userform label for the same purpose, or somehow show the icon set that i have set the cell to show, with conditional formatting.

[Teeroy]

You could easily code your own equivalent to a conditional format with IFs or SELECT CASE and change the label font color with the Label.ForeColor property. Just don't forget to refresh the userform to display the change.

[gringo287]

Teeroy, thank you, Ive never come across select case before, but ive just been having a read up on it and im sure this is going to solve my problem and help me no end with future projects aswell.

[gringo287]

Hi,
I cant tell what im doing wrong. Im not getting any errors, but nothing is happening. in terms of refreshing the userform, the label change is linked to a spin button, which is linked to a refresh macro so that the value in the label updates perfectly with each +1,-1, but just no colour change as of yet??.

[VBA]Private Sub LblAcwDay_Change()
Dim my_val As Long

my_val = 100
Select Case my_val
Case Is < 100: LblAcwDay.ForeColor = vbGreen
Case Is >= 100: LblAcwDay.ForeColor = vbRed
End Select
End Sub[/VBA]

[Teeroy]

Where is the code located? If it is in the userform code module (which it looks like it is) the following should work.

[vba]Private Sub LblAcwDay_Change()
Dim my_val As Long

my_val = 100
Select Case my_val
Case Is < 100: LblAcwDay.ForeColor = vbGreen
Case Is >= 100: LblAcwDay.ForeColor = vbRed
End Select
DoEvents 'This is what I meant about refreshing the userform

End Sub [/vba]

[gringo287]

Its located in the userform code. Ive stumbled across something wierd now, though. Ive actually managed to get the colour to appear, but only the VbRed is working.. the weird thing is that the colour starts black (the default colour that ive set in the properties is white "inactive boarder") and gradually gets more and more red!.

[Teeroy]

Can you post an example workbook?

[gringo287]

here you go. be aware that at the moment, it keeps bugging if the spinbutton indicator goes into minus figures..

thanks for your help

[gringo287]

now im really confused!, ive just tried a standard IF statement and its doing the same thing

[VBA]Private Sub LblCalls_Change()
Application.ScreenUpdating = False
If LblCalls.Caption >= 100 Then
LblCalls.ForeColor = vbRed
If LblCalls.Caption <= 100 Then
LblCalls.ForeColor = vbGreen
End If
End If
End Sub[/VBA]

[gringo287]

any thoughts guys?

[mikerickson]

There is no Change event for a Label.
That code should be in the spin button's Change event, where (I suspect) the caption of the label is being changed.

[gringo287]

Bingo, thank you mike.

I nw hav just one small sticking point. I have one Label working fine with the .forecolour change now and that label is linked to a cell with standard "number" formatting. The others are formatted for percentage and im getting some funky issues with these.

this is the one thats working fine

[VBA]Private Sub SpinAcw_SpinUp()
Application.ScreenUpdating = False
Sheets("Sheet1").Range("G8").Value = Sheets("Sheet1").Range("G8").Value + 1
LblAcwMin.Caption = Sheets("sheet1").Range("G8").Text
LblAcwDay.Caption = Sheets("sheet1").Range("G10").Text
If LblAcwDay.Caption <= 97 Then
LblAcwDay.ForeColor = vbGreen
If LblAcwDay.Caption >= 97 Then
LblAcwDay.ForeColor = vbRed
End If
End If
Update
End Sub

Private Sub SpinAcw_SpinDown()
Application.ScreenUpdating = False
Sheets("Sheet1").Range("G8").Value = Sheets("Sheet1").Range("G8").Value - 1
LblAcwMin.Caption = Sheets("sheet1").Range("G8").Text
LblAcwDay.Caption = Sheets("sheet1").Range("G10").Text
If LblAcwDay.Caption <= 97 Then
LblAcwDay.ForeColor = vbGreen
If LblAcwDay.Caption >= 97 Then
LblAcwDay.ForeColor = vbRed
End If
End If[/VBA]

and this is one of the ones thats playing up.

[VBA]Private Sub SpinBox1_SpinUp()
Application.ScreenUpdating = False
Sheets("Sheet1").Range("D8").Value = Sheets("Sheet1").Range("D8").Value + 1
LblOffers1.Caption = Sheets("sheet1").Range("D8").Value
LblBox1.Caption = Sheets("sheet1").Range("D10").Text
Sheets("Sheet1").Range("J8").Value = Sheets("Sheet1").Range("J8").Value + 1
If LblBox1.Caption <= "2.00" Then
LblBox1.ForeColor = vbRed
If LblBox1.Caption >= "2.00" Then
LblBox1.ForeColor = vbGreen
End If
End If
Update
End Sub
Private Sub SpinBox1_SpinDown()
Application.ScreenUpdating = False
Sheets("Sheet1").Range("D8").Value = Sheets("Sheet1").Range("D8").Value - 1
LblOffers1.Caption = Sheets("sheet1").Range("D8").Value
LblBox1.Caption = Sheets("sheet1").Range("D10").Text
Sheets("Sheet1").Range("J8").Value = Sheets("Sheet1").Range("J8").Value - 1
If LblBox1.Caption <= "2.00" Then
LblBox1.ForeColor = vbRed
If LblBox1.Caption >= "2.00" Then
LblBox1.ForeColor = vbGreen
End If
End If
Update
End Sub[/VBA]

ive tried a few variations with the % sign etc but the colour is changing at random percentages.

im sure its something to do with the percentage formatting, but im still new to this vba malarky, so not sure how to fix it.

[mikerickson]

What I would do is something like

[VBA]With Lable1
.Tag = Val(Range("A1").Value)
.Caption = Format(.Tag, "0.00 %")
If Val(.Tag) < .5 Then
.ForeColor = RGB(255, 0, 0)
Else
.ForeColor = RGB(0, 255, 0)
End If
End With[/VBA]

The issue is what data types are being used.
A cell holds numbers, a cell's formatting changes that into strings, so that a cell that holds the number .5 can show either "0.50" or "50 %" (or ...)

A Label (indeed any userform control) does not hold numbers, they hold strings.

One workaround for odd formats is to put the basic underlying numeral* into the .Tag of the control and use that for calculations or comparisons and put the formatted value into the .Caption.

*- a numeral is a string representing a number; "123" rather than 123. As strings, "19" < "234", thus Val(numeral) should be used for comparisons.

[Teeroy]

You've got a few issues.
Firstly you are taking a string (.Text property) and treating it as a number. You need to use the Val function to get it back to a number.
Secondly you are trying to do number comparisons (e.g. <=) on 2 strings.
Thirdly you've embedded If statements that are mutually exclusive so the second one will never run its contents. Change the subs you posted to the following:

[vba]Private Sub SpinAcw_SpinUp()
Application.ScreenUpdating = False
Sheets("Sheet1").Range("G8").Value = Sheets("Sheet1").Range("G8").Value + 1
LblAcwMin.Caption = Sheets("sheet1").Range("G8").Text
LblAcwDay.Caption = Sheets("sheet1").Range("G10").Text
If Val(LblAcwDay.Caption) <= 97 Then
LblAcwDay.ForeColor = vbGreen
End If
If Val(LblAcwDay.Caption) >= 97 Then
LblAcwDay.ForeColor = vbRed
End If

DoEvents
End Sub

Private Sub SpinAcw_SpinDown()
Application.ScreenUpdating = False
Sheets("Sheet1").Range("G8").Value = Sheets("Sheet1").Range("G8").Value - 1
LblAcwMin.Caption = Sheets("sheet1").Range("G8").Text
LblAcwDay.Caption = Sheets("sheet1").Range("G10").Text
If Val(LblAcwDay.Caption) <= 97 Then
LblAcwDay.ForeColor = vbGreen
End If
If Val(LblAcwDay.Caption) >= 97 Then
LblAcwDay.ForeColor = vbRed
End If

End Sub

Private Sub SpinBox1_SpinDown()
Application.ScreenUpdating = False
Sheets("Sheet1").Range("D8").Value = Sheets("Sheet1").Range("D8").Value - 1
LblOffers1.Caption = Sheets("sheet1").Range("D8").Value
LblBox1.Caption = Sheets("sheet1").Range("D10").Text
Sheets("Sheet1").Range("J8").Value = Sheets("Sheet1").Range("J8").Value - 1
If Sheets("sheet1").Range("D10").Value <= 0.02 Then
LblBox1.ForeColor = vbRed
End If
If Sheets("sheet1").Range("D10").Value >= 0.02 Then
LblBox1.ForeColor = vbGreen
End If
Update
End Sub

Private Sub SpinBox2_SpinUp()
Application.ScreenUpdating = False
Sheets("Sheet1").Range("E8").Value = Sheets("Sheet1").Range("E8").Value + 1
LblOffers2.Caption = Sheets("sheet1").Range("E8").Value
LblBox2.Caption = Sheets("sheet1").Range("E10").Text
Sheets("Sheet1").Range("J8").Value = Sheets("Sheet1").Range("J8").Value + 1
Update
End Sub

[/vba]

Some general comments;
The Vision button sets the .Forecolor property of the LblAcwDay label to a value on the spreadsheet and gives some wierd results.
The close button is only available when you expand which might get annoying when you've disabled the window close event.

[gringo287]

Thank you Mike and Teeroy for your input, it really is invaluable. i dont have time to test out your advice at the moment, but ill be testing it asap. Teeroy, with regards to the .text/.value, ive set the labels that reference percentage formatted cells, as this was the only way i could prevent too many decimal places showing. You have noticed already im sure, but i am basically learning as i go with vba etc.

[Teeroy]

You have noticed already im sure, but i am basically learning as i go with vba etc.

Aren't we all

[gringo287]

Touché

[gringo287]

sorry for the late response, guys. been mad busy the last couple of days.
both of you guys methods ended up getting used in a few ways and your comments Teeroy, were also very helpful also.

thankyou both