VBA warning to print 1 page

[excellence]

I have a 2 page spreadsheet that I would like to warn me, prior to printing to print 1 page as I need to take the printed page and flip it to print page 2

I tried the vba below, but I can't get past the warning to print page 1 as the message keeps coming back.
Perhaps there is a different way but wonder how to edit this to answer no and proceed to the print dialog box so I can choose which page to print, not both.

My guess is to have the cancel=true modified to take me to the print dialog box?

Many thanks

Private Sub Workbook_BeforePrint(Cancel As Boolean)
    Result = MsgBox("PRINT 1 PAGE  ANSWER >>> NO", vbYesNo)
    If Result = vbNo Then
        Cancel = True
    End If
End Sub

 

[Burrgogi]

I would write a script that prints the 1st page only with something like this:

MsgBox ("This script will print page 1 only.")
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=1, Collate:=False

Then wait for say 20-30 seconds and have a message box appear that asks "Are you ready to print page 2?" (vbYesNo).

If vbYes
     ActiveWindow.SelectedSheets.PrintOut From:=2, To:=2, Copies:=1, Collate:=False
End If
If vbNo
     Exit Sub
End If

 

[MikeVol]

Hi @excellence.
Maybe like:

Option Explicit

Private Sub Workbook_BeforePrint(Cancel As Boolean)
    Static showMessage As Boolean
    Dim Result      As VbMsgBoxResult

    If Not showMessage Then
        Result = MsgBox("Print only one page at a time. Click 'No' to open the print dialog box and manually select the page to print.", vbYesNo)
        
        If Result = vbNo Then
            Cancel = True
            showMessage = True
            Application.Dialogs(xlDialogPrint).Show
        End If
    
    Else
        showMessage = False
    End If

End Sub

I'm not sure it will work the way you want, but it's worth a try. Let me know if this code works correctly. Good luck.

 

[excellence]

I would write a script that prints the 1st page only with something like this:

MsgBox ("This script will print page 1 only.")
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=1, Collate:=False

Then wait for say 20-30 seconds and have a message box appear that asks "Are you ready to print page 2?" (vbYesNo).

If vbYes
     ActiveWindow.SelectedSheets.PrintOut From:=2, To:=2, Copies:=1, Collate:=False
End If
If vbNo
     Exit Sub
End If

Not sure where to put the Msg Box VBA as I have another VBA that is an Auto open VBA and had put my VBA under it in "this workbook"
It seems mine works if I answer yes to the msg box, but yours seems better

 

[excellence]

I would write a script that prints the 1st page only with something like this:

MsgBox ("This script will print page 1 only.")
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=1, Collate:=False

Then wait for say 20-30 seconds and have a message box appear that asks "Are you ready to print page 2?" (vbYesNo).

If vbYes
     ActiveWindow.SelectedSheets.PrintOut From:=2, To:=2, Copies:=1, Collate:=False
End If
If vbNo
     Exit Sub
End If

Not sure where to put the Msg Box VBA as I have another VBA that is an Auto open VBA and had put my VBA under it in "this workbook"
It seems mine works if I answer yes to the msg box, but yours seems better

also don't want to print just get to print dialog.

 

[excellence]

Hi @excellence.
Maybe like:

Option Explicit

Private Sub Workbook_BeforePrint(Cancel As Boolean)
    Static showMessage As Boolean
    Dim Result      As VbMsgBoxResult

    If Not showMessage Then
        Result = MsgBox("Print only one page at a time. Click 'No' to open the print dialog box and manually select the page to print.", vbYesNo)
       
        If Result = vbNo Then
            Cancel = True
            showMessage = True
            Application.Dialogs(xlDialogPrint).Show
        End If
   
    Else
        showMessage = False
    End If

End Sub

I'm not sure it will work the way you want, but it's worth a try. Let me know if this code works correctly. Good luck.

An amateur here, and don't really know how/where to paste this into vba though have checked require variable declaration in preferences

 

[jolivanes]

When the message pops up, walk over to the printer and flip your sheet.
Click on OK on the message box to finish printing.

Sub Maybe()
Dim i As Long, sh1 As Worksheet
Set sh1 = Worksheets("Sheet1")    '<-----Change as required
sh1.PageSetup.PrintArea = "A1:I90"    '<-----Change as required
    For i = 1 To 2
        sh1.PrintOut i, i
        If i = 1 Then MsgBox "Waiting for operator to turn paper."
    Next i
End Sub

Put the code in a regular module.

 

[excellence]

When the message pops up, walk over to the printer and flip your sheet.
Click on OK on the message box to finish printing.

Sub Maybe()
Dim i As Long, sh1 As Worksheet
Set sh1 = Worksheets("Sheet1")    '<-----Change as required
sh1.PageSetup.PrintArea = "A1:I90"    '<-----Change as required
    For i = 1 To 2
        sh1.PrintOut i, i
        If i = 1 Then MsgBox "Waiting for operator to turn paper."
    Next i
End Sub

Put the code in a regular module.

I don't want to print necessarily, but just be warned not to print lest I print 2 pages. So the message is a warning to print just one page of the 2 page document so I would flip it IF I decided to print

 

[jolivanes]

I don't get the logic. Why use "Print" if you don't want to print? Why have a message box for this?

 

[excellence]

I don't get the logic. Why use "Print" if you don't want to print? Why have a message box for this?

Want to be reminded to select one page when printing if I print. May want to print preview the spreadsheet and not print it when I see the preview, but was reminded about just printing one page if I decide to print.