Generating Invoices from Excel Sheet

REUBEN

Board Regular
Joined
Mar 7, 2014
Messages
113
Hi All,

I've used a VBA code to create invoices for my casework from my worksheet. This code was provided online and I have tweaked it to work for my specific requirements. But I do know have the VBA programming skills to figure out how to make it do a few specific tasks.

The code picks up certain data from my work sheet and uses a template to print a softcopy of an invoice. I want to tweak it so I could embed the button in each row so it would generate invoices for the data on that specific row only. Right now the code is made so it would generate invoices endlessly from a specified starting row to an ending row (which includes blank rows too - which is totally unnecessary). There is a step in the code that checks if that row's invoice has been prepare or not and then is to generate its respective invoice. But I dont want that, I'd rather have the button be embedded in each respective row and make it generate the invoice for that specific row only. Could anyone help me correct this please?

Sub Button5_Click()

Dim ponumber As String
Dim customername As String
Dim caseworkername As String
Dim casetype As String
Dim dateofsubmission As String
Dim r As Long
Dim path As String
Dim myfilename As String
lastrow = Sheets("IMMIG. TRACKER").Range(“A” & Rows.Count).End(xlUp).Row
r = 78
For r = 78 To lastrow
If Cells(r, 18).Value = "Invoice Prepared" Then GoTo nextrow
ponumber = Sheets("IMMIG. TRACKER").Cells(r, 19).Value
customername = Sheets("IMMIG. TRACKER").Cells(r, 4).Value
caseworkername = Sheets("IMMIG. TRACKER").Cells(r, 20).Value
casetype = Sheets("IMMIG. TRACKER").Cells(r, 21).Value
dateofsubmission = Sheets("IMMIG. TRACKER").Cells(r, 13).Value
' UnitPrice = Sheets("IMMIG. TRACKER").Cells(r, 20).Value
' SalesTaxRate = Sheets("IMMIG. TRACKER").Cells(r, 16).Value

Cells(r, 18).Value = "Invoice Prepared"
Application.DisplayAlerts = False
Workbooks.Open Filename:= _
"C:\Users\reuben@relocate.dk\Google Drev\Desktop Shortcuts\PO-IBM-Fragomen Invoice-V2.0.xlsx", UpdateLinks:=xlUpdateLinksAlways
ActiveWorkbook.Sheets("Invoice").Activate
ActiveWorkbook.Sheets("Invoice").Range("A13").Value = ponumber
ActiveWorkbook.Sheets("Invoice").Range("B15").Value = customername
ActiveWorkbook.Sheets("Invoice").Range("E13").Value = caseworkername
ActiveWorkbook.Sheets("Invoice").Range("B19").Value = casetype
ActiveWorkbook.Sheets("Invoice").Range("B26").Value = dateofsubmission
' ActiveWorkbook.Sheets("Ark1").Range("H21").Value = UnitPrice
' ActiveWorkbook.Sheets("Ark1").Range("D18").Value = SalesTaxRate

path = "C:\Users\reuben@relocate.dk\Desktop\INVOICES\"

ActiveWorkbook.SaveAs Filename:=path & ponumber & "-" & "IBM" & "-" & customername & ".xlsx"
myfilename = ActiveWorkbook.FullName
SetAttr myfilename, vbReadOnly
Application.DisplayAlerts = True
'ActiveWorkbook.PrintOut copies:=1
ActiveWorkbook.Close SaveChanges:=False

nextrow:

Next r

End Sub
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Hi,

embedding a button in each row would be a PITA.
Why not assign the macro to a single button and use an inputBox for you to enter the row number.

Something along the lines of:

Code:
Sub Button5_Click()

Dim ponumber As String
Dim customername As String
Dim caseworkername As String
Dim casetype As String
Dim dateofsubmission As String
Dim r As Long
Dim path As String
Dim myfilename As String


r = InputBox("Enter row Number")
If Cells(r, 1).Value = "Invoice Prepared" Then
MsgBox ("Already Prepared")
Exit Sub
End If

