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

 

[jolivanes]

Re: "select one page"
Does this mean select page1 or page2? Or just the first page?
If you want a choice of
print one page (page1 or page2) only
print first page only
print second page only
print preview print range
print preview page1
print preview page2
the best way to go would be a Userform where you can select on of these possibilities.

or

A choice to Print Preview or Print

Sub Maybe_So()
Dim i As Long, sh1 As Worksheet
Set sh1 = Worksheets("Sheet1")    '<-----Change as required
sh1.PageSetup.PrintArea = "A1:I90"    '<-----Change as required
If MsgBox("Print Preview?", vbYesNo, "Yes means PrintPreview, No means Print.") = vbYes Then
sh1.PrintOut , , , -1
GoTo KeepGoing
Else
KeepGoing:
If MsgBox("Do you want to print?", vbYesNo, "Print or Exit.") = vbYes Then
    For i = 1 To 2
        sh1.PrintOut i, i
        If i = 1 Then MsgBox "Waiting for operator to turn paper."
    Next i
End If
End If
End Sub

 

[excellence]

Re: "select one page"
Does this mean select page1 or page2? Or just the first page?
If you want a choice of
print one page (page1 or page2) only
print first page only
print second page only
print preview print range
print preview page1
print preview page2
the best way to go would be a Userform where you can select on of these possibilities.

or

A choice to Print Preview or Print

Sub Maybe_So()
Dim i As Long, sh1 As Worksheet
Set sh1 = Worksheets("Sheet1")    '<-----Change as required
sh1.PageSetup.PrintArea = "A1:I90"    '<-----Change as required
If MsgBox("Print Preview?", vbYesNo, "Yes means PrintPreview, No means Print.") = vbYes Then
sh1.PrintOut , , , -1
GoTo KeepGoing
Else
KeepGoing:
If MsgBox("Do you want to print?", vbYesNo, "Print or Exit.") = vbYes Then
    For i = 1 To 2
        sh1.PrintOut i, i
        If i = 1 Then MsgBox "Waiting for operator to turn paper."
    Next i
End If
End If
End Sub

I want to print one page, either would be ok, but want reminder to print one page if I choose to print.
Not at all proficient with this (obviously), and not sure where/how to place your vba
Sorry for any confusion.
Tweeking my original vba works for me shown below:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
    Result = MsgBox("Select YES and choose to print ONE page", vbYesNo)
    If Result = vbNo Then
        Cancel = True
    End If
End Sub

 

[MikeVol]

Regarding your post #6: Enter my code instead of your code in the ThisWorkbook module and you will be happy.

 

[excellence]

Regarding your post #6: Enter my code instead of your code in the ThisWorkbook module and you will be happy.

When I take all of #6 and click NO and it opens the dialog box get the error below
I have pasted the entire vba excluding "option explicit" When option explicit is excluded snd click no I get the error in pick below'

Don't see how this works better than my revised post…my reply in #12…must be doing something wrong
When I include "option explicit" I immediately get a Compile error

 

[jolivanes]

Re: "but want reminder to print one page if I choose to print"
It must be me but this does not make any sense at all. Once when you have decided that you're printing, why would you want a reminder? I guess it is up to you but I just don't get it.

I think you should explain in a detailed manner what you want to achieve and all the different ifs and buts.
If this then do this
If that then do that
If so then do such
If not so then do different such.

 

[excellence]

Re: "but want reminder to print one page if I choose to print"
It must be me but this does not make any sense at all. Once when you have decided that you're printing, why would you want a reminder? I guess it is up to you but I just don't get it.

I think you should explain in a detailed manner what you want to achieve and all the different ifs and buts.
If this then do this
If that then do that
If so then do such
If not so then do different such.

I might want to print preview it or save as a .pdf or some other format, I "simply" want a reminder that should I decide to print, that I print just one page NOT the two page sheet AS I need to flip it to print the other page, and in my case it doesn't matter which page I print if I choose to print.

Thanks for your help

 

[jolivanes]

Did you actually try the code from Post 11?

 

[excellence]

Did you actually try the code from Post 11?

Yes I did, perhaps incorrectly placed but I just substituted your vba for mine in "this workbook" and when opening the file
• it opens no dialog box
• can choose file print and nothing happens other than dialog box opens and can choose print or cancel

It's as if the code was not there.
Given my remedial knowledge, I must have placed it in the wrong area.

 

[excellence]

Yes I did, perhaps incorrectly placed but I just substituted your vba for mine in "this workbook" and when opening the file
• it opens no dialog box
• can choose file print and nothing happens other than dialog box opens and can choose print or cancel

It's as if the code was not there.
Given my remedial knowledge, I must have placed it in the wrong area.

oops it opens no message box

 

[jolivanes]

Insert and run VBA macros in Excel - step-by-step guide

This is a short step-by-step tutorial for beginners showing how to add VBA code (Visual Basic for Applications code) to your Excel workbook and run this macro to solve your spreadsheet tasks.

www.ablebits.com