albertc30
Well-known Member
- Joined
- May 7, 2012
- Messages
- 1,091
- Office Version
- 2019
- Platform
- Windows
Hi all.
Bellow is the code I've been tinkering and googling away and it's working to an extent.
Having said this, it's driving me crazy that this is adding number 31 in two cells, not shown on excel but once pdf file is saved.
What on earth is happening here and were is this number 31 coming from?
Also, the code is calling for list of printers with doPDF already selected by user has to accept. This should happen automatically.
I also have set a printing area smaller than where the number 31 are showing when saved meaning the print to pdf is printing more than the printing area.
Any help on the above is trully appreciated as I am running out of ideas especially as to where is the number 31 coming from.
Thanks.
Bellow is the code I've been tinkering and googling away and it's working to an extent.
Having said this, it's driving me crazy that this is adding number 31 in two cells, not shown on excel but once pdf file is saved.
What on earth is happening here and were is this number 31 coming from?
Also, the code is calling for list of printers with doPDF already selected by user has to accept. This should happen automatically.
I also have set a printing area smaller than where the number 31 are showing when saved meaning the print to pdf is printing more than the printing area.
VBA Code:
Private Sub CBGenDocument_Click()
If Range("F3") = Empty Then 'Checks if next document value is empty.
MsgBox "Please select your document type!", vbInformation, "Document..."
Range("E3").Select
Selection.ClearContents
Exit Sub
End If
If MsgBox("You have selected """ & Range("E3") & """ document." & vbNewLine & "Is this the document you wish to generate?", vbQuestion + vbYesNo, "Document type...") = vbNo Then
Range("F3").Clear
Range("E3").Select
Selection.ClearContents
Exit Sub
End If
Select Case StrConv(Range("E3"), vbLowerCase)
Case Is = "invoice"
Dim Data(1 To 4) As Variant
Dim DstRng As Range
Dim RngEnd As Range
Set DstRng = Worksheets("Invoices").Range("A1:D1")
Set RngEnd = DstRng.Parent.Cells(Rows.Count, DstRng.Column).End(xlUp)
Set DstRng = IIf(RngEnd.Row < DstRng.Row, DstRng, RngEnd.Offset(1, 0).Resize(1, 4))
With Worksheets("Create")
Data(1) = .Range("F3") 'Invoice number
Data(2) = .Range("C12") 'Invoice total
Data(3) = .Range("C14") 'Deposit
Data(4) = .Range("C16") 'Owed
End With
DstRng = Data
'save invoice pdf code below
Application.ActivePrinter = "doPDF v7 on DOP7:" 'Selects doPDF to genarate PDF file of invoice but its asking user to select it. want it all auto.
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"D:\Software\Invoices\INV" & Range("F3").Text & ".pdf", _
Quality:=xlQualityStandard, IncludeDocProperties:=False, IgnorePrintAreas _
:=True, OpenAfterPublish:=False
If Application.Dialogs(xlDialogPrinterSetup).Show Then
End If
'save invoice code above
'bellow clears invoice number after saved in pdf
Range("F3").Select
With Selection
.HorizontalAlignment = xlCenter
.Clear
End With
Range("E3").Select
Selection.ClearContents
'Selection.NumberFormat = "00000"
'above clears invoice number after saved in pdf
'Proforma code bellow...
Case Is = "proforma"
Set DstRng = Worksheets("Proformas").Range("A1:D1")
Set RngEnd = DstRng.Parent.Cells(Rows.Count, DstRng.Column).End(xlUp)
Set DstRng = IIf(RngEnd.Row < DstRng.Row, DstRng, RngEnd.Offset(1, 0).Resize(1, 4))
With Worksheets("Create")
Data(1) = .Range("F3") 'Proforma number
Data(2) = .Range("C12") 'Proforma total
Data(3) = .Range("C14") 'Deposit
Data(4) = .Range("C16") 'Owed
End With
DstRng = Data
Range("F3").Select
With Selection
.HorizontalAlignment = xlCenter
.Clear
End With
Range("E3").Select
Selection.ClearContents
Range("F3").Select
With Selection
.HorizontalAlignment = xlCenter
.Clear
End With
Range("E3").Select
Selection.ClearContents
End Select
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
If Not Intersect(Target, Range("E3")) Is Nothing Then
If Target.Value <> "" Then
With Sheets(Target.Value & "s") 'The "s" is the last letter in the sheets Invoices and Proformas.
Target.Offset(, 1).Value = Application.Max(.Range("A:A")) + 1
End With
Range("F3").Select
With Selection
.HorizontalAlignment = xlCenter
.NumberFormat = "00000"
End With
End If
End If
End Sub
Any help on the above is trully appreciated as I am running out of ideas especially as to where is the number 31 coming from.
Thanks.