FormatCondition

[howard]

Hi there

I'd be most grateful for any help on how to, correctly,
programmatically, write an Expression, for FormatCondition.

By using FormatCondition, from the Format menu, I entered
this expression - [txtLabel].[Value]="Other" - for
txtActual, and it worked.

I tried to write this expression in VBA as;

Set myFC = Me.[txtActual].FormatConditions.Add
(acExpression, ,[txtLabel].[Value]="Other")

However, I got an error message that said: I entered an
expression that had no value.

How do I write this sort of expression, properly, in VBA?

Thanks
H

 

[Marshall Barton]

I'd be most grateful for any help on how to, correctly,
programmatically, write an Expression, for FormatCondition.

By using FormatCondition, from the Format menu, I entered
this expression - [txtLabel].[Value]="Other" - for
txtActual, and it worked.

I tried to write this expression in VBA as;

Set myFC = Me.[txtActual].FormatConditions.Add
(acExpression, ,[txtLabel].[Value]="Other")

However, I got an error message that said: I entered an
expression that had no value.

How do I write this sort of expression, properly, in VBA?

OK, since you haven't gotten any other responses, I take a
shot at it (with the caveat that I don't use conditional
formatting this way).

First off, I believe that the expression must be a text
string.

Second, that is a method call and I don't think it can be
used in an assignment statement. Even I'm wrong about this,
I think it's a poor coding style to combine so much in one
line

Third, I believe the add method can only be used in design
view, so, unless you're creating a design time Wizard type
of procedure, you can not do this (and if you want to do
this at run time, you definitely should not do it).

In design view, I think this might be closer to what you
want:

With Me.[txtActual].FormatConditions
.Add acExpression, ,"txtLabel.Value=""Other"""
Set myFC = .Item(.Count - 1)

Maybe what you're really looking for is to set the
expression to a user defined function that will be called at
run time?? If so (I'm really getting in over my head here),
then maybe you could accomplish what you need by setting
some other FormatCondition properties at run time.

Regardless of what I say or think, you should definitely
look at Stephen Lebans' Examples of Conditional Formatting
at www.lebans.com

 

[howard]

Nice one Marshall, your response, including the
recommended site, was most helpful. Thanks.

H

-----Original Message-----

I'd be most grateful for any help on how to, correctly,
programmatically, write an Expression, for FormatCondition.

By using FormatCondition, from the Format menu, I entered
this expression - [txtLabel].[Value]="Other" - for
txtActual, and it worked.

I tried to write this expression in VBA as;

Set myFC = Me.[txtActual].FormatConditions.Add
(acExpression, ,[txtLabel].[Value]="Other")

However, I got an error message that said: I entered an
expression that had no value.

How do I write this sort of expression, properly, in VBA?

OK, since you haven't gotten any other responses, I take a
shot at it (with the caveat that I don't use conditional
formatting this way).

First off, I believe that the expression must be a text
string.

Second, that is a method call and I don't think it can be
used in an assignment statement. Even I'm wrong about this,
I think it's a poor coding style to combine so much in one
line

Third, I believe the add method can only be used in design
view, so, unless you're creating a design time Wizard type
of procedure, you can not do this (and if you want to do
this at run time, you definitely should not do it).

In design view, I think this might be closer to what you
want:

With Me.[txtActual].FormatConditions
.Add acExpression, ,"txtLabel.Value=""Other"""
Set myFC = .Item(.Count - 1)

Maybe what you're really looking for is to set the
expression to a user defined function that will be called at
run time?? If so (I'm really getting in over my head here),
then maybe you could accomplish what you need by setting
some other FormatCondition properties at run time.

Regardless of what I say or think, you should definitely
look at Stephen Lebans' Examples of Conditional Formatting
at www.lebans.com