none
Having trouble figuring out how to use FormatConditions in VBA RRS feed

  • 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.
    Monday, January 30, 2017 3:36 PM

Answers

  • 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...

    • Marked as answer by HTHP Monday, January 30, 2017 4:46 PM
    Monday, January 30, 2017 4:06 PM
  • 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...

    • Marked as answer by HTHP Monday, January 30, 2017 4:46 PM
    Monday, January 30, 2017 4:39 PM

All replies

  • 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...

    • Marked as answer by HTHP Monday, January 30, 2017 4:46 PM
    Monday, January 30, 2017 4:06 PM
  • 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?
    Monday, January 30, 2017 4:31 PM
  • 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...

    • Marked as answer by HTHP Monday, January 30, 2017 4:46 PM
    Monday, January 30, 2017 4:39 PM