Re: Need Help writing Print Macro


Thank you OssieMac! :_)


"OssieMac" <OssieMac@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:47CED767-31AB-4F19-B4AF-BE5E317090F9@xxxxxxxxxxxxxxxx
Hi again Gerald,

When I said the code runs from a button or menu I meant from a standard
toolbar button or menu item file -> print. The advantage of my code over
the
other code which was posted while I was doing this one is my code
basically
disables printing unless the conditions are met.

--
Regards,

OssieMac


"OssieMac" wrote:

Hi Gerard,

The following code runs whenever Print is selected either from a button
or
menu and works on the active *** but skips code for all but a specific
***. See comment in code where you need to edit the macro for the
required
***.

Not sure if you need all the following instructions but just in case.

Press Alt/F11 to open the VBA editor.
Double Click Thisworkbook in the project explorer on the left of the
screen.
Copy the macro into the editor. (It must be in ThisWorkbook module)

Private Sub Workbook_BeforePrint(Cancel As Boolean)

'Edit Sheet1 in the following line to match the sheet on
'which you want the macro to control otherwise will affect
'all worksheets in the workbook.

If ActiveSheet.Name = "Sheet1" Then

Application.EnableEvents = False

Cancel = True 'Cancels the initial print call.

With Active***
If .Range("G59") = "Select Customer from Dropdown List" Or _
.Range("F64") = "Select User from Dropdown List" Or _
.Range("E65") = "Not Balanced !" Then

MsgBox "Batch will not print until all " & vbCrLf & _
"discrepancies have been settled"

GoTo ReEnableEvents
Else
'Following line not required if page setup has
'been previously setup.
Active***.PageSetup.PrintArea = "$A$18:$I$69"

ActiveWindow.SelectedSheets.PrintOut copies:=1, collate:=True
End If
End With
End If

ReEnableEvents:
Application.EnableEvents = True

End Sub

Now if during testing you have a problem and the code fails to run then
you
need the following to re-enable events. copy it to anywhere in the VBA
editor
and simply click anywhere within it and press F5. (You won't see anything
occur but it does re-enable the events if the other code fails for any
reason.)


Sub Re_Enable_Events()

'Click anywhere in this sub and press F5
'if events get turned off inadvertantly

Application.EnableEvents = True

End Sub


--
Regards,

OssieMac




.