Reference no: EM132562994

Instructions:

You must only use technics taught / learnt during the module to solve the following questions and you must comment your VBA code precisely.

Use file wineFairInitial.xlsm file. Rename it such as <studentCode><firstName><lastName>.xlsm

Context:

In the middle of September many wine fairs are organized by supermarkets in France. You have been asked by a supermarket manager to support him for:
• Helping the customers to find the wines they would like to buy, either by color or by type;
• Generate their invoices and
• Store some data for each invoice.

The manager informs you that the wine fair propose a selection of thirty wines. This limit is not variable. As the manager is used to work with Excel, he provided the following workbook to you:

Question 1: Updating wine type list

a- In "Interface" sheet column G, add a spin button that allow changes between 0 and 12 of the number of bottles chosen for first wine (A13)
b- Beside vat rate (F2), design a scroll bar to define the rate which must be comprised between 5% and 40% with small changes of 0,1% and big changes of 1%.

c- Create a macro "generate_wine_types" that generates the list of wine type (in the "Wine_database" sheet, under the F2 cell) and sorts it by alphabetic order. Each wine type must only appear once.
d- In the "interface" sheet, create a button to run "generate_wine_types" macro.

e- Name the generated list of wine types "types" so that the name automatically adapts to the number of existing types generated by the macro.

f- In the "interface" sheet, in the C3 cell, create a choice list based on the types of wines called "types".

Question 2: Opening the workbook

When the user opens the workbook, the following message appears Use VBA functions date() and time() to get this exact result (with current date and time).

Question 3: Filling the invoice

Create a macro called "searchWines" which will be run from "Interface" sheet through a button:

• that shows the list of wines corresponding to the criteria (the color and/or type of wine) chosen in cells B3 and C3, under the headers (B12:E12) in the "Interface" sheet.
• if the result of the search is empty, sends a dialog box to the user to inform him/her to change his/her criteria such as "no result for your choice criteria, please change them"
• otherwise (if search produced at least one answer), clears the contents of the A13:A42 range of cells
Now the "Interface" sheet is ready to register a new invoice.

Question 4: Customized function for VAT
All the prices of the wines in the database are provided including the VAT.
• Create a customized function called "vatAmount", that calculates the VAT amount of the invoice.
• This function requires 2 informations: Invoice amount including VAT and VAT rate.
• Calculation of VAT amount: Invoice Amount With VAT - Invoice Amount With VAT / (1 + VAT rate)
• This function must be used in the "Interface" sheet, in the F8 cell.

Question 5: Backup

• Create a macro called "backup" that stores some items of each new invoice in the "Backup" sheet, in the first empty row.
• Create a button in the "Interface" sheet to run the "backup" macro.
• Create a macro that deletes the active row.
• Create a button in the "Backup" sheet that calls this procedure (in order to delete a backup if necessary).

Question 6: Formatting

The formatting of the result of the filtering by color and/or type of wine (the A13:F42 range) is not very nice. As a matter of fact, borders are shown around all the cells for the thirty wines proposed in this fair.
• Create a macro called "formatInvoice" that format the table while the invoice is completed by the customer, and show borders only for the bought wines.
• You are free to choose any way to obtain this result.
Comments of all formatting steps in this macro are compulsory.
• Create a button called "format the invoice" that runs your new macro from the "Interface" sheet.

Attachment:- Advanced Excel Level.rar