Hyperlink invoice to customer at time of printing

[ipbr21054]

Morning,

I have a worksheet called DATABASE where all my customers info is stored.
In the same workbook i have a worksheet called INV where i select the customer from a drop down list & it then gathers all the info to populate the fields.

I then print off an invoice & the copy is saved in a PDF format within a folder on my pc.
I then go to my worksheet called DATABASE & in column P for the customer in question will be the invoice number.
With this cell selected i run the code below which will then hyperlink it to the PDF file.

To save time as it can be long winded if i have mutiple to do i would like the hyperlink piece to happen when i print the invoice.
The code supplied works fine but you manually need to go select the cell etc etc.
So it should work like so,

When i print the invoice look at customers name on INV worksheet.
Look for a match in column A on the DATABASE worksheet.
Hyperlink the value in that cell row at column P with the matched PDF file in the given path.

Sub hyperlink()

        Const FILE_PATH As String = "C:\Users\Ian\Desktop\REMOTES ETC\DR COPY INVOICES\"
    If ActiveCell.Column = Columns("P").Column Then
        If Len(Dir(FILE_PATH & ActiveCell.Value & ".pdf")) Then
            ActiveCell.Hyperlinks.Add Anchor:=ActiveCell, Address:=FILE_PATH & ActiveCell.Value & ".pdf"
        End If
    Else
        MsgBox "Please Select An Invoice Number.", vbExclamation, "Hyperlinking The Invoice Number"
    End If
End Sub

 

[ipbr21054]

I believe i have all the info required to do this but need some help putting these 2 codes together.



This is the code which is in use.
The hyperlink part above can run after i click OK to the msgbox

"Remember the hyperlink code above only works IF I select the cell"
"I need the code added which will look at customers names on worksheet INV at cell G13"
"Now look in column A of worksheet DATABASE for a match,once the match is found hyperlink the value in column P with the PDF that was just saved"
"path shown in above code"

Have a nice day

Private Sub Print_Invoice(n As Long)
  Dim strFileName As String
  If Range("N18") = "" Then
    MsgBox ("PLEASE SELECT A PAYMENT TYPE "), vbCritical, "Payment Type Not Selected"
    Exit Sub
  End If
  ActiveSheet.PrintOut Copies:=n
  Unload InvoicePrintForm
  MsgBox "ONCE PRINTED PLEASE CLICK OK BUTTON" & vbNewLine & vbNewLine & "TO SAVE INVOICE & CLEAR CURRENT INFO", vbExclamation + vbOKOnly, "PRINT SAVE & CLEAR MESSAGE"
  strFileName = "C:\Users\Ian\Desktop\REMOTES ETC\DR COPY INVOICES\" & Range("N4").Value & ".pdf"
  If Dir(strFileName) <> vbNullString Then
    MsgBox "INVOICE " & Range("N4").Value & " WAS NOT SAVED AS IT ALLREADY EXISTS", vbCritical + vbOKOnly
    Exit Sub
  End If
  With ActiveSheet
    .PageSetup.PrintArea = "$G$3:$O$61"
    .ExportAsFixedFormat Type:=xlTypePDF, Filename:=strFileName, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False
    MsgBox "INVOICE " & Range("N4").Value & " WAS SAVED SUCCESSFULLY", vbInformation + vbOKOnly
    Range("G27:N36").ClearContents
    Range("G46:G48").ClearContents
    Range("G47:I51").ClearContents
    Range("N18").ClearContents
    Range("N4").Value = Range("N4").Value + 1
    Range("G13").ClearContents
    Range("G13").Select
    ActiveWorkbook.Save
  End With
End Sub