Generating Invoices from Excel Sheet

[REUBEN]

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​

 

[daverunt]

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:

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

 

[REUBEN]

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​

 

[REUBEN]

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?

 

[daverunt]

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:

 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

 

[daverunt]

Forgot....you'll need to remove Dim r As Long or change it to Variant as it will be whatever the InputBox content is.

 

[REUBEN]

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

 

[daverunt]

Hi,

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

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.

 

[REUBEN]

Hi,

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

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.

 

[daverunt]

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.

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
 
............