Print worksheet but dont want two cells too print is it possible

[pano]

Hi, I have two cells that I dont want to print on a worksheet

I1 & J1

I cant hide the row before printing as there are other cells I want
printed

Has any one done this or have any ideas how this could be
accomplished.

Thanks in advance
Stephen

 

[Guest]

Hi Stephen,

I don't believe you can prevent individual cells from being printed,
although someone may prove me wrong.

You could try to set the font colour the same as the background colour.
Alternatively you could create a new column, put the information from these
cells in it, and then hide that column.

Sean.

 

[pano]

Hi Stephen,

I don't believe you can prevent individual cells from being printed,
although someone may prove me wrong.

You could try to set the font colour the same as the background colour.
Alternatively you could create a new column, put the information from these
cells in it, and then hide that column.

Sean.
--
(please remember to click yes if replies you receive are helpful to you)









- Show quoted text -

Ive been hunting and reading

I found this in the archives by Tom O but am having trouble getting
it to work, I gather this would be put in the worksheet .

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Application.EnableEvents = False
Dim CellArray()
Debug.Print ActiveSheet.Name
If ActiveSheet.Name = "Sheet1" Then
Set nonPrint = Range("C10:E13")
ReDim CellArray(1 To nonPrint.Count)
i = 0
For Each Cell In nonPrint
i = i + 1
CellArray(i) = nonPrint(i).NumberFormat
nonPrint(i).NumberFormat = ";;;"
Next
ActiveSheet.PrintOut
i = 0
For Each Cell In nonPrint
i = i + 1
nonPrint(i).NumberFormat = CellArray(i)
Next
Cancel = True
End If
Application.EnableEvents = True
End Sub

Tom Ogilvy

 

[pano]

Ive been hunting and reading

I found this in the archives by Tom O but am having trouble getting
it to work, I gather this would be put in the worksheet .

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Application.EnableEvents = False
Dim CellArray()
Debug.Print ActiveSheet.Name
If ActiveSheet.Name = "Sheet1" Then
Set nonPrint = Range("C10:E13")
ReDim CellArray(1 To nonPrint.Count)
i = 0
For Each Cell In nonPrint
i = i + 1
CellArray(i) = nonPrint(i).NumberFormat
nonPrint(i).NumberFormat = ";;;"
Next
ActiveSheet.PrintOut
i = 0
For Each Cell In nonPrint
i = i + 1
nonPrint(i).NumberFormat = CellArray(i)
Next
Cancel = True
End If
Application.EnableEvents = True
End Sub

Tom Ogilvy- Hide quoted text -

- Show quoted text -

This is the code I am using to print the sheets , the cells I want to
hide are on sheet "back1" I1 & J1
If I went the format the text to white in those cells where in this
code would I put it????????

Sub Front1()
'Day 1
Application.ScreenUpdating = False
If WorksheetFunction.CountBlank(Sheets("back1A").Range("e5:e40")) = 36
Then
Sheets(Array("MDN1", "back1", "front1")).Select
Else
Sheets(Array("MDN1", "back1", "back1a", "front1")).Select
End If
Sheets("front1").Activate
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Sheets("PrintMenu").Select
Application.ScreenUpdating = True
End Sub

 

[Ron de Bruin]

See
http://www.rondebruin.nl/print.htm#Hide

 

[pano]

Seehttp://www.rondebruin.nl/print.htm#Hide

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm







- Show quoted text -

Thanks for the web site Ron
Ok I'm confused.....

I place this in the This Workbook Module (after changing)
Should make the font in Cells Back1 i1 and j1 white for printing then
back to black after printing.

I run it through the debug Sub Front1() runs then calls BeforePrint
but still prints out text in cell i1 and cell j1 in black?????
I must be doing something wrong, Placed something in the wrong
place???

Private Sub Workbook_BeforePrint(Cancel As Boolean)
If ActiveSheet.Name = "Back1" Then
Cancel = True
Application.EnableEvents = False
Application.ScreenUpdating = False

'Range with one area
With ActiveSheet
.Range("i1:j1").Font.ColorIndex = 2
.PrintOut
.Range("i1:j1").Font.ColorIndex = 1
End With

Application.EnableEvents = True
Application.ScreenUpdating = True
End If
End Sub

I run it through the debug Sub Front1() runs then calls BeforePrint
but still prints out text in cell i1 and cell j1 in black?????
I must be doing something wrong

Sub Front1()
'Day 1

Application.ScreenUpdating = False
If WorksheetFunction.CountBlank(Sheets("back1A").Range("e5:e40")) = 36
Then
Sheets(Array("MDN1", "back1", "front1")).Select
Else
Sheets(Array("MDN1", "back1", "back1a", "front1")).Select
End If
Sheets("front1").Activate1
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Sheets("PrintMenu").Select
Application.ScreenUpdating = True
End Sub

 

[Ron de Bruin]

Only press the Print button in the menu
You not have to run a sub

See this page to find the Thisworkbook module
http://www.rondebruin.nl/code.htm

 

[Guest]

If you can't get my code to work, send me a sample workbook and I will see
if I can see what the problem is.

(e-mail address removed)

 

[pano]

If you can't get my code to work, send me a sample workbook and I will see
if I can see what the problem is.

(e-mail address removed)

--
Regards,
Tom Ogilvy














- Show quoted text -

Ok this post is the sub I am using to work this, tested and it works
fine thanks for the help everyone

Sub Front1()
'Day 1

Application.ScreenUpdating = False

'Change cells & txt green & format lines & unprotect sheet
Sheets("back1").Activate
ActiveSheet.Unprotect Password:="123"
Range("I1:J1").Select
With Selection.Interior
.ColorIndex = 35
.Pattern = xlSolid
End With
Selection.Font.ColorIndex = 35
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone


If WorksheetFunction.CountBlank(Sheets("back1A").Range("e5:e40")) = 36
Then
Sheets(Array("MDN1", "back1", "front1")).Select
Else
Sheets(Array("MDN1", "back1", "back1a", "front1")).Select
End If
Sheets("front1").Activate
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Sheets("PrintMenu").Select

'Change cells back again
Sheets("back1").Activate
Range("I1:J1").Select
Selection.Interior.ColorIndex = xlNone
Selection.Font.ColorIndex = 1
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
ActiveSheet.Protect Password:="123"
Range("A5").Select
Sheets("printmenu").Activate
End

Application.ScreenUpdating = True
End Sub

 

[Guest]

I guess if it is only for you, that is fine.

I have seen printers that recognize the inconsistencies in the font being
the same color as the background and correct for it so the text in the cell
is visible.