Solved If Field Contains Specific Text Then Change Back Color (1 Viewer)

ChrisMore

Member
Local time
Today, 09:27
Joined
Jan 28, 2020
Messages
97
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:

Code:
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

Enthusiastic Amateur
Local time
Today, 09:27
Joined
Sep 21, 2011
Messages
7,450
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

Member
Local time
Today, 09:27
Joined
Jan 28, 2020
Messages
97
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

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

and

Code:
[Order_Status] Like "On Schedule"

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

Gasman

Enthusiastic Amateur
Local time
Today, 09:27
Joined
Sep 21, 2011
Messages
7,450
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

Member
Local time
Today, 09:27
Joined
Jan 28, 2020
Messages
97
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

Code:
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

AWF VIP
Local time
Today, 09:27
Joined
Jul 26, 2013
Messages
7,735
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

Member
Local time
Today, 09:27
Joined
Jan 28, 2020
Messages
97
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 :giggle:
 
Last edited:

Gasman

Enthusiastic Amateur
Local time
Today, 09:27
Joined
Sep 21, 2011
Messages
7,450
That did it, thanks very much for your input Minty. Thanks also Gasman for your help :giggle:
BTW, you need to start using Option Explicit in every module as lngWhite does not exist.?
 

ChrisMore

Member
Local time
Today, 09:27
Joined
Jan 28, 2020
Messages
97
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

Enthusiastic Amateur
Local time
Today, 09:27
Joined
Sep 21, 2011
Messages
7,450
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

Member
Local time
Today, 09:27
Joined
Jan 28, 2020
Messages
97
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 (y):)
 

Gasman

Enthusiastic Amateur
Local time
Today, 09:27
Joined
Sep 21, 2011
Messages
7,450
Thanks very much, I have set that option (y):)
It is not retrospective, so you need to add it manually for existing modules. Any new ones, will now have it automatically.
 

Users who are viewing this thread

Top Bottom