ipbr21054
Well-known Member
- Joined
- Nov 16, 2010
- Messages
- 5,226
- Office Version
- 2007
- Platform
- Windows
Evening,
Im getting confused / creating errors for myself trying to get this code in the correct order.
When i click the command button to print either 1 or 2 invoices the code SHOULD first look at the value in cell L4 & see if the PDF exists in the saved location.
If the PDF exists then show MsgBox advising file already exists & then STOP ANY PRINTING.
If the PDF does not exist then show my MsgBox asking if i wish to print 1 or 2 copies & continue.
As the code stands it asks how many copies i need to print, so i select & it starts to print only then to be told file not saved as PDF already exists.
Hence im now printing something i need to print again.
Im trying to check if the PDF exists before any printing happens but getting in a right mess.
Code in use is shown below.
Im getting confused / creating errors for myself trying to get this code in the correct order.
When i click the command button to print either 1 or 2 invoices the code SHOULD first look at the value in cell L4 & see if the PDF exists in the saved location.
If the PDF exists then show MsgBox advising file already exists & then STOP ANY PRINTING.
If the PDF does not exist then show my MsgBox asking if i wish to print 1 or 2 copies & continue.
As the code stands it asks how many copies i need to print, so i select & it starts to print only then to be told file not saved as PDF already exists.
Hence im now printing something i need to print again.
Im trying to check if the PDF exists before any printing happens but getting in a right mess.
Code in use is shown below.
Rich (BB code):
Private Sub Print_Invoice(n As Long)
Unload InvoicePrintForm
Dim strFileName As String
If Range("L18") = "" Then
MsgBox ("PLEASE SELECT A PAYMENT TYPE "), vbCritical, "PAYMENT TYPE WAS NOT SELECTED"
Range("L18").Select
Unload InvoicePrintForm
Exit Sub
End If
ActiveSheet.PrintOut Copies:=n
MsgBox "ONCE PRINTED PLEASE CLICK THE OK BUTTON" & vbNewLine & vbNewLine & "TO SAVE INVOICE " & Range("L4").Value & " THEN TO CLEAR CURRENT INFO", vbExclamation + vbOKOnly, "PRINT SAVE & CLEAR MESSAGE"
Unload InvoicePrintForm
strFileName = "C:\Users\Ian\Desktop\REMOTES ETC\DR\DR COPY INVOICES\" & Range("L4").Value & ".pdf"
If Dir(strFileName) <> vbNullString Then
MsgBox "INVOICE " & Range("L4").Value & " PDF FILE WAS NOT SAVED AS IT ALLREADY EXISTS", vbCritical + vbOKOnly, "INVOICE NOT SAVED MESSAGE"
Exit Sub
End If
With ActiveSheet
.PageSetup.PrintArea = "$F$2:$N$61"
.ExportAsFixedFormat Type:=xlTypePDF, Filename:=strFileName, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False
MsgBox "INVOICE " & Range("L4").Value & " PDF FILE WAS SAVED SUCCESSFULLY", vbInformation + vbOKOnly, "INVOICE SAVED SUCCESSFULLY"
Dim i As Long, lRow As Long, ws As Worksheet
Set ws = Application.Worksheets("DATABASE")
lRow = ws.Cells(Rows.Count, 1).End(xlUp).Row
For i = 6 To lRow
If Trim(Range("G13").Value) = Trim(ws.Cells(i, 1).Value) Then
If ws.Cells(i, 16).Value = "" Then
ws.Cells(i, 16).Value = Range("L4").Value ' adding invoice number to INV sheet "P"
ActiveSheet.Hyperlinks.Add ws.Cells(i, 16), Address:="C:\Users\Ian\Desktop\REMOTES ETC\DR\DR COPY INVOICES\" & Range("L4").Value & ".pdf"
MsgBox "INVOICE " & ws.Cells(i, 16).Value & " WAS HYPERLINKED SUCCESSFULLY.", vbInformation, "HYPERLINK SUCCESSFULL MESSAGE"
Else
If MsgBox("COLUMN CELL P ISNT EMPTY " & ws.Cells(i, 16).Value & " IS ENTERED IN IT." & vbNewLine & "WOULD YOU LIKE TO CORRECT IT ?", vbCritical + vbYesNo, "COLUMN P NOT EMPTY MESSAGE") = vbYes Then
ws.Activate
ws.Cells(i, 16).Select
End If
Exit Sub
End If
End If
Next i
Range("G27:L36").ClearContents
Range("G46:G50").ClearContents
Range("L18").ClearContents
Range("L4").Value = Range("L4").Value + 1
Range("G13").ClearContents
Range("G13").Select
ActiveWorkbook.Save
End With
End Sub