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 sheet but skips code for all but a specific
sheet. See comment in code where you need to edit the macro for the required
sheet.

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