ponumber = Sheets("IMMIG. TRACKER").Cells(r, 19).Value
customername = Sheets("IMMIG. TRACKER").Cells(r, 4).Value
caseworkername = Sheets("IMMIG. TRACKER").Cells(r, 20).Value
casetype = Sheets("IMMIG. TRACKER").Cells(r, 21).Value
dateofsubmission = Sheets("IMMIG. TRACKER").Cells(r, 13).Value
' UnitPrice = Sheets("IMMIG. TRACKER").Cells(r, 20).Value
' SalesTaxRate = Sheets("IMMIG. TRACKER").Cells(r, 16).Value


Cells(r, 18).Value = "Invoice Prepared"

Application.DisplayAlerts = False

Workbooks.Open Filename:= _

"C:\Users\reuben@relocate.dk\Google Drev\Desktop Shortcuts\PO-IBM-Fragomen Invoice-V2.0.xlsx", UpdateLinks:=xlUpdateLinksAlways

ActiveWorkbook.Sheets("Invoice").Activate
ActiveWorkbook.Sheets("Invoice").Range("A13").Value = ponumber
ActiveWorkbook.Sheets("Invoice").Range("B15").Value = customername
ActiveWorkbook.Sheets("Invoice").Range("E13").Value = caseworkername
ActiveWorkbook.Sheets("Invoice").Range("B19").Value = casetype
ActiveWorkbook.Sheets("Invoice").Range("B26").Value = dateofsubmission

' ActiveWorkbook.Sheets("Ark1").Range("H21").Value = UnitPrice
' ActiveWorkbook.Sheets("Ark1").Range("D18").Value = SalesTaxRate

path = "C:\Users\reuben@relocate.dk\Desktop\INVOICES\"

ActiveWorkbook.SaveAs Filename:=path & ponumber & "-" & "IBM" & "-" & customername & ".xlsx"

myfilename = ActiveWorkbook.FullName
SetAttr myfilename, vbReadOnly
Application.DisplayAlerts = True

'ActiveWorkbook.PrintOut copies:=1
ActiveWorkbook.Close SaveChanges:=False

End Sub
 
Upvote 0
Hi Daverunt,

thanks so much for that suggestion.

I wasnt thinking on those lines.

I went ahead and made just a few minor changes to the code. But now I'm stuck with another issue. The below works fine when there's only one client in the invoice. If i need to add more (dependents/family), this gets in to a mess. I am unable to come up with the calculation for the same. Because the varialbles are many.

For example:

