Problem - Workbook_BeforePrint

  • Thread starter Thread starter JON-JON
  • Start date Start date
J

JON-JON

HELLO VB EXPERTS,

It seems that Workbook_BeforePrint event is triggered by both PrintOut and
PrintPreview. Is it possible that I could run a different code for each of
the command the user will choose. somewhat like:

Private Sub Workbook_BeforePrint

If user choose PrintOut Then
My code goes here
Else
My other code goes here
End If

End Sub

Anyone who could give light to my problem will be highly appreciated

Thanks in advance,

Jon-jon
 
You could use a more elaborate form but this should get you started. Place
the code in the "ThisWorkbook" module

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Cancel = True
Dim Msg As String, Style As String, Title As String
Dim Response As String, MyAnswer As String
Msg = "Select 'Yes' to Print, 'No' to Print Preview, 'Cancel' to exit"
Style = vbYesNoCancel + vbQuestion
Title = "Print or Print Preview"
Response = MsgBox(Msg, Style, Title)
Application.EnableEvents = False
If Response = vbYes Then
MyAnswer = "No printing allowed"
'or other code for a macro here
ElseIf Response = vbNo Then
MyAnswer = "Print Preview allowed"
'or other code for a macro here
ActiveSheet.PrintPreview
ElseIf Response = vbCancel Then
MyAnswer = "Bailing out"
End If
Application.EnableEvents = True
MsgBox MyAnswer
End Sub


--
XL2002
Regards

William
(e-mail address removed)

| HELLO VB EXPERTS,
|
| It seems that Workbook_BeforePrint event is triggered by both PrintOut and
| PrintPreview. Is it possible that I could run a different code for each
of
| the command the user will choose. somewhat like:
|
| Private Sub Workbook_BeforePrint
|
| If user choose PrintOut Then
| My code goes here
| Else
| My other code goes here
| End If
|
| End Sub
|
| Anyone who could give light to my problem will be highly appreciated
|
| Thanks in advance,
|
| Jon-jon
|
|
|
|
|
 
It does trigger an event, but William turned off events. The problem is,
that when his code issues the printout or printpreview command, the event
will be fired again unless he turns off events.

--
Regards,
Tom Ogilvy



JON-JON said:
William,

Thank you very much!
However, there is one more big problem. After allowing the printpreview,
there is a commandbutton [print...] in the preview window and clicking it
doesn't trigger the BeforePrint event anymore and so in effect the code
becomes useless.
Is there a way a can solve this? One more interesting question, does
Microsoft doesn't come to think of it that said commandbutton should also
trigger an event?

Hope to received more replies from you and other VB experts out there.

Jon-jon

William said:
You could use a more elaborate form but this should get you started. Place
the code in the "ThisWorkbook" module

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Cancel = True
Dim Msg As String, Style As String, Title As String
Dim Response As String, MyAnswer As String
Msg = "Select 'Yes' to Print, 'No' to Print Preview, 'Cancel' to exit"
Style = vbYesNoCancel + vbQuestion
Title = "Print or Print Preview"
Response = MsgBox(Msg, Style, Title)
Application.EnableEvents = False
If Response = vbYes Then
MyAnswer = "No printing allowed"
'or other code for a macro here
ElseIf Response = vbNo Then
MyAnswer = "Print Preview allowed"
'or other code for a macro here
ActiveSheet.PrintPreview
ElseIf Response = vbCancel Then
MyAnswer = "Bailing out"
End If
Application.EnableEvents = True
MsgBox MyAnswer
End Sub


--
XL2002
Regards

William
(e-mail address removed)

| HELLO VB EXPERTS,
|
| It seems that Workbook_BeforePrint event is triggered by both PrintOut and
| PrintPreview. Is it possible that I could run a different code for each
of
| the command the user will choose. somewhat like:
|
| Private Sub Workbook_BeforePrint
|
| If user choose PrintOut Then
| My code goes here
| Else
| My other code goes here
| End If
|
| End Sub
|
| Anyone who could give light to my problem will be highly appreciated
|
| Thanks in advance,
|
| Jon-jon
|
|
|
|
|
Click to expand...
Click to expand...
 
Tom,
I got your point and thanks for clearing it up to me. (Almost blame
Microsoft for my own ignorance, sorry!)
This brings me back to my original problem that is knowing if the user
initially choose a printpreview command or a printout.

Somewhat like

Private Sub Workbook_BeforePrint(Cancel As Boolean)
If user initially choose printpreview then
my code goes here
Else
my other code goes here
End if
End sub

I find it logical but I really don't think it is possible or not.

Thanks,

Jon-jon




Tom Ogilvy said:
It does trigger an event, but William turned off events. The problem is,
that when his code issues the printout or printpreview command, the event
will be fired again unless he turns off events.

--
Regards,
Tom Ogilvy



JON-JON said:
William,

Thank you very much!
However, there is one more big problem. After allowing the printpreview,
there is a commandbutton [print...] in the preview window and clicking it
doesn't trigger the BeforePrint event anymore and so in effect the code
becomes useless.
Is there a way a can solve this? One more interesting question, does
Microsoft doesn't come to think of it that said commandbutton should also
trigger an event?

Hope to received more replies from you and other VB experts out there.

Jon-jon

William said:
You could use a more elaborate form but this should get you started. Place
the code in the "ThisWorkbook" module

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Cancel = True
Dim Msg As String, Style As String, Title As String
Dim Response As String, MyAnswer As String
Msg = "Select 'Yes' to Print, 'No' to Print Preview, 'Cancel' to exit"
Style = vbYesNoCancel + vbQuestion
Title = "Print or Print Preview"
Response = MsgBox(Msg, Style, Title)
Application.EnableEvents = False
If Response = vbYes Then
MyAnswer = "No printing allowed"
'or other code for a macro here
ElseIf Response = vbNo Then
MyAnswer = "Print Preview allowed"
'or other code for a macro here
ActiveSheet.PrintPreview
ElseIf Response = vbCancel Then
MyAnswer = "Bailing out"
End If
Application.EnableEvents = True
MsgBox MyAnswer
End Sub


--
XL2002
Regards

William
(e-mail address removed)

| HELLO VB EXPERTS,
|
| It seems that Workbook_BeforePrint event is triggered by both
Click to expand...
PrintOut
and
| PrintPreview. Is it possible that I could run a different code for each
of
| the command the user will choose. somewhat like:
|
| Private Sub Workbook_BeforePrint
|
| If user choose PrintOut Then
| My code goes here
| Else
| My other code goes here
| End If
|
| End Sub
|
| Anyone who could give light to my problem will be highly appreciated
|
| Thanks in advance,
|
| Jon-jon
|
|
|
|
|
Click to expand...
Click to expand...
Click to expand...
 
Back
Top