VBA Conditional formatting alternative.

[Sarah88]

Hi All


I am very new to access and have a bit of experience with excel and have ran into a problem that I am hoping someone out there will be kind enough to give me an answer to. I was working on my first database with access and I thought it would be nice to use conditional formatting to change the cell color in my DB when I entered one of three possible responses; Yes, No, N/A to the corresponding colors Green, Red, Orange. I was able to accomplish this with conditional formatting however I was horrified after spending several hours getting all my conditional formatting set up to notice that it had stopped working for the last six columns. Apparently there is a limit on how many conditional formats you can have on one table so my question is there a way to change the cell color based on the the three possible responses; Yes, No, N/A to the corresponding colors in order Green, Red, Orange using VBA.

[June7]

How many textboxes are you doing this with?

If form is not in Datasheet or Continuous view, use VBA to set control properties.

If this is a report, try VBA in the OnFormat event. Supposed to work but I am not having luck with it right now.

[burrina]

Example:

If (Me.SomeField) = "SomeValue" Then
Me.SomeControl) = vbRed
ElseIf (Me.Somefield) = "Somevalue" Then
(Me.SomeControl) = vbGreen
ElseIf (Me.SomeControl) = "SomeValue" Then
(Me.SomeControl) = vbOrange
End If

HTH

[Missinglinq]

We really need to know what type of Form we're dealing with, here! The use of the term 'columns' makes it sound suspiciously like you're talking about a Datasheet View Form, in which your only real option is using Conditional Formatting off of the Ribbon. As June7 suggested, you can only use VBA code for this kind of formatting on a Single View Form.

I've never heard of limitations on the number of Controls that were being used with CF, but how many Fields are we actually talking about, here?

Linq ;0)>

[Sarah88]

Thank you all for replying. to answer question yes we definitely are talking about a Datasheet View Form here, but I am not sure why I can't use something like Burrina suggested. I have looked at the conditional formatting for the other six fields and they appear as ' Value = ""yes"" ' there is that extra set of quotes around the "yes" so I guess I have hit the conditional formatting limit. As for the number of fields well its 40 I want to apply this Yes,No,N/A format to. Surely there must be away to accomplish this

Thank you for taking the time to reply

[Missinglinq]

...yes we definitely are talking about a Datasheet View Form...

...I am not sure why I can't use something like Burrina suggested...

Because it is a Datasheet View Form and you cannot use VBA code to do formatting that effects the appearance of Controls in Datasheet View Forms or Continuous View Forms! In these types of Forms the formatting of ControlA, on the Current Record, will be the the formatting of ControlA, on the All Records. On these types of Forms you can only use Conditional Formatting off of the Ribbon to accomplish this.

Why is there an extra set of Double Quotes around the yes? That may be the culprit; instead of looking for Yes in the Control, the Access Gnomes may be looking for "Yes".

Linq ;0)>

[burrina]

You would need an additional counter for each record and refer to it with OpenArgs, maybe this would work, have not tried it myself!
What say you Missingling ? Would that work?

[Missinglinq]

You would need an additional counter for each record and refer to it with OpenArgs, maybe this would work, have not tried it myself!
What say you Missingling ? Would that work?

I can honestly say that I have absolutely no idea what you're talking about! What would having an additional counter for each record and referring to it with OpenArgs have to do with the problem at hand? Once again, this is a Datasheet View Form, and any formatting that changes the appearance of a Control, on this type of Form, has to be done with Conditional Formatting, not VBA code...period.
Linq ;0)>

[burrina]

http://www.lebans.com/conditionalformatting.htm Says it can be done! Have not tried it myself.

[Missinglinq]

The Lebans hacks, neat as they are, are a very complicated and only apply to highlighting the current row or alternate rows for these types of Forms; they do not apply to this problem!