Having trouble figuring out how to use FormatConditions in VBA

Question

I want to know how to do 2 different things. 1st, add a format condition depending on the value of a control. 2nd, add a format condition based on the value of a field in the underlying Recordset.

I am able to do the 2nd one, this works:

Dim ofc As FormatCondition
Set ofc = Me.cbxStatus.FormatConditions.Add(acFieldValue, acEqual, "On Display")
ofc.BackColor = RGB(0, 255, 0)


But the 1st thing I can't nail down, I've tried:
Dim ofc As FormatCondition
Set ofc = Me.cbxStatus.FormatConditions.Add(acExpression, , "Me!cbxStatus = 'On Display'")
ofc.BackColor = RGB(0, 255, 0)

But this doesn't work.

Answer 1

Hi,

Have you tried removing the "Me!" part? For example:

Set ofc = Me.cbxStatus.FormatConditions.Add(acExpression, , "[cbxStatus]='OnDisplay'")

If it doesn't work, I am not sure if you need to add an equal sign also. For instance:

Set ofc = Me.cbxStatus.FormatConditions.Add(acExpression, , "=[cbxStatus]='OnDisplay'")

Hope it helps...

Answer 2

Thanks, all seems to be working now. But I had one more questions. How can I use VBA to change the order of priority for FormatConditions that may conflict? Is it just as simple the first created has higher priority than subsequently created, or does that priority have to be edited some other way using VBA?

Answer 3

Hi,

Yes, in VBA, you can only have three formatconditions:

FormatCondition(0),

FormatCondition(1), and

FormatCondtiion(2)

As far as I know, Conditional Formatting is applied as soon as the first True condition is met.

Hope it helps...