disable print

[Alberto Ast]

I would like to force the users of my program to use my print command... and
not be able to use the standard excel options to print...

can I disable those buttons/options like

 

[Jim Thomlinson]

In my experinece you are better off to let the user do what they want how
they want. The trick is to catch when they are doing things in ways you would
like to alter in some way. To that end XL has a Before Print event that you
can catch. Right click on the XL icon in the upper left corner of Excel and
choose View Code. This will take you into the VBE inside of the ThisWorkbook
module. Here is where you write code for Workbook level events such as Before
Print. Just above the Code window is a drop down on the Left which says
General. Switch that to Workbook. Now select BeforePrint from the drop down
just to the right. A code snippet will be added to your project soemthing
like this...

Private Sub Workbook_BeforePrint(Cancel As Boolean)

End Sub

Note it has 1 argument Cancel which will allow you to abort the printout by
making Cancel = True inside the procedure.

 

[מיכ×ל (מיקי) ×בידן]

You should try:
====================
Sub Disable_Print()
Application.OnKey ("^p"), ""
CommandBars("Worksheet Menu
Bar").Controls("File").Controls("Print...").Enabled = False
CommandBars("Standard").Controls(6).Enabled = False
End Sub

Sub Enable_Print()
Application.OnKey ("^p")
CommandBars("Worksheet Menu
Bar").Controls("File").Controls("Print...").Enabled = True
CommandBars("Standard").Controls(6).Enabled = True
End Sub
============
Micky

 

[Dave Peterson]

You could use an event macro.

This goes into the ThisWorkbook module.

Option Explicit
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Cancel = True
MsgBox "Please use my button to print"
End Sub

But you'll have to add a couple of lines to your code that does the printing--so
that the _BeforePrint routine doesn't get called.

Option Explicit
Sub YourPrintMacroHere()

'a bunch of code

'right before you print
application.enableevents = false
worksheets("somesheetnamehere").printout
application.enableevents = true

'more code???

End Sub

Be aware that the user can not allow macros to run and they'll be able to print
anything--no matter how you try to stop them. (Or they could even just disable
events from the immediate window of the VBE.)

 

[Alberto Ast]

Thanks Jim, I have read some about those options but never knew where to get
the different options... it was very ilustrative and help fulll. I did learn
a lot and get my needs resolved. It is a big plus for your input.

 

[Alberto Ast]

Dave, thanks for your input.... actually this is what I did with Jim
inputs... write my macro as you comment...
Have another question which hope I get a reply because once i rate a poste
normaly no more follow up is received....

What is the "Option Explicit" expression for? and when should I use it? I
have seen it several times but I do not understand it.

 

[Alberto Ast]

Mike,

Even I got it resolve with Jim's input I wanted to try your option to
lear some more... intersting what you did... now I know how to disable
several options...

great...

 

[Dave Peterson]

"Option Explicit" at the top of a module means that you want VBA to check to
make sure each variable is declared (using Dim or Const or whatever)...

If you get a warning message that you have a variable that isn't defined, then
you should declare that variable.

Dim myVar as string 'or as long or data or whatever it is.

If you declare all your variables, you won't have to spend much time debugging a
line like:

ctrl = ctr1 + 1

Those two are separate variables--one ends with "ell" and one ends with "one".

At first, you may think that this is just a pain, but you'll soon find out that
it saves much more time than the alternative.

 

[Alberto Ast]

thanks for replying Dave... do I need to write this statement on each module
or just once... what about forms and/or sheets, ThisWorkbook, etc ... do they
use it too?

 

[Dave Peterson]

It's a module by module command/setting.

If the module already exists (or you've added something to the
sheet/ThisWorkbook module), then you have to add it manually.

But you can toggle a setting for new modules.
Inside the VBE
Tools|Options|Editor tab|check "Require variable declaration"

And any new modules you create will have that line at the top.

(Yes, any module you write VBA code should have this line in it.)

========
Saved from a previous post:

I do it for a much more selfish reason.

If I add "Option Explicit" to the top of a module (or have the VBE do it for me
via tools|options|Editor tab|check require variable declaration), I know that
most of my typos will stop my code from compiling.

Then I don't have to spend minutes/hours looking at code like this:
ctr1 = ctrl + 1
(One is ctr-one and one is ctr-ell)
trying to find why my counter isn't incrementing.

And if I declare my variables nicely:

Dim wks as worksheet
not
dim wks as object
and not
dim wks as variant

I get to use the VBE's intellisense.

If I use "dim wks as worksheet", then I can type:
wks.
(including the dot)
and the VBE will pop up a list of all the properties and methods that I can
use. It saves time coding (for me anyway).

And one final selfish reason.

If I use a variable like:

Dim ThisIsACounterOfValidResponses as Long

I can type
Thisis
and hit ctrl-space and the VBE will either complete the variable name or give me
a list of things that start with those characters.

And by using a combination of upper and lower case letters in my variables, the
VBE will match the case found in the declaration statement.

ps. From what I've read, if you declare a variable as Integer, the modern pc
will have to spend time converting it to long. So I've stopped using "dim x as
integer". It's safer for me and quicker for the pc.

Same thing with "dim x as single". I'll never use it.

I'll use "dim x as double".

 

[Alberto Ast]

Very educational.... thanks.. I have set up my vbe to add Option Explicit to
each module and the leasson you give me will help me out...

yes I do some typo and mess up my logic and work a lot to fix... this should
get ride of it.

Thanks

 

[Alberto Ast]

Mickey,

I use your macros in one of my programs but it disable the print options
for all my excel files open... can I have it apply only for the excel file I
am executing the macro?