Hi,

I'd like a print macro to print range A18:I69 only IF:

IF -- G59 < "Select Customer from Dropdown List"
IF--- F64 < "Select User from Dropdown List"
IF--- E65 < "Not Balanced !"

IF all conditions above are not met:

Message Box:

"Batch will not print until all discrepancies have been settled
and required information filled."

Appreciate any help.


Thanks!

I think this will do what you want:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("E65,F64,G59")) Is Nothing Then
If G59 < "Select Customer from Dropdown List" And F64 < "Select Customer
from Dropdown List" And E65 < "Not Balanced!" Then Range("A18:I69").Select
Selection.PrintOut Copies:=1, Collate:=True
End If
End Sub

Remember, this is event-ode, so you have to right-click on the tab of the
sheet where you want to run the code. Paste it into the window that opens.

Regards,
Ryan---
--
RyGuy


"Gerard Sanchez" wrote:

Hi,

I'd like a print macro to print range A18:I69 only IF:

IF -- G59 < "Select Customer from Dropdown List"
IF--- F64 < "Select User from Dropdown List"
IF--- E65 < "Not Balanced !"

IF all conditions above are not met:

Message Box:

"Batch will not print until all discrepancies have been settled
and required information filled."

Appreciate any help.


Thanks!

Hi Ryguy

Is there a way we can modify this code into a module that I can assign to a
button instead of being an even code?



Thanks
"ryguy7272" wrote in message
...
I think this will do what you want:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("E65,F64,G59")) Is Nothing Then
If G59 < "Select Customer from Dropdown List" And F64 < "Select Customer
from Dropdown List" And E65 < "Not Balanced!" Then
Range("A18:I69").Select
Selection.PrintOut Copies:=1, Collate:=True
End If
End Sub

Remember, this is event-ode, so you have to right-click on the tab of the
sheet where you want to run the code. Paste it into the window that
opens.

Regards,
Ryan---
--
RyGuy


"Gerard Sanchez" wrote:

Hi,

I'd like a print macro to print range A18:I69 only IF:

IF -- G59 < "Select Customer from Dropdown List"
IF--- F64 < "Select User from Dropdown List"
IF--- E65 < "Not Balanced !"

IF all conditions above are not met:

Message Box:

"Batch will not print until all discrepancies have been settled
and required information filled."

Appreciate any help.


Thanks!

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

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

Thank you OssieMac! :_)


"OssieMac" wrote in message
...
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

Thanks Ryan : ) !



"Gerard Sanchez" wrote in message
...

Hi Ryguy

Is there a way we can modify this code into a module that I can assign to
a button instead of being an even code?



Thanks
"ryguy7272" wrote in message
...
I think this will do what you want:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("E65,F64,G59")) Is Nothing Then
If G59 < "Select Customer from Dropdown List" And F64 < "Select
Customer
from Dropdown List" And E65 < "Not Balanced!" Then
Range("A18:I69").Select
Selection.PrintOut Copies:=1, Collate:=True
End If
End Sub

Remember, this is event-ode, so you have to right-click on the tab of the
sheet where you want to run the code. Paste it into the window that
opens.

Regards,
Ryan---
--
RyGuy


"Gerard Sanchez" wrote:

Hi,

I'd like a print macro to print range A18:I69 only IF:

IF -- G59 < "Select Customer from Dropdown List"
IF--- F64 < "Select User from Dropdown List"
IF--- E65 < "Not Balanced !"

IF all conditions above are not met:

Message Box:

"Batch will not print until all discrepancies have been settled
and required information filled."

Appreciate any help.


Thanks!