Get Control Before AND After Printing
 

Thanks for your reply.

I see what you're saying. I tried .PrintOut with the command button
approach, but not with the BeforePrint approach. But, won't I still NOT get
the print options dialog when I use .PrintOut with BeforePrint?

I guess my question was poorly stated, in that I want BOTH -- the
shading/unshading, AND the print options dialog.

Do you know any way I can get both?

Thanks,

Phil
"Jim Thomlinson" wrote:

The whole thing is done with the before print. the general flow is to cancel
the print job, do your shading, disable the events print the document and
re-enable the events...

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Cancel = True
Application.EnableEvents = False
'do your shading
ThisWorkbook.PrintOut
'undo your shading
Application.EnableEvents = True
End Sub
--
HTH...

Jim Thomlinson


"pwrichcreek" wrote:

I want to get control before printing, change the shading on certain cells so
that the shading does not show up on the printed page, and then get control
again after printing and change the shading back to what it was before
printing. The WorkbookBeforePrint event works fine and I get the desired
printing. But I don't know how to get control to change the shading back.

Is there some other event I can intercept that will let me make the
formatting change after printing?

If I use the .PrintOut method on a commnd button, I get control both before
and after printing. However, I lose the printer setup dialog (destination
printer, number of copies, etc.) that comes "for free" with the File/Print
command.

Is there something I can do to get the printer setup dialog to come up when
using .PrintOut?

TIA,

Phil

Get Control Before AND After Printing
 

Sorry forgot to mention that .printout has a number of optional arguments
that allow you to specify a number of printing parameters...
--
HTH...

Jim Thomlinson


"pwrichcreek" wrote:

Thanks for your reply.

I see what you're saying. I tried .PrintOut with the command button
approach, but not with the BeforePrint approach. But, won't I still NOT get
the print options dialog when I use .PrintOut with BeforePrint?

I guess my question was poorly stated, in that I want BOTH -- the
shading/unshading, AND the print options dialog.

Do you know any way I can get both?

Thanks,

Phil
"Jim Thomlinson" wrote:

The whole thing is done with the before print. the general flow is to cancel
the print job, do your shading, disable the events print the document and
re-enable the events...

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Cancel = True
Application.EnableEvents = False
'do your shading
ThisWorkbook.PrintOut
'undo your shading
Application.EnableEvents = True
End Sub
--
HTH...

Jim Thomlinson


"pwrichcreek" wrote:

I want to get control before printing, change the shading on certain cells so
that the shading does not show up on the printed page, and then get control
again after printing and change the shading back to what it was before
printing. The WorkbookBeforePrint event works fine and I get the desired
printing. But I don't know how to get control to change the shading back.

Is there some other event I can intercept that will let me make the
formatting change after printing?

If I use the .PrintOut method on a commnd button, I get control both before
and after printing. However, I lose the printer setup dialog (destination
printer, number of copies, etc.) that comes "for free" with the File/Print
command.

Is there something I can do to get the printer setup dialog to come up when
using .PrintOut?

TIA,

Phil

Get Control Before AND After Printing
 

Thanks again for your reply, Jim.

What I want is for the user to be unconcerned about anything that might be
going on in the background when they click the EXCEL menu File/Print command.
I want them to get the "normal" options dialog, asking what printer to print
on, how many copies, properties, etc. I started looking at a PRINT command
button, because I didn't realize I could get control both before and after,
until you clued me in on the CANCEL option in BeforePrint.

Your dialogs method does the trick. Combined with the BeforePrint Event, my
code is working exactly as I want. Here's the code.

Private Sub App_WorkbookBeforePrint(ByVal Wb As Workbook, _
Cancel As Boolean)
On Error GoTo ErrorHandler
Application.EnableEvents = False
Cancel = True
[clearCellBackground] = "1" 'set flag to clear background for print
Application.Dialogs(xlDialogPrint).Show
[clearCellBackground] = "0" 'set flag to show background on screen
ErrorHandler:
Application.EnableEvents = True
End Sub

You get a gold star! Or at least a green checkmark.

Thanks,

Phil

"Jim Thomlinson" wrote:

Before print just captures the print event. It does not indicate whether the
print was the result of the print dialog or a direct print from the print
button or printing via code. You can get a print dialog with the following
code...

Application.Dialogs(xlDialogPrint).Show

Are you trying to catch when the user clicks the print icon and bring up the
print dialog in place of printing directly?
--
HTH...

Jim Thomlinson


"pwrichcreek" wrote:

Thanks for your reply.

I see what you're saying. I tried .PrintOut with the command button
approach, but not with the BeforePrint approach. But, won't I still NOT get
the print options dialog when I use .PrintOut with BeforePrint?

I guess my question was poorly stated, in that I want BOTH -- the
shading/unshading, AND the print options dialog.

Do you know any way I can get both?

Thanks,

Phil
"Jim Thomlinson" wrote:

The whole thing is done with the before print. the general flow is to cancel
the print job, do your shading, disable the events print the document and
re-enable the events...

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Cancel = True
Application.EnableEvents = False
'do your shading
ThisWorkbook.PrintOut
'undo your shading
Application.EnableEvents = True
End Sub
--
HTH...

Jim Thomlinson


"pwrichcreek" wrote:

I want to get control before printing, change the shading on certain cells so
that the shading does not show up on the printed page, and then get control
again after printing and change the shading back to what it was before
printing. The WorkbookBeforePrint event works fine and I get the desired
printing. But I don't know how to get control to change the shading back.

Is there some other event I can intercept that will let me make the
formatting change after printing?

If I use the .PrintOut method on a commnd button, I get control both before
and after printing. However, I lose the printer setup dialog (destination
printer, number of copies, etc.) that comes "for free" with the File/Print
command.

Is there something I can do to get the printer setup dialog to come up when
using .PrintOut?

TIA,

Phil

Get Control Before AND After Printing
 

Before print just captures the print event. It does not indicate whether the
print was the result of the print dialog or a direct print from the print
button or printing via code. You can get a print dialog with the following
code...

Application.Dialogs(xlDialogPrint).Show

Are you trying to catch when the user clicks the print icon and bring up the
print dialog in place of printing directly?
--
HTH...

Jim Thomlinson


"pwrichcreek" wrote:

Thanks for your reply.

I see what you're saying. I tried .PrintOut with the command button
approach, but not with the BeforePrint approach. But, won't I still NOT get
the print options dialog when I use .PrintOut with BeforePrint?

I guess my question was poorly stated, in that I want BOTH -- the
shading/unshading, AND the print options dialog.

Do you know any way I can get both?

Thanks,

Phil
"Jim Thomlinson" wrote:

The whole thing is done with the before print. the general flow is to cancel
the print job, do your shading, disable the events print the document and
re-enable the events...

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Cancel = True
Application.EnableEvents = False
'do your shading
ThisWorkbook.PrintOut
'undo your shading
Application.EnableEvents = True
End Sub
--
HTH...

Jim Thomlinson


"pwrichcreek" wrote:

I want to get control before printing, change the shading on certain cells so
that the shading does not show up on the printed page, and then get control
again after printing and change the shading back to what it was before
printing. The WorkbookBeforePrint event works fine and I get the desired
printing. But I don't know how to get control to change the shading back.

Is there some other event I can intercept that will let me make the
formatting change after printing?

If I use the .PrintOut method on a commnd button, I get control both before
and after printing. However, I lose the printer setup dialog (destination
printer, number of copies, etc.) that comes "for free" with the File/Print
command.

Is there something I can do to get the printer setup dialog to come up when
using .PrintOut?

TIA,

Phil