make textbox gray if Null

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have numerous reports with numerous textboxes each and would like to use
VBA in the Format section of the report to make the textboxes gray if they
are null. I'm trying to get around using Conditional formatting on each
textbox.

I thought I could use something like:
Dim ctl As Control
Dim lngColor As Long
lngColor = vbGrey
For Each ctl In Me.Section(0).Controls
If IsNull(ctl) Then
Clt.ForeColor = lngColor
Next

Will this work?
 
Using code will be an order of magnitude slower to execute that using
Conditional Formatting.

If you want to do it anyway, you will need to test ctl.ControlType to see if
it is acTextbox (e.g. lines don't have a ForeColor.) You will also need to
set it back to the normal color when not null. And you might need to check
if the BackStyle is transparent.
 
OK, this seems more difficult. Is there a way in the conditional formatting
to automatically reference the cell that is getting the formatting so that I
can use the Paint brush to apply the formatting to each field? I wanted an
easier way than typing [TxtBoxName] is Null in each textbox.

Thanks
 
If there are bucketloads of these, it might be worth the effort to open the
report in design view, loop through the Controls in the Section, and add
FormatConditions(0) to each one.

Since you have ctl.Name at design time, you can include the name in
Expression1 without doing it manually.

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

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

RW said:
OK, this seems more difficult. Is there a way in the conditional
formatting
to automatically reference the cell that is getting the formatting so that
I
can use the Paint brush to apply the formatting to each field? I wanted an
easier way than typing [TxtBoxName] is Null in each textbox.

Thanks

Allen Browne said:
Using code will be an order of magnitude slower to execute that using
Conditional Formatting.

If you want to do it anyway, you will need to test ctl.ControlType to see
if
it is acTextbox (e.g. lines don't have a ForeColor.) You will also need
to
set it back to the normal color when not null. And you might need to
check
if the BackStyle is transparent.
Click to expand...
Click to expand...
 
I'm not sure how to loop thru the controls in design view. Also, what does
FormatConditions(0) do?

Is what I want to do as simple as using Conditional Formatting and setting
Field Equal To Null and then using the paint brush to apply to other fields?

Allen Browne said:
If there are bucketloads of these, it might be worth the effort to open the
report in design view, loop through the Controls in the Section, and add
FormatConditions(0) to each one.

Since you have ctl.Name at design time, you can include the name in
Expression1 without doing it manually.

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

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

RW said:
OK, this seems more difficult. Is there a way in the conditional
formatting
to automatically reference the cell that is getting the formatting so that
I
can use the Paint brush to apply the formatting to each field? I wanted an
easier way than typing [TxtBoxName] is Null in each textbox.

Thanks

Allen Browne said:
Using code will be an order of magnitude slower to execute that using
Conditional Formatting.

If you want to do it anyway, you will need to test ctl.ControlType to see
if
it is acTextbox (e.g. lines don't have a ForeColor.) You will also need
to
set it back to the normal color when not null. And you might need to
check
if the BackStyle is transparent.

I have numerous reports with numerous textboxes each and would like to
use
VBA in the Format section of the report to make the textboxes gray if
they
are null. I'm trying to get around using Conditional formatting on each
textbox.

I thought I could use something like:
Dim ctl As Control
Dim lngColor As Long
lngColor = vbGrey
For Each ctl In Me.Section(0).Controls
If IsNull(ctl) Then
Clt.ForeColor = lngColor
Next

Will this work?
Click to expand...
Click to expand...
Click to expand...
 
From your original post, I assume you understand some VBA and know how to
loop through the controls.

You can do this with the report open in design view, and using a procedure
in a standard module. You can then use the Add method to add the first item
of the FormatConditions collection, and set the properties of the item.

If that is new territory, it will be quicker for you to set them manually.

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

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

RW said:
I'm not sure how to loop thru the controls in design view. Also, what does
FormatConditions(0) do?

Is what I want to do as simple as using Conditional Formatting and setting
Field Equal To Null and then using the paint brush to apply to other
fields?

Allen Browne said:
If there are bucketloads of these, it might be worth the effort to open
the
report in design view, loop through the Controls in the Section, and add
FormatConditions(0) to each one.

Since you have ctl.Name at design time, you can include the name in
Expression1 without doing it manually.

RW said:
OK, this seems more difficult. Is there a way in the conditional
formatting
to automatically reference the cell that is getting the formatting so
that
I
can use the Paint brush to apply the formatting to each field? I wanted
an
easier way than typing [TxtBoxName] is Null in each textbox.

Thanks

:

Using code will be an order of magnitude slower to execute that using
Conditional Formatting.

If you want to do it anyway, you will need to test ctl.ControlType to
see
if
it is acTextbox (e.g. lines don't have a ForeColor.) You will also
need
to
set it back to the normal color when not null. And you might need to
check
if the BackStyle is transparent.

I have numerous reports with numerous textboxes each and would like
to
use
VBA in the Format section of the report to make the textboxes gray
if
they
are null. I'm trying to get around using Conditional formatting on
each
textbox.

I thought I could use something like:
Dim ctl As Control
Dim lngColor As Long
lngColor = vbGrey
For Each ctl In Me.Section(0).Controls
If IsNull(ctl) Then
Clt.ForeColor = lngColor
Next
Click to expand...
Click to expand...
Click to expand...
 
Back
Top