Dave Peterson - Can you help me, please?

[Potoroo]

Hello Dave (if you are reading this),

I am using Excel 2003 on XP.

You posted the following in a NewsGroup:

From: Dave Peterson - view profile
Date: Thurs, Sep 18 2003 9:39 am
Email: Dave Peterson <ec35...@msn.com>
Groups: microsoft.public.excel.misc

I could still select a range and then File|Print|Selection and get more stuff to
print.

I think I'd put a button from the forms toolbar on each worksheet. Then assign
it this macro which goes into a General Module:

Option Explicit
Sub myPrintRoutine()
Dim wks As Worksheet
Dim myAddr As String

Set wks = ActiveSheet

myAddr = ""
Select Case LCase(wks.Name)
Case Is = "sheet1": myAddr = "a1:j99"
Case Is = "sheet2": myAddr = "b3:l19"
End Select

Application.EnableEvents = False
If myAddr = "" Then
wks.PrintOut preview:=True
Else
wks.Range(myAddr).PrintOut preview:=True
End If
Application.EnableEvents = True

End Sub

Add as many Sheet# with the addresses to print in that "select/end select"
portion.

then use the workbook_beforeprint to cancel all print jobs that didn't come
from one of those buttons:

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

(Like Paul's code, it goes in the Thisworkbook module.)

After a shaky start (still very new to this), I have adapted it for a spreadsheet where I need to ensure the end user resets the print area before printing as other users have a tendency to set "different" print areas. Our end users often neglect to check this before printing which can cause a lot of grief.

I have adjusted the print area to be B4:X93 and attached the code to a command button which is placed above the print area (B2:C2).

In A1:A2, I have inserted a small logo, and A3 contains a heading for Column A (which is only used for the customers' reference and not required for printing.) When I run the code, it sets the print area correctly and takes me into the Print Preview.

When I continue to print from the preview screen, I don't get the dialog box option to refine further printing options. Can you please advise how to get the dialog box to display for the user to manually make any changes to the printing options? I tried recording macros and using the generated code to modify your routine but without success.

Also, if I close the Print Preview screen, the logo and heading in A3 disappear from view and the formatting in cells A1:A3 changes to match other cells in the non-printing area. If I don't back out, and then continue with the print request, A1:A3 appears the way it should. Is there something else I can do to have the spreadsheet appear unchanged if I close the print preview and return to the main spreadsheet?

I would be very thankful for any assistance.

[Potoroo]

OK.

It's a slow process for an old lady but I think I may be getting it a bit ...

The code placed in the Workbook_BeforePrint module prevents the dialog box opening. Result = I have to print without going via the dialog box.

I'll try a messagebox reminder to get the end users to make printer setting changes before continuing with the print request.

Anyone have any ideas about the mysterious behaviour of the imbedded logo and heading in the range A1:A3 when the "Close" button is used to back out of Print Preview?