Solved If Field Contains Specific Text Then Change Back Color

[ChrisMore]

Hi,

Please can I have some VBA advice. I am writing some code so the Order_Status field back color changes depending on what text the field contains. This is what I have done so far:

Private Sub Form_Current()

Dim lngOrange As Long, lngPink As Long, lngYellow As Long, lngGreen As Long
Dim lngGrey As Long, lngRed As Long, lngPurple As Long, lngBeige As Long

lngOrange = RGB(255, 192, 0)
lngPink = RGB(255, 153, 204)
lngYellow = RGB(255, 255, 0)
lngGreen = RGB(155, 187, 89)
lngGrey = RGB(89, 89, 89)
lngRed = RGB(255, 0, 0)
lngPurple = RGB(128, 100, 162)
lngBeige = RGB(148, 138, 84)
lngWhite = RGB(255, 255, 255)

If Me!Order_Status.Value = "Direct Delivery - Awaiting Invoicing" Then
Me!Order_Status.BackColor = lngOrange
End If
If Me!Order_Status.Value = "Delayed - Delivery Date TBA" Then
Me!Order_Status.BackColor = lngPink
End If
If Me!Order_Status.Value = "Awaiting Planning" Then
Me!Order_Status.BackColor = lngYellow
End If
If Me!Order_Status.Value = "On Schedule" Then
Me!Order_Status.BackColor = lngGreen
End If
If Me!Order_Status.Value = "On hold" Then
Me!Order_Status.BackColor = lngGrey
End If
If Me!Order_Status.Value = "Cancelled" Then
Me!Order_Status.BackColor = lngRed
End If
If Me!Order_Status.Value = "Order Complete" Then
Me!Order_Status.BackColor = lngPurple
End If
If Me!Order_Status.Value = "Awaiting Collection" Then
Me!Order_Status.BackColor = lngBeige
Else
Me!Order_Status.BackColor = lngWhite
End If
End Sub

I am new to writing code and can't figure out what syntax to use for this. I am currently doing something similar to a formula by saying If Order_Status = "text" Then. It seems that this simple approach isn't working with VBA and I'm sure it's a bit more complex than that!

How can I get this to work?

Thanks very much,
Chris

 

[Gasman]

I'd probably just use Conditional formatting unless you plan on adding more conditions.?
If code I'd probably use a Case statement as well.?

Plus see Similar threads at the bottom of this post.

 

[ChrisMore]

I'd probably just use Conditional formatting unless you plan on adding more conditions.?
If code I'd probably use a Case statement as well.?

Plus see Similar threads at the bottom of this post.

Hi Gasman,

I've tried conditional formatting and I haven't been able to get it work, plus I am trying to learn VBA so thought the more I do it the more I'll learn.

What expression should I use to get the conditional formatting to work? I've tried

InStr([Order_Status],"On Schedule")>0

and

[Order_Status] Like "On Schedule"

None of these have worked. The field Back Style is Normal.

 

[Gasman]

OK, scrap the CF option as you have too many to enter. I tink you can get past the limit with vba code, but that defeats the object in this case.

Use Me.ControlName not Me!ControlName
Value is not needed in this case as it is the default value.
If you have to use If, exit the sub when true. No point testing others when you know what it is?

In that mind, I would use Select Case. https://docs.microsoft.com/en-us/of...nce/user-interface-help/select-case-statement

HTH

 

[ChrisMore]

OK, scrap the CF option as you have too many to enter. I tink you can get past the limit with vba code, but that defeats the object in this case.

Use Me.ControlName not Me!ControlName
Value is not needed in this case as it is the default value.
If you have to use If, exit the sub when true. No point testing others when you know what it is?

In that mind, I would use Select Case. https://docs.microsoft.com/en-us/of...nce/user-interface-help/select-case-statement

HTH

I have looked at Select Case and come up with this

Private Sub Form_Current()
Dim lngOrange As Long, lngPink As Long, lngYellow As Long, lngGreen As Long
Dim lngGrey As Long, lngRed As Long, lngPurple As Long, lngBeige As Long

lngOrange = RGB(255, 192, 0)
lngPink = RGB(255, 153, 204)
lngYellow = RGB(255, 255, 0)
lngGreen = RGB(155, 187, 89)
lngGrey = RGB(89, 89, 89)
lngRed = RGB(255, 0, 0)
lngPurple = RGB(128, 100, 162)
lngBeige = RGB(148, 138, 84)
lngWhite = RGB(255, 255, 255)

Select Case Me.Order_Status
Case "Direct Delivery - Awaiting Invoicing"
    Me.Order_Status.BackColor = lngOrange
Case "Delivery Date TBA"
    Me.Order_Status.BackColor = lngPink
Case "Awaiting Planning"
    Me.Order_Status.BackColor = lngYellow
Case "On Schedule"
    Me.Order_Status.BackColor = lngGreen
Case "On Hold"
    Me.Order_Status.BackColor = lngGrey
Case "Cancelled"
    Me.Order_Status.BackColor = lngRed
Case "Order Complete"
    Me.Order_Status.BackColor = lngPurple
Case "Awaiting Collection"
    Me.Order_Status.BackColor = lngBeige
Case Else
Me.Order_Status.BackColor = lngWhite
End Select
End Sub

However, its not working when I change the order status. If I change the Case Else to any of the colors it fills the back color with color so it just seems to not like the Cases I've entered...

 

[Minty]

Is your order_status a combo storing one value (an ID possibly) but displaying another text field?

In which case you are testing the second column not the value stored. Try
Select Case Me.Order_Status.Column(1)

Column numbering starts at 0

 

[ChrisMore]

Is your order_status a combo storing one value (an ID possibly) but displaying another text field?

In which case you are testing the second column not the value stored. Try
Select Case Me.Order_Status.Column(1)

Column numbering starts at 0

That did it, thanks very much for your input Minty. Thanks also Gasman for your help

 

[Gasman]

That did it, thanks very much for your input Minty. Thanks also Gasman for your help

BTW, you need to start using Option Explicit in every module as lngWhite does not exist.?

 

[ChrisMore]

BTW, you need to start using Option Explicit in every module as lngWhite does not exist.?

Good tip, I shall try to remember that for the future. I didn't even realize I had not declared lngWhite (not that it matters in this case as the combo is defaulted to white) so Option Explicit would have helped to notice that.

Cheers.

 

[Gasman]

Well I have it, and as soon as I tried to run your code, it flagged it up as not existing.
Set the Option Explicit in Tools/Options in the VBE window with 'Require variable declaration', then it is done and dusted.

 

[ChrisMore]

Well I have it, and as soon as I tried to run your code, it flagged it up as not existing.
Set the Option Explicit in Tools/Options in the VBE window with 'Require variable declaration', then it is done and dusted.

Thanks very much, I have set that option

 

[Gasman]

Thanks very much, I have set that option

It is not retrospective, so you need to add it manually for existing modules. Any new ones, will now have it automatically.