Lesson 4 - Producing an invoice template with macros and buttons
You will be producing a standard invoice (with 3 macros to do the common tasks) and then customise the toolbars with macro buttons. A macro will record a series of actions so that the next time you only have to click a button and it will do it for you automatically 1) First produce an invoice template.
You can produce your own or copy and customise this one, which is in the IT shared documents saved as “invoice.xls” Use your own heading to match your other World Club Travel documents and save it as a template Printout a copy showing formula (select Tools, Options tick the formula box). Adjust column width so you can read the formula and make sure your name is on it before printing 2) You now need to record 3 macros to a) Save the invoice b) Print the invoice c) Clear the invoice
a) Go to Tools, Macro, Record New Macro Give it a name eg “SaveInvoice” (note no spaces in the name) and click OK Take care because everything you do from now on will be recorded until you click the stop button Go to File, Save As and save it with a new name eg invoice 1 When it has finished saving click the stop button - you have recorded your first macro b) Go to Tools, Macro, Record New Macro and give it a name eg “PrnInvoice” Click the print icon on the toolbar - wait a few seconds and click stop - button 2nd macro recorded c) Go to Tools, Macro, Record New Macro and give it a name eg “Clear” Select the areas you want to clear (address, invoice number, date, quantity, description and unit cost) on the example it would be A9:E12 A15:D15 and A18:D28 Select each area in turn and press the Delete key. When finished click stop - button 3rd macro recorded 3) Now we must produce 3 buttons and link them to the macros
Go to View Toolbars, Forms Click on the button icon and draw a small rectangle at the top of your invoice This is your first macro button. Another window will list your macros Select “SaveInvoice” (or whatever you called it) OK If you right click on the button you can change the text to “Save Invoice” Do two more buttons for the other macros
EVIDENCE REQUIRED
1) 2) You should already have a printout of your template showing formula You should also have a printout of the blank invoice when you recorded your macro
3) Use your World Club Travel database to fill in the details of a client and at least two holidays make sure your formulae work and you have a correct answer. Do a screen dump and paste it into word. Click your “print” macro button and label the printout saying produced using the macro button 4) Click your save button and then click your clear button - it should clear the invoice - do another screen dump. Add this to the one you already have and print out the two screen dumps on one page. Annotate to say these are the before and after screens when using your “clear” macro. This printout will also show your macro buttons - label then as such. 5) Finally you will need to print out your macros Select Tools, Macro, Visual Basic editor Click the + signs in this window until you find Module 1 Double click it and it will show the Macro code Copy and paste it into word and print a copy Annotate to explain what each line does (See example below)