Only Spouse, Spouse and Child, Spouse and Children, Only Child, Only Children. The calculation with added dependents is making me go in circles! :(

Could there be a way to calculate this in the sheet and so the below vba would simply pick it up from the sheet and print it in the invoice. I would also like to have an option in the invoice if there are no dependents then that whole row to be blank.
Sub Button5_Click()

Dim ponumber As String
Dim customername As String
Dim caseworkername As String
Dim casetype As String
Dim dateofsubmission As String
Dim dependents As String
'Dim dependentfee As String
Dim r As Long
Dim path As String
Dim myfilename As String


r = InputBox("Please Enter Row Number")
If Cells(r, 18).Value = "Invoice Prepared" Then
MsgBox ("Already Prepared")
Exit Sub
End If

ponumber = Sheets("IMMIG. TRACKER").Cells(r, 19).Value
customername = Sheets("IMMIG. TRACKER").Cells(r, 4).Value
caseworkername = Sheets("IMMIG. TRACKER").Cells(r, 20).Value
casetype = Sheets("IMMIG. TRACKER").Cells(r, 21).Value
dateofsubmission = Sheets("IMMIG. TRACKER").Cells(r, 13).Value
dependents = Sheets("IMMIG. TRACKER").Cells(r, 7).Value

Cells(r, 18).Value = "Invoice Prepared"

Application.DisplayAlerts = False

Workbooks.Open Filename:= _
"C:\Users\reuben@relocate.dk\Google Drev\Desktop Shortcuts\PO-IBM-Fragomen Invoice-V2.0.xlsx", UpdateLinks:=xlUpdateLinksAlways

ActiveWorkbook.Sheets("Invoice").Activate
ActiveWorkbook.Sheets("Invoice").Range("A13").Value = ponumber
ActiveWorkbook.Sheets("Invoice").Range("B15").Value = customername
ActiveWorkbook.Sheets("Invoice").Range("E13").Value = caseworkername
ActiveWorkbook.Sheets("Invoice").Range("B19").Value = casetype
ActiveWorkbook.Sheets("Invoice").Range("B28").Value = dateofsubmission
ActiveWorkbook.Sheets("Invoice").Range("A20").Value = " Accompanying " & dependents
ActiveWorkbook.Sheets("Invoice").Range("A25").Value = " Accompanying " & dependents

path = "C:\Users\reuben@relocate.dk\Desktop\INVOICES\"

ActiveWorkbook.SaveAs Filename:=path & ponumber & "-" & "IBM" & "-" & customername & ".xlsx"

myfilename = ActiveWorkbook.FullName
'SetAttr myfilename, vbReadOnly
Application.DisplayAlerts = True

'ActiveWorkbook.PrintOut copies:=1
ActiveWorkbook.Close SaveChanges:=False

End Sub
 
Upvote 0
Alright. So I played around a bit more and figured a way to calculate all the variables.

Everything is working fine.

However, when the input box comes up and I choose to hit cancel it gives me a Run-time error '13', Type Mismatch.

Any suggestions what's going on?
 
Upvote 0
Yes you need some checking on the input box. Should exit on Cancel or empty box (no msg displayed) or pick up a non numeric character and force a retry.

Try the following:

Code:
 ValidEntry = False
    
    Do
        r = InputBox("Enter the row number")
        
    If IsNumeric(r) Then
       ValidEntry = True
    End If
    If r = "" Then

     Exit Sub
    End If
    
    If Not IsNumeric(r) Then
        MsgBox "Enter a number only"
    
    Else

    End If
    
    Loop Until ValidEntry

    If Cells(r, 18).Value = "Invoice Prepared" Then
    MsgBox ("Already Prepared")
    Exit Sub
    End If
 
Last edited:
Upvote 0
Forgot....you'll need to remove Dim r As Long or change it to Variant as it will be whatever the InputBox content is.
 
Upvote 0
Super!


Incorporated it all. One last question, In cases where I only have one client and no accompanying dependents. The dependents cell value must be printed as blank but as of now the code is still printing "Accompanying ". what should be the code to check this?


Here's my updated code:


Sub Button5_Click()


Dim ponumber As String
Dim customername As String
Dim caseworkername As String
Dim casetype As String
Dim dateofsubmission As String
Dim dependents As String
Dim principalfee As String
Dim dependentfee As String
Dim govtfee As String
Dim depgovtfee As String
Dim r As Variant
Dim path As String
Dim myfilename As String


ValidEntry = False


Do
r = InputBox("Please Enter The Row Number Of The Assignee")


If IsNumeric(r) Then
ValidEntry = True
End If
If r = "" Then


Exit Sub
End If


If Not IsNumeric(r) Then
MsgBox "Enter a number only"


Else


End If


Loop Until ValidEntry


If Cells(r, 18).Value = "Invoice Prepared" Then
MsgBox ("Invoice Already Prepared")
Exit Sub
End If


ponumber = Sheets("IMMIG. TRACKER").Cells(r, 19).Value
customername = Sheets("IMMIG. TRACKER").Cells(r, 4).Value
caseworkername = Sheets("IMMIG. TRACKER").Cells(r, 20).Value
casetype = Sheets("IMMIG. TRACKER").Cells(r, 21).Value
dateofsubmission = Sheets("IMMIG. TRACKER").Cells(r, 13).Value
dependents = Sheets("IMMIG. TRACKER").Cells(r, 7).Value
principalfee = Sheets("IMMIG. TRACKER").Cells(r, 22).Value
dependentfee = Sheets("IMMIG. TRACKER").Cells(r, 23).Value
govtfee = Sheets("IMMIG. TRACKER").Cells(r, 24).Value
depgovtfee = Sheets("IMMIG. TRACKER").Cells(r, 25).Value


Cells(r, 18).Value = "Invoice Prepared"


Application.DisplayAlerts = False


Workbooks.Open Filename:= _
"C:\Users\reuben@relocate.dk\Google Drev\Desktop Shortcuts\PO-IBM-Fragomen Invoice-V2.0.xlsx", UpdateLinks:=xlUpdateLinksAlways


ActiveWorkbook.Sheets("Invoice").Activate
ActiveWorkbook.Sheets("Invoice").Range("A13").Value = ponumber
ActiveWorkbook.Sheets("Invoice").Range("B15").Value = customername
ActiveWorkbook.Sheets("Invoice").Range("E13").Value = caseworkername
ActiveWorkbook.Sheets("Invoice").Range("B19").Value = casetype
ActiveWorkbook.Sheets("Invoice").Range("B28").Value = dateofsubmission
ActiveWorkbook.Sheets("Invoice").Range("A20").Value = " Accompanying " & dependents
ActiveWorkbook.Sheets("Invoice").Range("A25").Value = " Accompanying " & dependents
ActiveWorkbook.Sheets("Invoice").Range("G19").Value = principalfee
ActiveWorkbook.Sheets("Invoice").Range("G20").Value = dependentfee
ActiveWorkbook.Sheets("Invoice").Range("G24").Value = govtfee
ActiveWorkbook.Sheets("Invoice").Range("G25").Value = depgovtfee


path = "C:\Users\reuben@relocate.dk\Desktop\INVOICES\"


ActiveWorkbook.SaveAs Filename:=path & ponumber & "-" & "IBM" & "-" & customername & ".xlsx"


myfilename = ActiveWorkbook.FullName
'SetAttr myfilename, vbReadOnly
Application.DisplayAlerts = True


'ActiveWorkbook.PrintOut copies:=1
ActiveWorkbook.Close SaveChanges:=False


End Sub
 
Upvote 0
Hi,

You could check the length of the Dependents string, I used zero but the minimum is your choice.


Code:
If Len(dependents) > 0 Then
 ActiveWorkbook.Sheets("Invoice").Range("A25").Value = " Accompanying " & dependents
 End If

...Oh and please use Code tags around your code, it just makes it easier on the eye.
 
Upvote 0
Hi,

You could check the length of the Dependents string, I used zero but the minimum is your choice.


Code:
If Len(dependents) > 0 Then
 ActiveWorkbook.Sheets("Invoice").Range("A25").Value = " Accompanying " & dependents
 End If

...Oh and please use Code tags around your code, it just makes it easier on the eye.


Hi Daverunt

Sorry for that...

What if the that cell had a text like "None" or "No". I'm guessing this length check will not work here.

Also can I just use this code right after the previous lines in the code or is there a specific place for it?

Thanks again.
 
Upvote 0
Hi,

Potentially any string is a name so it really depends on how many non name strings you need to eliminate as to whether the code gets untidy.

If there are only a handful such as None, No or blank it's not so bad:

There are case issues in comparing text see here:
Stop Excel VBA Macro Code Being Case Sensitive & Compare Text. Excel VBA Case Sensitive

I have used OptionCompare at the top of the macro so NONE, None and none are regarded as the same.

You can put code between as shown.

Code:
Option Compare Text

Sub Button5_Click()

......


      ActiveWorkbook.Sheets("Invoice").Range("B28").Value = dateofsubmission

 If dependents = "None" Or dependents = "No" Or dependents = "" Then
       ActiveWorkbook.Sheets("Invoice").Range("A20").Value = ""
       ActiveWorkbook.Sheets("Invoice").Range("A25").Value = ""
 Else
      ActiveWorkbook.Sheets("Invoice").Range("A20").Value = " Accompanying " & dependents
      ActiveWorkbook.Sheets("Invoice").Range("A25").Value = " Accompanying " & dependents
 End If

    ActiveWorkbook.Sheets("Invoice").Range("G19").Value = principalfee
 
............
 
Upvote 0

Forum statistics

Threads
1,222,014
Messages
6,163,392
Members
451,834
Latest member
tomtownson

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
Back
Top