Thanks for chipping in Dave.

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


Gord

On Fri, 11 Jul 2008 17:09:56 -0500, Dave Peterson
wrote:

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 he
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.)

Andy wrote:

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