Preventing Column Cell Values from Printing

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

mlv - 25 Feb 2008 18:18 GMT

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

Signature

Mike
-Please remove 'safetycatch' from email address before firing off your
reply-

Reply to this Message

Gary''s Student - 25 Feb 2008 19:00 GMT

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

Signature

Gary''s Student - gsnu2007d

> I have a summary worksheet with several columns of information.
>
[quoted text clipped - 13 lines]
>
> TIA

Reply to this Message

mlv - 26 Feb 2008 08:58 GMT

"Gary's Student" wrote:

> Here is an event macro. It examines cell A1 every time the cell is
> changed.
[quoted text clipped - 3 lines]
> 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.

Signature

Mike
-Please remove 'safetycatch' from email address before firing off your
reply-

Reply to this Message

Gord Dibben - 26 Feb 2008 21:51 GMT

Set the text color to light magnolia.

Gord Dibben MS Excel MVP

>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.
>--

Reply to this Message

mlv - 27 Feb 2008 14:28 GMT

>> I wrote:
> >
[quoted text clipped - 4 lines]
>
> 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).

Signature

Mike
-Please remove 'safetycatch' from email address before firing off your
reply-

Reply to this Message

Gord Dibben - 27 Feb 2008 17:14 GMT

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

Gord

>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).

Reply to this Message

mlv - 28 Feb 2008 16:06 GMT

> 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 ;-)

Signature

Mike
-Please remove 'safetycatch' from email address before firing off your
reply-

Reply to this Message

Gord Dibben - 28 Feb 2008 20:31 GMT

I see.

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

Gord

>> If you print in color I don't see how magnolia text on
>> a magnolia background would be visible.
[quoted text clipped - 3 lines]
>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 ;-)

Reply to this Message

Ian - 29 Feb 2008 09:09 GMT

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

>>> I wrote:
>> >
[quoted text clipped - 12 lines]
> text printing. Magnolia text would print, and defeat the purpose of the
> exercise (see original post).

Reply to this Message

mlv - 03 Mar 2008 12:37 GMT

> 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
[quoted text clipped - 7 lines]
>
> Ian

Thanks Ian, good idea.

Signature

Mike
-Please remove 'safetycatch' from email address before firing off your
reply-

Reply to this Message

Gary''s Student - 25 Feb 2008 20:23 GMT

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

Signature

Gary''s Student - gsnu2007d

> I have a summary worksheet with several columns of information.
>
[quoted text clipped - 13 lines]
>
> TIA

Reply to this Message

Rate this thread: