Need Help writing Print Macro

[Gerard Sanchez]

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!

 

[ryguy7272]

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

 

[Gerard Sanchez]

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

 

[OssieMac]

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

 

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

 

[Gerard Sanchez]

Thank you 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.

 

[Gerard Sanchez]

Thanks Ryan : ) !

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