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

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

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

On Fri, 11 Jul 2008 14:06:01 -0700 (PDT), Andy <(E-Mail Removed)>
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

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

Gord Dibben wrote:
>
> 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
>
> On Fri, 11 Jul 2008 14:06:01 -0700 (PDT), Andy <(E-Mail Removed)>
> 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

--

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

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

--

Dave Peterson

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 <(E-Mail Removed)>
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 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.)
>
>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