Thread: Events before printing

Thanks. That helped.

One more thing.

I ask when printing a question and if the anser is "no" I want to call my print macro insted that will alter the sheet and then print it. I use for that:

Call PrintWithoutWhiteText

But since this call is made within the BeforePrint Event the sheet is not altered in the macro. Well the macros print statement seems not to be executed at all. I tried simething like that, if the anser is "no":

Cancel = True
Call PrintWithoutWhiteText

How can I cancel the print in BeforePrint Event and then run the macro just after ... ?

You place the question in the before print event

Code:

Option Explicit

Private Sub Workbook_BeforePrint(Cancel As Boolean)
    Select Case MsgBox("Do you want to print white text?", vbYesNo Or vbQuestion Or vbDefaultButton1, "Print options")

        Case vbYes
            Cancel = True
            PrintWithoutWhiteText
        Case vbNo
            .do Nothing
    End Select
End Sub

Place the print text in a standard module.

It works fine for me, have you put the code in the right place? It should be added to the workbook code module:

Copy the code that you want to use
Select the workbook in which you want to store the code
Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
In the Project Explorer, find your workbook, and open the list of Microsoft Excel Objects
Right-click on the ThisWorkbook object, and choose View Code
Where the cursor is flashing, choose Edit | Paste

Your post does not comply with Rule 5 of our Forum RULES. We have Seven question forums: Miscellaneous, General, Programming, Worksheet Functions, Charting, Excel 2007 Help and New Users. Please choose the appropriate forum, and post your question in ONLY one forum.

Moved to programming

VBA Noob

Well, sorry for chosing the wrong forum and thank you for moving it.

----

I looked throught tha macro and everything is working fine exept the print options is ignored when macro is called in the BeforePrint Event. Although it work fine when runing the macro seperatly. In the macro I use:

Code:

 
Application.Dialogs(xlDialogPrintPreview).Show

I can't guess without seeing the code.

In ThisWorkbook I have:

Option Explicit

Private Sub Workbook_BeforePrint(Cancel As Boolean)

Dim Answer As String
Dim MyNote As String

MyNote = "Are you printing a quote for .....?"
Answer = MsgBox(MyNote, vbQuestion + vbYesNo, ".....?")

If Answer = vbNo Then
'Code for No button Press
MsgBox "Printing .....!"
Else
'Code for Yes button Press
MsgBox "Printing .....!"
Cancel = True
PrintWithoutWhiteText
End If

End Sub

The macro PrintWithoutWhiteText in Module2

Sub PrintWithoutWhiteText()
Application.EnableEvents = False

Dim LastColumn As Long
Dim LastRow As Long
Dim LastCell

LastRow = ActiveSheet.UsedRange.Rows.Count
LastColumn = ActiveSheet.UsedRange.Columns.Count
LastCell = Cells(LastRow, LastColumn).Address(False, False)

For Each c In Worksheets("Quote").Range("B1:" + LastCell).Cells
If c.Font.ColorIndex = 2 Then
c.NumberFormat = ";;;"
End If
Next

On Error GoTo ErrCatch
Application.Dialogs(xlDialogPrintPreview).Show

ErrCatch:
If Err.Number = 123 Then
For Each d In Worksheets("Quote").Range("B1:" + LastCell).Cells
If d.NumberFormat = ";;;" Then
d.NumberFormat = "#,##0"
End If
Next
Let Cells(1, 30).Value = 0
End If

For Each d In Worksheets("Quote").Range("B1:" + LastCell).Cells
If d.NumberFormat = ";;;" Then
d.NumberFormat = "#,##0"
End If
Next

Application.EnableEvents = True
End Sub