Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    2 Star Lounger
    Join Date
    Nov 2001
    Location
    Watford, Hertfordshire, England
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Warn if printing more than 1 page wide (2003 SP1)

    We have a lot of staff who do not bother to print preview, and then waste loads of paper when they find out their spreadsheet is more than a page in width with their current settings. Does anyone have a clever macro we can put on the print button that warns if the page is going to print over the page width?
    Neil Eustice
    Woody Worshipper

  2. #2
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Warn if printing more than 1 page wide (2003 SP1)

    Neil,
    In the page setup there is an option to set the page width to 1 page WIDE. Have you tried this? I may work for you depending on the relative size of your data.
    HTH

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Warn if printing more than 1 page wide (2003 SP1)

    How about something like this?

    <pre>Option Explicit
    Sub PrintMe()
    Dim sht, vResponse
    For Each sht In ActiveWindow.SelectedSheets
    If sht.VPageBreaks.Count > 0 Then
    vResponse = MsgBox( _
    prompt:="Page will print past page width." & _
    vbCrLf & "Do you still want to print?", _
    Buttons:=vbYesNo)
    If vResponse <> vbYes Then
    MsgBox "Print cancelled"
    Exit Sub
    End If
    End If
    sht.PrintOut
    Next
    End Sub</pre>


    You will have to remove the builtin print button and add your own button which runs this code...

    Steve

  4. #4
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Warn if printing more than 1 page wide (2003 SP1)

    Hi Steve,

    just out of curiousity given the desire to check the width, why wouldn't the macro use HPageBreaks.Count instead of VPage... ?

    Also, why not a BeforePrint event with the ability to cancel (I think you can do that) if the number of pages is >1? It wasn't clear to me that all worksheets would be printed at once.

    Fred

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Warn if printing more than 1 page wide (2003 SP1)

    HPageBreaks are, as the prefix seems to suggest ever so slightly, horizontal page breaks. If the used range of the worksheet is too wide for one page, this will cause a vertical page break, so it doesn't make much sense to look at horizontal page breaks.

    The Workbook_BeforePrint event procedure has a Cancel argument, so it is possible to cancel the print job. But how are you going to count the number of pages that would be printed? And if it is > 1, how can you be sure it is caused by the width of the used range of a worksheet?

  6. #6
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Warn if printing more than 1 page wide (2003 SP1)

    Thanks Hans.

    I'm forever confusing the HPage... and the VPage... properties.

    As to the 2nd item, I'm not sure I understand the question. Can't you use VPageBreaks.Count in a BeforePrint event in the same way as Steve did? I was envisioning code pretty much the same as what he posted except that it would apply to the current sheet. Perhaps if you're printing >1 sheet, that would be a problem since Steve's code looped thru the sheets. With BeforePrint, you either cancel the entire job or let it go. So if one sheet is too wide, you can't print the others. Or could you - using the manual PrintOut command per sheet and then cancel the print that was invoked by the user?

    Anyway, always trying to learn from the masters.

    Fred

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Warn if printing more than 1 page wide (2003 SP1)

    Steve's code only loops through the selected sheets (by default only the active sheet), not through all sheets.

    I don't think it would be a good idea to invoke PrintOut from the BeforePrint event. - you'd have to determine whether to display the print dialog etc.

  8. #8
    2 Star Lounger
    Join Date
    Nov 2001
    Location
    Watford, Hertfordshire, England
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Warn if printing more than 1 page wide (2003 SP1)

    Thanks Paul -I was looking for a macro that I could replace for the print button as I am aware of these options but a lot of staff do not bother trying to look first. Thanks anyway. Got some great macros advice coming through from others if you need it yourself though.
    Neil Eustice
    Woody Worshipper

  9. #9
    2 Star Lounger
    Join Date
    Nov 2001
    Location
    Watford, Hertfordshire, England
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Warn if printing more than 1 page wide (2003 SP1)

    That is superb! Thanks very much.

    Neil
    Neil Eustice
    Woody Worshipper

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Warn if printing more than 1 page wide (2003 SP1)

    Something like this?
    It does not require a button, but must be added to the workbook (It also pops up with Print Preview). It requires that all pages are not too wide or it will ask. You get all or nothing...

    Steve

    <pre>Option Explicit
    Private Sub Workbook_BeforePrint(Cancel As Boolean)
    Dim sht, vResponse
    Dim bTooWide As Boolean
    bTooWide = False
    For Each sht In ActiveWindow.SelectedSheets
    If sht.VPageBreaks.Count > 0 Then
    bTooWide = True
    End If
    Next
    If bTooWide Then
    vResponse = MsgBox( _
    prompt:="At least one Page will print past page width" & _
    vbCrLf & "Do you still want to print?", _
    Buttons:=vbYesNo)
    If vResponse <> vbYes Then
    Cancel = True
    MsgBox "Print cancelled"
    End If
    End If
    End Sub</pre>


  11. #11
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Warn if printing more than 1 page wide (2003 SP1)

    Looks good Steve. I added this to my bag of tricks.

    Just out of curiousity: could you have put the "too-wide" checking into the loop that goes thru the selected sheets? I'm not sure how Canel and the printout command work. I'm thinking that although the Print command has been invoked from the GUI that the BeforePrint event gives you ultimate control. That is, cancel the print anyway because you don't want sheets that are too wide, assuming there is one. Then go thru the selected sheets to see if each is too wide. If a sheet is too wide, then ask about printing. Proceed according to the Yes or No response. If it is not too wide, then just print it. In either case, if a sheet is to be printed, use the sht.printout command as you had in the original. That was my thinking in my original post on this thread. Seems like from what Hans said in response to that that the answer is not.

    Thanks.

    Fred

  12. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Warn if printing more than 1 page wide (2003 SP1)

    You mean like this? It will ask for each selected sheet...

    Steve

    <pre>Option Explicit
    Private Sub Workbook_BeforePrint(Cancel As Boolean)
    Dim sht, vResponse
    Application.EnableEvents = False
    For Each sht In ActiveWindow.SelectedSheets
    If sht.VPageBreaks.Count > 0 Then
    vResponse = MsgBox( _
    prompt:=sht.Name & " will print past page width" & _
    vbCrLf & "Do you still want to print it?", _
    Buttons:=vbYesNo)
    If vResponse = vbYes Then
    sht.PrintOut
    End If
    Else
    sht.PrintOut
    End If
    Next
    Cancel = True
    Application.EnableEvents = True
    End Sub</pre>


  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Warn if printing more than 1 page wide (2003 SP1)

    The problem with this event procedure is that it intercepts the File | Print... dialog, so you can't change printer settings. It also intercepts Print Preview.

  14. #14
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Warn if printing more than 1 page wide (2003 SP1)

    Y-E-S !!

    Thanks. This is good for me; don't know if original poster's needs are better served but mine are!

    Fred

  15. #15
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Warn if printing more than 1 page wide (2003 SP1)

    Yes, those are limitations of it.

    That is why I originally posted an example to replace the print dialog with some code. That routine, since it would replace the built-in printing could also be written to have something like the pritn dialog...

    Steve

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •