Preventing Column Cell Values from Printing

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
--
Mike
-Please remove 'safetycatch' from email address before firing off your
reply-

GarysStudent
Mon Feb 25 13:00:07 CST 2008

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


"mlv" wrote:

> 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
> --
> Mike
> -Please remove 'safetycatch' from email address before firing off your
> reply-
>
>
>
>

GarysStudent
Mon Feb 25 14:23:04 CST 2008

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
--
Gary''s Student - gsnu2007d


"mlv" wrote:

> 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
> --
> Mike
> -Please remove 'safetycatch' from email address before firing off your
> reply-
>
>
>
>

mlv
Tue Feb 26 02:58:22 CST 2008

"Gary's Student" wrote:
>
> 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.
--
Mike
-Please remove 'safetycatch' from email address before firing off your
reply-

Gord
Tue Feb 26 15:51:16 CST 2008

Set the text color to light magnolia.


Gord Dibben MS Excel MVP

On Tue, 26 Feb 2008 08:58:22 -0000, "mlv" <mike.safetycatchvincent@jet.co.uk>
wrote:

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

mlv
Wed Feb 27 08:28:20 CST 2008

Gord Dibben wrote:
>
>> I wrote:
> >
>> 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.
>
> 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).
--
Mike
-Please remove 'safetycatch' from email address before firing off your
reply-

Gord
Wed Feb 27 11:14:47 CST 2008

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


Gord

On Wed, 27 Feb 2008 14:28:20 -0000, "mlv" <mike.safetycatchvincent@jet.co.uk>
wrote:

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

mlv
Thu Feb 28 10:06:17 CST 2008

Gord Dibben wrote:
>
> 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 ;-)
--
Mike
-Please remove 'safetycatch' from email address before firing off your
reply-

Gord
Thu Feb 28 14:31:06 CST 2008

I see.

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


Gord

On Thu, 28 Feb 2008 16:06:17 -0000, "mlv" <mike.safetycatchvincent@jet.co.uk>
wrote:

>Gord Dibben wrote:
>>
>> 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 ;-)

Ian
Fri Feb 29 03:09:45 CST 2008

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" <mike.safetycatchvincent@jet.co.uk> wrote in message
news:fq3s2a$8nm$1@north.jnrs.ja.net...
> Gord Dibben wrote:
>>
>>> I wrote:
>> >
>>> 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.
>>
>> 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).
> --
> Mike
> -Please remove 'safetycatch' from email address before firing off your
> reply-
>

mlv
Mon Mar 03 06:37:44 CST 2008

Ian wrote:
>
> 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.
--
Mike
-Please remove 'safetycatch' from email address before firing off your
reply-