Events before printing

[terabyte]

I have early made a macro that will change some of the cells before printing and then changing back again to initional state after the print out.

http://www.excelforum.com/excel-misc...-printing.html

But now I have to do let people also print a copy a regular way, without the macro I made before. But if they go through the regular way iof printing I would like to insert at header in red color, to notify them that this is just a preview and not the way to print. And here we come to the problem ....

I want to use something like this:

Please Login or Register  to view this content.

If I use it in "ThisWorkbook", this will also be activated even when print outs are made through the macro, but I don't want that to happen. When runing the macro for printing I don't want this event above to run .... only when printing through the regular way. How to achieve it?

[solnajeff]

Hi

You can use a 'helper' cell in the first line of the print macro that e.g. is set to 1 and in the last line set to 0. In the WorkbookBeforePrint macro insert a condition to check the value of the 'helper' cell and only execute the code if it is 0.

Regards

Jeff

[terabyte]

Can't do it. I have to make it quite general for other to use it ...

[solnajeff]

Hi

Nobody actually does anything, the idea it simply means that using the macro causes the code not to run that is all, users do not enter anything. If you use cells(1,1) to hold the value then the code required is as below.

Please Login or Register  to view this content.

In the print Macro

Please Login or Register  to view this content.

No user input required.

Regards

Jeff

[terabyte]

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 ... ?

[royUK]

You place the question in the before print event

Please Login or Register  to view this content.

Place the print text in a standard module.

[terabyte]

Thanks. But that is exact what I have done. Seem slike the macro is not executing ...

[royUK]

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

[terabyte]

Yes, it is right place. The print macro I have is in Module2. Seems like the macro is not executed at all. Could it be because the macro has print activities?

[VBA Noob]

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

[terabyte]

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:

Please Login or Register  to view this content.

[royUK]

I can't guess without seeing the code.

[terabyte]

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

[terabyte]

I tried also putting in some change of headers in the BeforePrint event, but it didn't work either, but works as a standalone macro.

ActiveSheet.PageSetup.CenterHeader = "text to be in header"

So, is there something in the BeforePrint event that blocks other calls? Seems like that.