Prevent Printing in Print Preview?

[JRK]

Is there a way to prevent a user from printing in Print Preview? I don't
think I can disable the button using VBA, but perhaps someone knows of a way
to prevent the print command using VBA. It would be great if procedure works
for Excel 97 + Thank you.

 

[Neptune Dinosaur]

There is a way to do this in XL2003 which may exist in 97 also. It actually
prevents normal printing entirely, but you allow the user to print by
providing a command button. The command button has code that gives the
appropriate print commands(as fits your purpose). You use the
Workbook_BeforePrint event to inhibit printing (you inhibit with the command
"Cancel = True") unless a boolean variable is set to True. You set this
boolean to True at the top of the code that runs when the command button is
pressed, and back to False at the end of it.

In the Workbook_BeforePrint event code, you set Cancel to True only if the
boolean variable is False, i.e. if the user accesses any Print-related
function (including Preview) by means other than your button, Cancel will be
True and the Print will not go ahead. I've used this several times in XL
2003 and it works a treat.

 

[JRK]

There is a way to do this in XL2003 which may exist in 97 also. It actually
prevents normal printing entirely, but you allow the user to print by
providing a command button. The command button has code that gives the
appropriate print commands(as fits your purpose). You use the
Workbook_BeforePrint event to inhibit printing (you inhibit with the command
"Cancel = True") unless a boolean variable is set to True. You set this
boolean to True at the top of the code that runs when the command button is
pressed, and back to False at the end of it.

In the Workbook_BeforePrint event code, you set Cancel to True only if the
boolean variable is False, i.e. if the user accesses any Print-related
function (including Preview) by means other than your button, Cancel will be
True and the Print will not go ahead. I've used this several times in XL
2003 and it works a treat.

Thank you, Neptune. I'm a bit of a novice, however. Can you please give me
the procedure and where to post. I currently use a sub to activate Print
Preview

With Sheets("my sheet")
.Visible = True
.PrintOut preview:=True
.Visible = False
End With

This works well. Now if you would just be so kind as to tell me how and
where I can stop the print command. Thank you, again.

 

[Neptune Dinosaur]

*Go to the Visual Basic Editor (from Excel, go Tools/Macro/Visual Basic
Editor)
*In the VB Editor, make sure the Project Explorer window is visible
(View/Project Explorer - there's also a button up in the toolbar for this,
which you will find easily enough if you nose around for it)
*In the Project Explorer, expand the "tree" for the VBA project that you
want to work on - it will have the name of the relevant Excel file in brackets
*DoubleClick on the "This Workbook" object within that tree
* There will be 2 dropdown boxes up near the top of the screen. Make sure
the left hand one says "Workbook" rather than "General"
* Scroll around in the right hand dropdown for "BeforePrint". When you
select it, the editor will place the"shell" of a Sub in the code window for
you. Whack your code in there and off you go. Note that the Sub comes
pre-loaded with the parameter "Cancel as Boolean" - If you go "Cancel =
True", the Print will not go ahead. Obviously you can play around with that
and make it conditional, as I described before.
* Hope this is all OK in XL97 !!!!!!

It's worthwhile having a good look at all of the other events that you can
harness in a Workbook and on individual Sheets. These are things that can
help turn little macro-driven Excel files into real interactive
mini-applications suitable for users who don't have much IT savvy (welcome to
my world ...)

PS: My name is not really Neptune, but time really is just the thing that
keeps everything from happening all at once

 

[JRK]

*Go to the Visual Basic Editor (from Excel, go Tools/Macro/Visual Basic
Editor)
*In the VB Editor, make sure the Project Explorer window is visible
(View/Project Explorer - there's also a button up in the toolbar for this,
which you will find easily enough if you nose around for it)
*In the Project Explorer, expand the "tree" for the VBA project that you
want to work on - it will have the name of the relevant Excel file in brackets
*DoubleClick on the "This Workbook" object within that tree
* There will be 2 dropdown boxes up near the top of the screen. Make sure
the left hand one says "Workbook" rather than "General"
* Scroll around in the right hand dropdown for "BeforePrint". When you
select it, the editor will place the"shell" of a Sub in the code window for
you. Whack your code in there and off you go. Note that the Sub comes
pre-loaded with the parameter "Cancel as Boolean" - If you go "Cancel =
True", the Print will not go ahead. Obviously you can play around with that
and make it conditional, as I described before.
* Hope this is all OK in XL97 !!!!!!

It's worthwhile having a good look at all of the other events that you can
harness in a Workbook and on individual Sheets. These are things that can
help turn little macro-driven Excel files into real interactive
mini-applications suitable for users who don't have much IT savvy (welcome to
my world ...)

PS: My name is not really Neptune, but time really is just the thing that
keeps everything from happening all at once

Thank you for that information. I'll see if I can make it work.