Auto invoicing with expired dates

[philfloyduk]

Hi.

I have set up a spreadsheet and user form to manage our domestic appliance rentals. The rental is managed with a rental ID number which populates all the information on a user form (thanks to the help of one of the members here).

I would like to set it up to automatically print an invoice when each rental expires. All I need to know is how to code the sheet to recognise it's expired and how to then get the sheet to run code in to the form and generate the invoice. I don't need the code to populate the form or to create an invoice, I already have that, I just need to know how to activate some form of range("a5")_change code to run when the cell is flagged as expired.

Thanks in advance for any help offered.

Phil

 

[RichardMGreen]

How are the dates in A5 populated?
Are they formulas or keyed dates?
How soon after the expired date do you want to print the invoice?

 

[philfloyduk]

Hi Richard.

The layout is as follows.

A2 = Rental ID
B2 = Address
C2 = Expiry date
D2 = Payment status
E2 = Hirer name

Obviously there are multiple entries so the cell number will vary.

When the rental ID is marked as delivered/ 'Active' the userform feeds these five cells with the information, so the date is put in to the cell using

sheets("annual rentals").range("c2").value = format, (expiryTextBox.value, "dd/mm/yyyy").

I'd like an invoice to be printed 14days prior to expiration, and then re-print if it's not been marked as paid within 28 days from the original invoice with a "reminder" label on.

Thank you for your response

Phil

 

[RichardMGreen]

You can use a formula to check what needs to be printed when by comparing the expiry date with todays date.
Assuming the spreadsheet will be used 7 days a week, in cell F2 enter
=if(c2=today()-14,"Print Today","")

in cell G2 you'll need to enter a marker to say if the invoice has been printed/paid

in cell H2 enter
=if(and"c2=today()-28,g2=""),"Reminder","")

You can then use the contents of cells F2 and G2 to customise the printing of the invoices.