How To Disable The File, Print... And The Print Button Functions

[Andy]

Hi Gang

Sorry to ask this again but the posts I see assume that I know a lot
about Excel programming. Can someone please explain to me like I'm 3
how to do this?

Thanks
Andy

 

[Andy]

Hi Jim

It was a figure of speech being 3. I meant can you please explain
step by step how to disable the print menu function and button. Could
you do this for me as I have a spreadsheet with a custom print
button? I don't want the users to use the normal print function.

Regards,
Andy

 

[Gord Dibben]

Andy

Assuming your custom print button runs a macro that prints only what you have
specified in that macro.............

Call that macro in a beforeprint event and it won't matter which button users
hit to print.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Call theprintmacro
End Sub


Gord Dibben MS Excel MVP

 

[Dave Peterson]

I think you'd want excel's print routine to not do any work, too:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Cancel = True
Call theprintmacro
End Sub

 

[Dave Peterson]

This is a variation of Gord's suggestion:

Create/record a macro that does the printing the way you like. Then drop a
button from the Forms toolbar onto the worksheet. Assign your macro to that
button.

Then in the ThisWorkbook module, stop any printing:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
cancel = true
Msgbox "Please click the button to print"
End Sub

=======
I'm not sure what your custom print macro will do, but you'll want to tell excel
not to its own printing.

This changes a footer and prints the sheet:

Option Explicit
Sub CustomPrintMacro()
With Worksheets("sheet999")
With .PageSetup
.LeftFooter = Format(Date, "mmmm dd, yyyy")
End With
Application.EnableEvents = False 'stop excel from trying to print!
.PrintOut
Application.EnableEvents = True
End With
End Sub


If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

 

[Gord Dibben]

Thanks for chipping in Dave.

My effort was pretty basic but I should have thought of the Cancel = True


Gord