Preventing Column Cell Values from Printing

[mlv]

I have a summary worksheet with several columns of information.

There is one particular column (E25:F39) where I don't always want to print
the values. Is there some simple way to toggle this column's values on and
off just before hitting the print command?

I was thinking of using an adjacent tick box outside the print area, which
when ticked would print the column values, and vice-versa.

I'm looking for a formula that would examine the tick box, and if the tick
(letter 'a' in Marlett font) is not present (i.e. cell value = ""), then the
values in column (E25:F39) would be hidden or inhibited in some way so that
the cells print blank.

Or, is there a better way of achieving this?

TIA

 

[Gary''s Student]

Here is an event macro. It examines cell A1 every time the cell is changed.
If the value is "a", then the color of the font of the range of interest is
set to black. If the value put in A1 is not "a", then the font color is set
to white. White-on-white is like hiding the cells.


Because it is worksheet code, it is very easy to install and use:

1. right-click the tab name near the bottom of the window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.

To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm

 

[Gary''s Student]

Here is the code:

Private Sub Worksheet_Change(ByVal Target As Range)
Set a = Range("A1")
If Intersect(Target, a) Is Nothing Then Exit Sub
Set rr = Range("E25:F39")
Application.EnableEvents = False
For Each r In rr
If a.Value = "a" Then
r.Font.ColorIndex = 0
Else
r.Font.ColorIndex = 2
End If
Next
Application.EnableEvents = True
End Sub

 

[mlv]

Here is an event macro. It examines cell A1 every time the cell is
changed.
If the value is "a", then the color of the font of the range of interest
is
set to black. If the value put in A1 is not "a", then the font color is
set
to white. White-on-white is like hiding the cells.

Thanks Gary. Does 'white on white' prevent the values in the cells
printing? Other programmes I use (specifically CAD progs) automatically
print white text as black.

I don't run a white background on my computer (I run a light magnolia so I
can see sneaky hidden white text in emails and on Web pages ;-), so I will
still see the values in the cells when the text is set to white.

 

[Gord Dibben]

Set the text color to light magnolia.


Gord Dibben MS Excel MVP

 

[mlv]

Set the text color to light magnolia.

There's no need as being able to see the white text against a light magnolia
background is not an issue (anyway, other people using the worksheet do run
a white background).

My question was whether setting the text colour to white would stop the text
printing. Magnolia text would print, and defeat the purpose of the exercise
(see original post).

 

[Gord Dibben]

If you print in color I don't see how magnolia text on a magnolia background
would be visible.


Gord

 

[mlv]

If you print in color I don't see how magnolia text on
a magnolia background would be visible.

'cos the magnolia background I referred to is the Windows background colour
set on my 'puter screen (control Panel/Display/Display
Properties/Advanced/Window/R244,G245,B203), it's not a background colour set
in Excel, or the colour of the paper I use, which is standard white ;-)

 

[Gord Dibben]

I see.

I thought you were referring to the background color of the cell as in
Format>Cells>Patterns


Gord

 

[Ian]

You may find, depending on the printer settings, that the white text will
still print out as black. I came across this issue a long time ago and ISTR
that if the printer driver was set to print black/white (rather than
colour), then the white text still printed.

An alternative might be to have other cells outside the print area holding
the data, then a formula in the printable cell that inserts the data if the
tickbox is checked. Something like =if(tickboxcell="a",datacell,"")

Ian

 

[mlv]

You may find, depending on the printer settings, that the white
text will still print out as black. I came across this issue a long
time ago and ISTR that if the printer driver was set to print
black/white (rather than colour), then the white text still printed.

An alternative might be to have other cells outside the print area
holding the data, then a formula in the printable cell that inserts
the data if the tickbox is checked. Something like
=if(tickboxcell="a",datacell,"")

Ian

Thanks Ian, good idea.