Advice for the order of code to be run

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,904
Office Version
  1. 2007
Platform
  1. 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.

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
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,904
Office Version
  1. 2007
Platform
  1. Windows
I was looking in the wrong place so now placing this here works all ok.


Rich (BB code):
Private Sub Print_Invoice_Click()
  strFileName = "C:\Users\Ian\Desktop\REMOTES ETC\DR\DR COPY INVOICES\" & Range("L4").Value & ".pdf"
  If Dir(strFileName) <> vbNullString Then
    MsgBox "INVOICE PDF " & Range("L4").Value & " ALLREADY EXISTS", vbCritical + vbOKOnly, "PDF FILE EXISTS MESSAGE"
    Else
InvoicePrintForm.Show
Exit Sub
End If
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,127,345
Messages
5,624,131
Members
416,012
Latest member
rockermom59

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top