print preview not working

[ira daboy]

I am trying to build a button to print two specific sheets, "PS" and "PL" are the name of the worksheets. But before the print I would like to preivew and if the preview is not acceptable then stop executing code. what is happening in the code i have build, if i Xout of the print preview it goes ahead and print anyway. does anyone know how i can stop it from printing if the preview in not acceptable? Below is the code i have so far. thanx

Private Sub CommandButton4_Click()
  Dim PrntRng As Range
Set PrntRng = Application.InputBox("Select Range To Print", "Select Range", Type:=8)
ActiveSheet.PageSetup.PrintArea = PrntRng.Address
Debug.Print PrntRng.Columns.Count
Debug.Print PrntRng.Rows.Count

Sheets(Array("PS", "PL")).PrintPreview
    Sheets(Array("PS", "PL")).PrintOut
    Sheets("PL").Select
End Sub

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

When posting vba code in the forum, please use the available code tags. My signature block below has more details. I fixed it for you this time.

Would adding this line work for you?

Sheets(Array("PS", "PL")).PrintPreview
If MsgBox("Confirm to print?", vbYesNo) = vbYes Then Sheets(Array("PS", "PL")).PrintOut
Sheets("PL").Select

 

[ira daboy]

thank you Peter, that worked great. and i have taken your advise and updated my account info.

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

.. and thanks for updating your details.

 

[ira daboy]

Hi Peter, I would like to update this a little more to make the user pick a range. And if no range is selected it will say "You must pick a print range" then give them an opportunity to pick a range or cancel.
This code as it is asks me to debug or end. Is this possible?

 

[Peter_SSs]

I don't have enough information to write any full code for you.

You apparently want to print two worksheets (PS and PL) but

• You are only setting the print range on one sheet, and
• That one sheet that you are setting the print range on may not even be one of the sheets to be printed. In your code, Print range is being applied to the ActiveSheet which, as far as I know, could be any worksheet in the workbook.

 

[ira daboy]

Sorry, I have actually changed my code a bit. Below is what I have now. PL will always be the active sheet, this is where my command button is. So when execute my code if I don't select a range or if I click "Cancel" I get and error (see attached). Instead of an error I would like a msg box to come up and say "You must pick a print range" or if cancel is selected it will terminate the code without an error. thanks for your help with this.


Private Sub CommandButton4_Click()
Dim PrntRng As Range
Set PrntRng = Application.InputBox("Select Range To Print", "Select Range", Type:=8)

ActiveSheet.PageSetup.PrintArea = PrntRng.Address
Debug.Print PrntRng.Columns.Count
Debug.Print PrntRng.Rows.Count
Sheets(Array("PS", "PL")).Select
Application.Dialogs(xlDialogPrint).Show
Sheets("PL").Select
End Sub

 

[Peter_SSs]

What sheet is CommandButton4 on?

 

[ira daboy]

CommandButton4 is on Sheet4 (PL)

 

[Peter_SSs]

CommandButton4 is on Sheet4 (PL)

Oops, I missed that you had already stated that.


Oops for you too, you missed or forgot my earlier point

When posting vba code in the forum, please use the available code tags. My signature block below has more details.

I did not fix it for you this time (post #7)

See if this would suffice. It gives the user two chances (adjustable in the 'Const' line) to choose a print range on 'PL' otherwise the code does nothing.

Private Sub CommandButton4_Click()
  Dim PrntRng As Range
  Dim Tries As Long
  
  Const AllowedTries As Long = 2
  
  Do
    On Error Resume Next
    Set PrntRng = Application.InputBox("Select Range To Print", "Select Range", Type:=8)
    On Error GoTo 0
    Tries = Tries + 1
  Loop Until Not PrntRng Is Nothing Or Tries = AllowedTries
  If Not PrntRng Is Nothing Then
    ActiveSheet.PageSetup.PrintArea = PrntRng.Address
    Sheets(Array("PS", "PL")).PrintPreview
    If MsgBox("Confirm to print?", vbYesNo) = vbYes Then Sheets(Array("PS", "PL")).PrintOut
    Sheets("PL").Select
  End If
End Sub