Conditional Formatting or VBA

  • Thread starter Thread starter Tony
  • Start date Start date
T

Tony

Is there a way to use vba functions the same way as
conditional formatting of a field?

I have a Form with with a subform, its a scheduling form.
The subform lists the scheduled meeting for the day, I
have conditional formatting changing the colorof the
background of some fields depending on priority. 2 thinks.

1. I have about 8 options, and conditional only supports
4, that means I have to use 2 differant fields for this.

2. I would like to use a wider range of colors.

Can any body help?
 
You need to decide which form event will be usable for looping through the
controls in order to test their current contents. Perhaps you could write a
single subroutine that sets the background colors of the controls based on
sets of values, and then call that subroutine on the after update event of
each control on the form?

Code would involve these steps for setting background color:

If Me.ControlName.Value = 1 Then
Me.ControlName.BackColor = "color value"
etc.
 
CF supports 3 conditions not 4 as you stated. THere is no way around
this. YOu can simulate more criteria by having a custom function
evaluated but the FormatCOnditions object collection class only supports
three entries.

FInally, you can use any RGB color value by manipulating the
FormatCOnditions object programmatically instead of via the CF GUI.
There is code on my site demonstrating how to do this.

--

HTH
Stephen Lebans
http://www.lebans.com
Access Code, Tips and Tricks
Please respond only to the newsgroups so everyone can benefit.
 
Hi,

I'm not the original author, but I'm in a similar
situation.

The problem with your suggestion is that in a
subform/continuous forms situation, you cannot set any
control on the form without affecting all of the other
records displayed, so, for example if Me.ControlName = 1
for record 1, I can change the colour of something but
this will also apply to all of the other records
displayed.

Conditional formatting is the partial solution, but as
mentioned it only supports a limited number of
permutations (3).

If anyone has any other suggestions I'd be very
interested as I really think in this case there aren't
any!
 
This is true, but usually I use these changes in background color to
highlight issues for the record that is current. My users haven't had
difficulties with this setup. But not all will like using it.
 
Ken,

I'm probably using conditional formatting in a way that I shouldn't be. I
have another post on the topic - essentially I want to colour the sections
depending on the customer being shown - to give a graphical representation
of a timetable. Each customer has their own colour (which is picked using
the colour pick API), so there are likely to be more than 3 colours.

I'd love to be able to do this, and this limitation is all that's holding me
back. If you can think of another way to do it, I'm all ears!

John C.
 
John, I think you are stuck if you must do this in a form.
In a report, you could use the Format event of the Detail section.

Should be dead easy if the color number (RGB value) is available in the
report's RecordSource.
 
Allen,

Thanks - until now I've not had a definite 'No - you can't' - which is
actually quite helpful because I can stop looking and concentrate on an
alternative!

I would have liked the form to be fairly interactive (e.g. click on a
booking to see details, etc), so it really does need to be a form.

It looks as if I'll have to ditch the colour coding aspect, but possibly
keep it for a report they can print out and stick on the wall next to the
computer screen.

I'll be cursing access for a couple of weeks.

Thanks again!

John C.
 
Before you give up, check out Stephen Lebans' site at
www.lebans.com

You said that each customer has its color assigned to it, so
the color value must be in a table that can be joined to the
form's record source table/query. I'm pretty sure you can
then use his FormatByCriteria technique to at least get
close to what you want.
 
Marshall if the OP is maintaining a table of colors linked to each
sutomer then perhaps they could add an OLE field for each row containing
a Bitmap of 1 pixel by 1 pixel with the desired color. THis would be the
easiest solution.

--

HTH
Stephen Lebans
http://www.lebans.com
Access Code, Tips and Tricks
Please respond only to the newsgroups so everyone can benefit.


Marshall Barton said:
Before you give up, check out Stephen Lebans' site at
www.lebans.com

You said that each customer has its color assigned to it, so
the color value must be in a table that can be joined to the
form's record source table/query. I'm pretty sure you can
then use his FormatByCriteria technique to at least get
close to what you want.
--
Marsh
MVP [MS Access]



John said:
Allen,

Thanks - until now I've not had a definite 'No - you can't' - which is
actually quite helpful because I can stop looking and concentrate on an
alternative!

I would have liked the form to be fairly interactive (e.g. click on a
booking to see details, etc), so it really does need to be a form.

It looks as if I'll have to ditch the colour coding aspect, but possibly
keep it for a report they can print out and stick on the wall next to the
computer screen.

I'll be cursing access for a couple of weeks.

Thanks again!

John C.


shouldn't be.
I picked
using
Click to expand...
Click to expand...
 
Stephen,

This sounds like a good idea - I will give it a go and post back with
results.

Thanks again!

John C.

Stephen Lebans said:
Marshall if the OP is maintaining a table of colors linked to each
sutomer then perhaps they could add an OLE field for each row containing
a Bitmap of 1 pixel by 1 pixel with the desired color. THis would be the
easiest solution.

--

HTH
Stephen Lebans
http://www.lebans.com
Access Code, Tips and Tricks
Please respond only to the newsgroups so everyone can benefit.


Marshall Barton said:
Before you give up, check out Stephen Lebans' site at
www.lebans.com

