Conditional Formatting (1 Viewer)

[aman909]

Hello,

Im trying to use conditional formatting in a text box on a form.

What im trying to do is that conditional formatting changes the colour of the text in the text box. I need the conditional formatting to look up another text box.

This is what i need:
i have one text box which is called colour and it shows various colours.
What i would like to do is in the other text box with conditional formatting is for the text box to change colour but only when there are certain colours in there.

For example i have these colours in my colour text box,

Red, Blue, Black, Green, Yellow.
What i would like is for the conditional formatting to work when the colour Red, Blue or Yellow is in the text box.

Can you please suggest the best way to do this.
If you need any more information please ask

 

[motleyjew]

Try putting some code in the on current event on your form like this:



Private Sub Form_Current()
If Me.colour = "Red" Then
Me.othertextbox.ForeColor = vbRed
ElseIf Me.colour = "Green" Then
Me.othertextbox.ForeColor = vbGreen
ElseIf Me.colour = "Blue" Then
Me.othertextbox.ForeColor = vbBlue
ElseIf Me.colour = "Black" Then
Me.othertextbox.ForeColor = vbBlack
ElseIf Me.colour = "Yellow" Then
Me.othertextbox.ForeColor = vbYellow
End If
End Sub


colour being the name of the text box on your form that will display the colour names and othertextbox being the text box name that you would like to change the text colour.

 

[aman909]

That is great that seems to be working fine. But the system is some what getting more advanced. It is now changed into a stock control system.

I now have colour, stock and minimum stock level.

What i would like is for the colour in the minimum stock level to change, but only when minimum stock level is greater then stock.

How do you suggest the best way is to do that?

 

[aman909]

I have tried to use this:

Private Sub Form_Current()
If Me.Colour = "Red" And Me.Minimum_Stock > Me.Stock Then
Me.Minimum_Stock.ForeColor = vbRed
ElseIf Me.Colour = "Green" And Me.Minimum_Stock > Me.Stock Then
Me.Minimum_Stock.ForeColor = vbGreen
ElseIf Me.Colour = "Blue" And Me.Minimum_Stock > Me.Stock Then
Me.Minimum_Stock.ForeColor = vbBlue
ElseIf Me.Colour = "Black" And Me.Minimum_Stock > Me.Stock Then
Me.Minimum_Stock.ForeColor = vbBlack
ElseIf Me.Colour = "Yellow" And Me.Minimum_Stock > Me.Stock Then
Me.Minimum_Stock.ForeColor = vbYellow
End If
End Sub

But im having a few problems, what happens is for example, if the colour black stock is greater then its minimum stock that stays black which it should but then if the blue minimum stock is greater then stock it goes blue which it should. The problem is now when i go back to the colour black it has turned blue. For some reason it is picking up the colour blue.

Does anyone have any ideas why and how to stop this?

 

[motleyjew]

Is the text going blue when one of your conditions is not met. If that is the case and you want your defult text to be black, put the following before End IF:

Else
Me.Minimum_Stock.ForeColor = vbBlack
End IF

This will make the text black if it does not meet any of your above rules.

 

[BadScript]

Is it also possible to change the color of the font depending on the text displayed in the textbox?..

 

[boblarson]

Get rid of all of the IF's and ElseIF's and go to a Select Case statement instead. It is cleaner and much easier to maintain. In fact, if the colors assigned may change, store the colors and the conditions in a table and use a function to pull the correct color based on the condition.

 

[BadScript]

I guess what I'm looking for is something like:

If txt_pi.Value = "FORBIDDEN" Then txt_pi.ForeColor = vbRed

but that doesn't seem to work..

 

[motleyjew]

I am assuming that txt_pi is the name of the field on your form. Maybe try this:

If Me.txt_pi = "FORBIDDEN" Then
Me.txt_pi.ForeColor = vbRed
End if

 

[BadScript]

Thanks alot, will try first thing in the morning

 

[BadScript]

I am assuming that txt_pi is the name of the field on your form. Maybe try this:

If Me.txt_pi = "FORBIDDEN" Then
Me.txt_pi.ForeColor = vbRed
End if

Indeed, the name of the textbox is txt_pi
It didn't work, mabye because I'm not using the textbox to input values but rather to display a result from a selection my the combo's; In the last combo I use:
txt_pi = Me.cbo_pg.Column(2)
in the afterupdate event....

So maybe I need something like this in the afterupdate event of my combo?:

If Me.cbo_pg.Column(2) = "FORBIDDEN"
Then txt_pi.ForeColor = vbRed
End if

 

[BadScript]

Works now, thanks alot boblarson. Appreciate the help..

 

[motleyjew]

I must thank Bob as well. I've used the case statement before but never really compared it to the If statements. You are correct in it being much easier to manage. I just tried it out and it works great. Thanks again Bob.
Gregg

 

[mattloflin]

HELP conditional formatting form VBA

I currently have this:

If Me.Description = "Print Not Available" Then
Me.Description.BackColor = vbRed
End If

description is the name of a column in a form.

It runs through this code and detects that the field = "Print..." and even runs through the .backcolor but it doesn't change on the form.

Any Ideas?

 

[BadScript]

I currently have this:

If Me.Description = "Print Not Available" Then
Me.Description.BackColor = vbRed
End If

description is the name of a column in a form.

It runs through this code and detects that the field = "Print..." and even runs through the .backcolor but it doesn't change on the form.

Any Ideas?

I'm a beginner myself, but shouldn't you use the name of your control instead of the column name? Maybe you can add a print screen so it's more obvious what you're trying to do.. It's also a good idea to use 'else' in this case, what i mean is something like:

If Me.Description = "Print Not Available" Then
Me.Description.BackColor = vbRed
Else
Me.Description.BackColor = vbWhite
End If

 

[boblarson]

I currently have this:

If Me.Description = "Print Not Available" Then
Me.Description.BackColor = vbRed
End If

description is the name of a column in a form.

It runs through this code and detects that the field = "Print..." and even runs through the .backcolor but it doesn't change on the form.

Any Ideas?

Have you made sure that the BackStyle isn't set to Transparent?