Change font color of one datasheet column?

[hockey8837]

Hi,
I have a datasheet which features a few text boxes displaying additional column info from a combo box's select query; i.e. the control source is "=[FundingIDFK].[column](2)", etc.

Since this column is not an editable field, I'd like to make the text grey instead of black (as in the rest of the datasheet), so that the user sees it and will not try to click/type in the field.

Is there a way to do this through VBA, or something else? I've only been able to change the font color for the whole datasheet. I tried putting in [grey], [grey]@, "[grey]" and other various combinations in the Format property, but it doesn't seem to do anything.

I have too many text/combo boxes to try a continuous form...which seems to sometimes be a suggestion on other pages.

Thanks!

 

[Rabbie]

Try searching this forum for "conditional formatting"

 

[hockey8837]

How do I conditional format an entire column that's not necessiarly 'conditioned' to what text is in the column? I don't need it to specify what is 'between,' 'greater than,' 'equal to,' etc. I just need the whole column to color.

Is there a way to do this short of making a condition for each of the 10 or 15 possible options in the column, which may change over time?

 

[missinglinq]

Yes, but for a Datasheet View form it's kind of tricky! You have to use something like this

Expression Is

Not IsNull([ControlName])

then select the formatting you want, where ControlName is the actual name of your column.

Linq ;0)>

 

[hockey8837]

Ah! Got it!!! Thanks a million!

 

[missinglinq]

Glad we could help!

 

[Snowflake68]

Yes, but for a Datasheet View form it's kind of tricky! You have to use something like this

Expression Is

Not IsNull([ControlName])

then select the formatting you want, where ControlName is the actual name of your column.

Linq ;0)>

I have used this in the past but you then lose the Alternate row coloring, if only there was a conditional format that could make the background colour transparent.

 

[missinglinq]

Another way to do this would be to place the below in the Format Property for the Field:

[red]#

for Red, or

[green]#

for Green.

You might try this and see if it affects the Alternate Color Property, but note that it only applies to Numeric/Date Fields, and only accepts the colors listed below:

Black
Red
Green
Yellow
Blue
Magenta
Cyan
White

Linq ;0)>

 

[Snowflake68]

Another way to do this would be to place the below in the Format Property for the Field:

[red]#

for Red, or

[green]#

for Green.

You might try this and see if it affects the Alternate Color Property, but note that it only applies to Numeric/Date Fields, and only accepts the colors listed below:

Black
Red
Green
Yellow
Blue
Magenta
Cyan
White

Linq ;0)>

Thanks for this but unfortunately I need it for a text field. I did however test it on a numeric field and that works and retains the alternate row colour. Just need to find a way for it to work with text fields now.

 

[missinglinq]

Mulled over the problem, for a second or two, and tried

[Red]!

on a Text Field, and it worked! Have no idea where that came from...having been messing around with coding, since the days before Windows and Mouse Wheels and Touch Screens and such!

Linq ;0)>

 

[Snowflake68]

Mulled over the problem, for a second or two, and tried

[Red]!

on a Text Field, and it worked! Have no idea where that came from...having been messing around with coding, since the days before Windows and Mouse Wheels and Touch Screens and such!

Linq ;0)>

You are a genius. Thank you so much for your persistence in trying to find a solution to this.

I also applied this to a combo box but unfortunately it didn't work. As soon as I put [Red]! in the format of the combo box control it changes it to ![Red] which is very odd. Why would it move the Bang ?

 

[Snowflake68]

You are a genius. Thank you so much for your persistence in trying to find a solution to this.

I also applied this to a combo box but unfortunately it didn't work. As soon as I put [Red]! in the format of the combo box control it changes it to ![Red] which is very odd. Why would it move the Bang ?

Just noticed that it moves the bang on the text box controls too but it still works for them. Oh well just have to accept that it wont colour the font on a combo box.

 

[missinglinq]

I have no problem doing this with a Combobox...but you have to understand that the Formatting of the Font on a Combobox only occurs after a selection is made...and for that selection...not for the entire list when it is dropped down.

Linq ;0)>

 

[Snowflake68]

I have no problem doing this with a Combobox...but you have to understand that the Formatting of the Font on a Combobox only occurs after a selection is made...and for that selection...not for the entire list when it is dropped down.

Linq ;0)>

thanks but I couldnt get it to work. I didnt want the font on the list to be coloured anyway so i understand that, but even when I select a value from the dropdown the selected value does not change colour even though I have the format set to

![Red]

Not sure what I am doing wrong here but I have only done the same thing as I did for the text boxes on the datasheet. Would be interest to know how you have managed it so if you could send me a database with it in I would very much appreciate it. I am using Access 2013 just in case that has any bearing on the results.

 

[Snowflake68]

:banghead::banghead::banghead::banghead::banghead: DOH!

Ive just realised that the combo box I was testing it on is Numeric. What a plonker I am
I have set it to

[red]#

and all is working now and I am forever in your debt with this one. Its been driving me mad and I can now put this one to bed.

Thanks again.

 

[Snowflake68]

I have even managed to set the font of the column to the custom colour that I originally wanted. So I wanted the font on one of the columns to be Teal Green RGB (0,0,128) and all of the fonts in other columns to be black.

So for the benefit of others here is what I did.

I set the whole datasheet to RGB(0,0,128) by selecting the whole datasheet and then choosing the colour from the font colour option on the menu bar. I then set the format on all of the other text boxes on the datasheet to Black by using the format ![Black] (for Text values) and [Black]# (numeric values).

This is working like a dream and retains the Alternate row colouring as well as allowing the use of a custom colour instead of having to use on of the 8 basic vb ones.

I couldnt be happier