Make notes on excel generated invoice but when printing hide them

[ipbr21054]

Hi,
I have made an Invoice in excel & wondering if the following can be done.
On my sheet at J40:M48 is some empty space & wishing to make use of it with some notes for that current job.

I would print the invoice BUT the notes would be hidden & thus not on the printed sheet.
The code then saves the invoice as a pdf & once saved the notes can be seen on the saved file.

So basically,
Add the notes & give the customer the printed sheet where he is unable to see any notes.
The saved file on my pc will have the notes for me to read at a later date etc.

 

[Micron]

Put the notes on the sheet, but pdf print a range that doesn't include them, and not print the whole sheet?

 

[ipbr21054]

Hi,
The sheet has a defined print area of which is the outside of all the internal text,images, border lines etc. Where I wish to add the notes has printable text outside of it.

 

[Micron]

I guess I'm not envisioning what you have. If all the data you want to print as a pdf is in a range like A1 to O55 then you print only the range as a pdf. If your notes lie outside of that range, they won't go into the pdf. If that doesn't help then I don't have any other suggestions.

 

[ipbr21054]

That’s what i mentioned in my last post.

The notes are inside of the printing range.

 

[Peter_SSs]

Could you use an idea like this?
Assuming that the notes are in, say, the range B3:C6 on 'Sheet1' then when the sheet is printed, the text in that range is first changed to white (assuming white background), the sheet printed and then the text in that range turned back to whatever colour it was (black?). then when you save as a pdf it is saved with the black text.

The code (in ThisWorkbook module) might be along these lines.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
  Cancel = True
  Application.EnableEvents = False
  With Sheets("Sheet1")
    .Range("B3:C6").Font.Color = vbWhite
    .PrintPreview 'Use .PrintPreview for testing but if it does what you want use .PrintOut instead
    .Range("B3:C6").Font.Color = vbBlack
  End With
  Application.EnableEvents = True
End Sub

 

[Micron]

I thought of that but wondered if anyone with the full Adobe version could revert the text to black. I wouldn't know because I've never had the full version.

 

[ipbr21054]

Hi,
The below code is what i use to print & also the code you advised with correct range etc.
Would i replace it with code shown in Red,please advise

Private Sub Print_Invoice_Click()
  Dim sPath As String, strFileName As String
  strFileName = "C:\Users\Ian\Desktop\REMOTES ETC\DR\DR COPY INVOICES\" & Range("L4").Value & ".pdf"
  If Range("L18") = "" Then
    MsgBox ("PLEASE SELECT A PAYMENT TYPE "), vbCritical, "PAYMENT TYPE WAS NOT SELECTED"
    Range("L18").Select
    Exit Sub
  End If
  
  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 & " WAS NOT SAVED AS IT ALLREADY EXISTS", vbCritical + vbOKOnly, "INVOICE NOT SAVED MESSAGE"
  Exit Sub
  
  End If
    With ActiveSheet
    .ExportAsFixedFormat Type:=xlTypePDF, fileName:=strFileName, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False
    
  End With
  
  sPath = "C:\Users\Ian\Desktop\REMOTES ETC\DR\DR COPY INVOICES\"
  strFileName = sPath & Range("L4").Value & ".pdf"
  If Dir(strFileName) <> vbNullString Then
    ActiveWorkbook.FollowHyperlink strFileName
  End If
  
  Call HYPERLINKP5

   With Sheets("INV")
    Worksheets("INV").Activate
    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
    MsgBox "ALL DONE & COMPLETED", vbExclamation + vbOKOnly, "NOW COMPLETED MESSAGE"
End Sub

Private Sub Workbook_BeforePrint(Cancel As Boolean)
  Cancel = True
  Application.EnableEvents = False
  With Sheets("INV")
    .Range("G52:G53").Font.Color = vbWhite
    .PrintPreview 'Use .PrintPreview for testing but if it does what you want use .PrintOut instead
    .Range("G52:G53").Font.Color = vbBlack
  End With
  Application.EnableEvents = True
End Sub

 

[Peter_SSs]

The below code is what i use to print

I can't see anywhere in that first code where anything is printed.

 

[ipbr21054]

Im sorry my mistake.
The code opens the pdf & it is then i print from the pdf screen.
There was an issue trying to print directly from the word doc.

So best i forget this.

Thanks all