Formatting Textbox
Using Conditional Formatting

VBA advantages - for greater functionality

This Formatting Textbox tip is based on a question I received about formatting a textbox in a form.  I chose to solve this problem using condtional formatting.


I have a form with a subform attached. Subform is called Products Subform and the main form is called Products. On the subform I have a textbox which does a calculation i.e.

=Sum(nz([UnitsReceived])-nz([UnitsShrinkage])-nz([UnitsSold])) it then moves this calculation up to the main form to a box called UnitsInStock =[ProductsSubform].Form!UnitsInStock Now what I would like is the following,when the units in stock figure gets to Zero (0) or less, I would like it turn red and bold and also write "NONE" in the same box. Is this possible?


To answer this question about formatting of a textbox, it had to be solved in two parts. The first was to use conditional formatting, the other using the IIF function.

To turn the formatting textbox to red or bold, use conditional formatting - found on your tool bar/ribbon:

With your form in 'Design' view, select the text box in question then, click on Format > Conditional Formatting.

Then, select the following options:

Field Value > Is Equal To > 0

Then, change the formatting to suit, your requirements


For writing the word ‘None’, if there is no stock available, try the following:

In the text box write the following:

=IIF([ProductsSubform].Form!UnitsInStock =0,"None",[ProductsSubform].Form!UnitsInStock)

The IIf statement displays the first option "None" of the statement is true; otherwise it displays the second option.

Note:  You would then have to change the conditional formatting to match, i.e. instead of:

Field Value : Is Equal To  : 0

It would be:

Field Value : Is Equal To : “None"

If the above tip does not quite meet your needs or your would like to learn a bit more on related topics, try the following:

Conditional formatting - an introduction

What is the IIF function

Nested IIF functions

Too many Nested IIF functions