You said that each customer has its color assigned to it, so
the color value must be in a table that can be joined to the
form's record source table/query. I'm pretty sure you can
then use his FormatByCriteria technique to at least get
close to what you want.
--
Marsh
MVP [MS Access]



John said:
Allen,

Thanks - until now I've not had a definite 'No - you can't' - which is
actually quite helpful because I can stop looking and concentrate on an
alternative!

I would have liked the form to be fairly interactive (e.g. click on a
booking to see details, etc), so it really does need to be a form.

It looks as if I'll have to ditch the colour coding aspect, but possibly
keep it for a report they can print out and stick on the wall next to the
computer screen.

I'll be cursing access for a couple of weeks.

Thanks again!

John C.


John, I think you are stuck if you must do this in a form.
In a report, you could use the Format event of the Detail section.

Should be dead easy if the color number (RGB value) is available in the
report's RecordSource.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message Ken,

I'm probably using conditional formatting in a way that I shouldn't be.
I
have another post on the topic - essentially I want to colour the
sections
depending on the customer being shown - to give a graphical
representation
of a timetable. Each customer has their own colour (which is picked
using
the colour pick API), so there are likely to be more than 3 colours.

I'd love to be able to do this, and this limitation is all that's
holding
me
back. If you can think of another way to do it, I'm all ears!

John C.


This is true, but usually I use these changes in background color to
highlight issues for the record that is current. My users haven't had
difficulties with this setup. But not all will like using it.

--
Ken Snell
<MS ACCESS MVP>
Hi,

I'm not the original author, but I'm in a similar
situation.

The problem with your suggestion is that in a
subform/continuous forms situation, you cannot set any
control on the form without affecting all of the other
records displayed, so, for example if Me.ControlName = 1
for record 1, I can change the colour of something but
this will also apply to all of the other records
displayed.

Conditional formatting is the partial solution, but as
mentioned it only supports a limited number of
permutations (3).

If anyone has any other suggestions I'd be very
interested as I really think in this case there aren't
any!
Click to expand...
Click to expand...
Click to expand...
 
In theory this works perfectly - keeping the single pixel and setting the
OLE control to 'Stretch' means that the OLE field takes up absolute minimum
space.

I'm still having a little trouble though - but it's something I'm sure can
be done this time.

The bound OLE control obviously needs to look at a bitmap. I have my
customer colours stored as integers - these integers were returned from the
colour pick API.

I have no problem with starting over and changing them to bitmaps, but I
just need to know how I can place a 1x1 pixel bmp in to the OLE field (with
the desired colour).

I'm looking at the moment - but if anyone has a quick answer that would be
great!

Thanks,

John C.

John Crighton said:
Stephen,

This sounds like a good idea - I will give it a go and post back with
results.

Thanks again!

John C.

Stephen Lebans said:
Marshall if the OP is maintaining a table of colors linked to each
sutomer then perhaps they could add an OLE field for each row containing
a Bitmap of 1 pixel by 1 pixel with the desired color. THis would be the
easiest solution.

--

HTH
Stephen Lebans
http://www.lebans.com
Access Code, Tips and Tricks
Please respond only to the newsgroups so everyone can benefit.


Marshall Barton said:
Before you give up, check out Stephen Lebans' site at
www.lebans.com

You said that each customer has its color assigned to it, so
the color value must be in a table that can be joined to the
form's record source table/query. I'm pretty sure you can
then use his FormatByCriteria technique to at least get
close to what you want.
--
Marsh
MVP [MS Access]



John Crighton wrote:

Allen,

Thanks - until now I've not had a definite 'No - you can't' - which is
actually quite helpful because I can stop looking and concentrate on an
alternative!

I would have liked the form to be fairly interactive (e.g. click on a
booking to see details, etc), so it really does need to be a form.

It looks as if I'll have to ditch the colour coding aspect, but possibly
keep it for a report they can print out and stick on the wall next to the
computer screen.

I'll be cursing access for a couple of weeks.

Thanks again!

John C.


John, I think you are stuck if you must do this in a form.
In a report, you could use the Format event of the Detail section.

Should be dead easy if the color number (RGB value) is available in the
report's RecordSource.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message Ken,

I'm probably using conditional formatting in a way that I shouldn't be.
I
have another post on the topic - essentially I want to colour the
sections
depending on the customer being shown - to give a graphical
representation
of a timetable. Each customer has their own colour (which is picked
using
the colour pick API), so there are likely to be more than 3 colours.

I'd love to be able to do this, and this limitation is all that's
holding
me
back. If you can think of another way to do it, I'm all ears!

John C.


This is true, but usually I use these changes in background color to
highlight issues for the record that is current. My users haven't had
difficulties with this setup. But not all will like using it.

--
Ken Snell
<MS ACCESS MVP>
Hi,

I'm not the original author, but I'm in a similar
situation.

The problem with your suggestion is that in a
subform/continuous forms situation, you cannot set any
control on the form without affecting all of the other
records displayed, so, for example if Me.ControlName = 1
for record 1, I can change the colour of something but
this will also apply to all of the other records
displayed.

Conditional formatting is the partial solution, but as
mentioned it only supports a limited number of
permutations (3).

If anyone has any other suggestions I'd be very
interested as I really think in this case there aren't
any!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top