I have 2 macros one for showing the print dialog (connected to a clipart
image of a printer) and the other to white out certain cells before print so
that they don't show. For some reason the print macro no longer works now
that I put in the before print macro. Any ideas? Fix? Here is my code below:

(I am running excel 2003, code below is in "thisworkbook")

Sub PrintSheet()
Application.Dialogs(xlDialogPrint).Show
End Sub

Private Sub Workbook_BeforePrint(Cancel As Boolean)
If ActiveSheet.Name = "sheet1" Then
Cancel = True
Application.EnableEvents = False
Application.ScreenUpdating = False
With ActiveSheet
.Range("C4").Font.ColorIndex = 2
.PrintOut
.Range("C4").Font.ColorIndex = 1
End With
Application.EnableEvents = True
Application.ScreenUpdating = True
End If
End Sub

The line
Cancel = True

Cancels the print job. it does not sound like that is your intention. Try
removing that line...
--
HTH...

Jim Thomlinson


"dgold82" wrote:

I have 2 macros one for showing the print dialog (connected to a clipart
image of a printer) and the other to white out certain cells before print so
that they don't show. For some reason the print macro no longer works now
that I put in the before print macro. Any ideas? Fix? Here is my code below:

(I am running excel 2003, code below is in "thisworkbook")

Sub PrintSheet()
Application.Dialogs(xlDialogPrint).Show
End Sub

Private Sub Workbook_BeforePrint(Cancel As Boolean)
If ActiveSheet.Name = "sheet1" Then
Cancel = True
Application.EnableEvents = False
Application.ScreenUpdating = False
With ActiveSheet
.Range("C4").Font.ColorIndex = 2
.PrintOut
.Range("C4").Font.ColorIndex = 1
End With
Application.EnableEvents = True
Application.ScreenUpdating = True
End If
End Sub

I am now officially lost. Do you have a print button on each sheet? If so
they are all attached to the same code that brings up the print dialog? Your
intention is to catch when Sheet 1 is being printed and to white out Cell C4
before the sheet is printed? Is the dialog supposed to show up twice when
sheet 1 is printed?

What is the point to bringing up the print dialog since the user could just
hit print button on the standard XL menu.

--
HTH...

Jim Thomlinson


"dgold82" wrote:

I a bit of a beginner at VBA so bear with me a bit. I removed the cancel=true
and I got the dialog box back for printing. Only problem is that now the code
to whiteout cell C4 in "sheet 1" no longer works.

If I am in any other worksheet (I have a print button on sheet2 as well) the
button works fine. Must be because the whiteout macro disables the print
dialog box event only on sheet1 and forces a straight to default printer. Is
there code that mimics a quick print rather than trying to pull up the
dialog? That might fix this.

Thanks.

The

"Jim Thomlinson" wrote:

Sorry I missed the
.printout

What part is not working... Are your events enabled. If at any point during
debuggin you stopped the code prior to

Application.EnableEvents = True

Then your events are not firing...
--
--
HTH...

Jim Thomlinson


"Jim Thomlinson" wrote:

The line
Cancel = True

Cancels the print job. it does not sound like that is your intention. Try
removing that line...
--
HTH...

Jim Thomlinson


"dgold82" wrote:

I have 2 macros one for showing the print dialog (connected to a clipart
image of a printer) and the other to white out certain cells before print so
that they don't show. For some reason the print macro no longer works now
that I put in the before print macro. Any ideas? Fix? Here is my code below:

(I am running excel 2003, code below is in "thisworkbook")

Sub PrintSheet()
Application.Dialogs(xlDialogPrint).Show
End Sub

Private Sub Workbook_BeforePrint(Cancel As Boolean)
If ActiveSheet.Name = "sheet1" Then
Cancel = True
Application.EnableEvents = False
Application.ScreenUpdating = False
With ActiveSheet
.Range("C4").Font.ColorIndex = 2
.PrintOut
.Range("C4").Font.ColorIndex = 1
End With
Application.EnableEvents = True
Application.ScreenUpdating = True
End If
End Sub

Seems like you mostly understand what's going on. I do have a print button on
each sheet and they are all attached to the same code that brings up the
print dialog. There is a cell on sheet1 that I wanted whited out so I added
the second set of code to do that. I now realize that I don't think I can
call up the print dialog and have that second set of code work properly.
Unless you know a way around it.

Perhaps changing the code to the quick print and making a separate button
for sheet1 might do it since it doesn't call up the dialog? Do you know how?

I didn't think you would ask, but my users can't pull up the print in the XL
menu (or 2007 ribbon) because I have disabled them by forcing full screen.
Long story.

"Jim Thomlinson" wrote:

I am now officially lost. Do you have a print button on each sheet? If so
they are all attached to the same code that brings up the print dialog? Your
intention is to catch when Sheet 1 is being printed and to white out Cell C4
before the sheet is printed? Is the dialog supposed to show up twice when
sheet 1 is printed?

What is the point to bringing up the print dialog since the user could just
hit print button on the standard XL menu.

--
HTH...

Jim Thomlinson


"dgold82" wrote:

I a bit of a beginner at VBA so bear with me a bit. I removed the cancel=true
and I got the dialog box back for printing. Only problem is that now the code
to whiteout cell C4 in "sheet 1" no longer works.

If I am in any other worksheet (I have a print button on sheet2 as well) the
button works fine. Must be because the whiteout macro disables the print
dialog box event only on sheet1 and forces a straight to default printer. Is
there code that mimics a quick print rather than trying to pull up the
dialog? That might fix this.

Thanks.

The

"Jim Thomlinson" wrote:

Sorry I missed the
.printout

What part is not working... Are your events enabled. If at any point during
debuggin you stopped the code prior to

Application.EnableEvents = True

Then your events are not firing...
--
--
HTH...

Jim Thomlinson


"Jim Thomlinson" wrote:

The line
Cancel = True

Cancels the print job. it does not sound like that is your intention. Try
removing that line...
--
HTH...

Jim Thomlinson


"dgold82" wrote:

I have 2 macros one for showing the print dialog (connected to a clipart
image of a printer) and the other to white out certain cells before print so
that they don't show. For some reason the print macro no longer works now
that I put in the before print macro. Any ideas? Fix? Here is my code below:

(I am running excel 2003, code below is in "thisworkbook")

Sub PrintSheet()
Application.Dialogs(xlDialogPrint).Show
End Sub

Private Sub Workbook_BeforePrint(Cancel As Boolean)
If ActiveSheet.Name = "sheet1" Then
Cancel = True
Application.EnableEvents = False
Application.ScreenUpdating = False
With ActiveSheet
.Range("C4").Font.ColorIndex = 2
.PrintOut
.Range("C4").Font.ColorIndex = 1
End With
Application.EnableEvents = True
Application.ScreenUpdating = True
End If
End Sub