Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Print worksheet but dont want two cells too print is it possible
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Print worksheet but dont want two cells too print is it possible
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) "pano" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Print worksheet but dont want two cells too print is it possible
On Oct 20, 8:46 pm, SeanC UK
wrote: 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) "pano" wrote: 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- Hide quoted text - - 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Print worksheet but dont want two cells too print is it possible
On Oct 20, 8:57 pm, pano wrote:
On Oct 20, 8:46 pm, SeanC UK wrote: 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) "pano" wrote: 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- Hide quoted text - - 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- 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").Rang e("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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Print worksheet but dont want two cells too print is it possible
See
http://www.rondebruin.nl/print.htm#Hide -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "pano" wrote in message ups.com... On Oct 20, 8:57 pm, pano wrote: On Oct 20, 8:46 pm, SeanC UK wrote: 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) "pano" wrote: 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- Hide quoted text - - 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- 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").Rang e("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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Print worksheet but dont want two cells too print is it possible
On Oct 20, 9:10 pm, "Ron de Bruin" wrote:
Seehttp://www.rondebruin.nl/print.htm#Hide -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "pano" wrote in oglegroups.com... On Oct 20, 8:57 pm, pano wrote: On Oct 20, 8:46 pm, SeanC UK wrote: 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) "pano" wrote: 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- Hide quoted text - - 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- 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").Rang e("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- Hide quoted text - - 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").Rang e("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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Print worksheet but dont want two cells too print is it possible
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 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "pano" wrote in message ps.com... On Oct 20, 9:10 pm, "Ron de Bruin" wrote: Seehttp://www.rondebruin.nl/print.htm#Hide -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "pano" wrote in oglegroups.com... On Oct 20, 8:57 pm, pano wrote: On Oct 20, 8:46 pm, SeanC UK wrote: 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) "pano" wrote: 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- Hide quoted text - - 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- 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").Rang e("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- Hide quoted text - - 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").Rang e("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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Print worksheet but dont want two cells too print is it possib
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Print worksheet but dont want two cells too print is it possib
On Oct 20, 11:01 pm, Tom Ogilvy
wrote: 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. -- Regards, Tom Ogilvy "pano" wrote: On Oct 20, 9:10 pm, "Ron de Bruin" wrote: Seehttp://www.rondebruin.nl/print.htm#Hide -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "pano" wrote in oglegroups.com... On Oct 20, 8:57 pm, pano wrote: On Oct 20, 8:46 pm, SeanC UK wrote: 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) "pano" wrote: 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- Hide quoted text - - 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- 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").Rang e("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- Hide quoted text - - 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").Rang e("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- Hide quoted text - - 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").Rang e("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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Print worksheet but dont want two cells too print is it possib
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. -- Regards, Tom Ogilvy "pano" wrote: On Oct 20, 11:01 pm, Tom Ogilvy wrote: 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. -- Regards, Tom Ogilvy "pano" wrote: On Oct 20, 9:10 pm, "Ron de Bruin" wrote: Seehttp://www.rondebruin.nl/print.htm#Hide -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "pano" wrote in oglegroups.com... On Oct 20, 8:57 pm, pano wrote: On Oct 20, 8:46 pm, SeanC UK wrote: 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) "pano" wrote: 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- Hide quoted text - - 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- 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").Rang e("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- Hide quoted text - - 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").Rang e("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- Hide quoted text - - 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").Rang e("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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dont print cell colors | Excel Worksheet Functions | |||
filled boxes with patterns dont print | Excel Discussion (Misc queries) | |||
cell borders that I create dont show on print preview or print | Excel Discussion (Misc queries) | |||
why dont cell patterns print | Excel Discussion (Misc queries) | |||
i want to print 70 work book and i dont want to open each n print | Excel Discussion (Misc queries) |