Excel Macro Development Project
Budget: €30 – €250 EUR
Completing an Excel macro assignment. The instructions are below:
For all tasks, a Cancel button must be provided on all Userforms so the end user can exit the macro whenever she/he wants. Cancel should never give any message.
Task A (10p):
a) Build a Splash Screen with some appropriate text and a picture and an OK button that closes it. 5p
b) Build a button in a Excel worksheet that opens an Userform with eight buttons. The eight buttons must have the texts Task 1, Task 2, Task 3, Task 4, Task 5, Task 6, Task 7 and Task 8. Make the Userform free so it can be open while working in Excel. The Userform must be open while running the task macros. Only the end user should be able to close it with Cancel. 5p All tasks except task 6 must use one sheet only.
Task 1 (10p):
Type the name of ten optional products in cells A1:A10. When the user presses button number 1 (see task A), an Userform opens with a Listbox() with the products. The user can then select a product from the list in the list box. Next, the end user presses a button on the list box page (on the user form) and a message is given telling the end user which product the user has chosen. Finally, the chosen product is also printed in cell A12.
Task 2 (10p):
Under button number 2, you should have a macro asking for five optional integer numbers from the end user, one number at a time with an Inputbox(). If the end user gives the number 666 the macro stops, and the end user gets a message “Wicked number – Ending”. Next, the numbers are inserted into cells C2:C6. Then the numbers are sorted, and finally printed in cells D2:D6 in sorted order with smallest number first.
Task 3 (10p)
Under button number 3, you should have a macro that loads two integers for a range between 1 and 100 from the end user. If the user gives a number greater than one hundred or less than one, the user should receive an error message and the macro should ask for a new number. When the two range numbers have successfully been given (for example 6 and 91), the macro then asks the user to guess a number in the range (we assume that the user does not know the range). If the user guesses wrong, a message is given with a Message Box that the number was too large (in the example more than 91) or too small for the range (in the example less than 6), then the macro asks for a new number. This continues until the user guesses a number within the range. Note that the end user might give the bigger number for the range before the smaller one. For example, 50 and 5, range 5-55.
Task 4 (10p):
Under button number 4, you should have a macro that loads five numbers from the end user and five numbers from cells B2:B6. Ten numbers all together. The macro prints then any numbers bigger than twelve out of the ten numbers in cells B7 onwards. Finally, the sum and average of all the ten numbers is given in a Message Box. Start by inserting five integer numbers in B2:B6 manually (the macro should not do this).
Task 5 (12p):
Under button number 5, you must have a macro that opens an Userform where the end user can give a company name and then select from the same userform the city from a list with the possibilities Helsinki, Turku, Tampere, and Vaasa (it must be impossible to have other cities). Then, the macro asks for a cell reference for a cell where the company name is to be entered, note that the Cancel button here must work. Finally, the macro asks for a cell reference for a cell in which the city is to be entered, also here the Cancel button must work. The company name and the city must be given, and the macro must not go to any printing if they have not been given.
Task 6 (12p):
Create a Listbox() on a Userform for a new worksheet. Fill the list box with seven customer names and addresses. Under button number 6, you should have a macro that opens the Userform with the list box. The list box must be such that the end user can select several customers at once. Also build an OK button that writes the customer names you selected into cell E2 and onwards, and the customers' addresses into cell F2 and onwards. Insert appropriate headings in cells E1 and F1. Build the list box so it contains headers for both columns. If the end user runs the macro several times, the selected customers and addresses must be written in the following empty cells.
Task 7 (12p):
Write a macro that asks for numbers from the end user with an InputBox(), one number after another until the average of the given numbers is 200 or more. The InputBox() must have a working Cancel button, pushing it stops the macro with no further actions. Then the macro gives the average of the given numbers to the end user with a Message box and writes the sum of the numbers in cell P1. Put this macro under button number 7.
Task 8 (14p):
Write a macro that asks for eight numbers from the end user with an Userform. If any of the numbers in the User form is negative, the macro changes the number to zero. Then the macro gives the unique numbers, but not the number zero, one by one to the end user with a message box and writes all numbers (except zero) in cells Q1, R2, Q2, R3 and so on. You must use procedures. Note that if all numbers are zero before printing, nothing is given to the end user nor printed. If the end user has not given all the eight numbers (there are empty boxes on the Userform) the macro will give an error message to the end user and does not start with any printing until all eight numbers have been given.
With unique numbers means that if there is for example the number 7 twice or more, in the Userform, the macro only prints it one time. Put this macro under button number 8.
The working order for the macro is as follows; the macro opens an Userform with boxes for the input, then the end user fills the eight boxes with numbers, the end user pushes a button with the text “Fix numbers” and the macro first checks if the end user gave all the 8 numbers, then the macro changes any negative numbers to zero. Then the macro works with the message boxes, and finally do the printing in the Excel sheet.
The Userform with the eight numbers should not be closed by the macro. Only the end user should be able to close it with Cancel.
The Userforms and Splash Screen can use the default design elements provided by Excel.
For all tasks, a Cancel button must be provided on all Userforms so the end user can exit the macro whenever she/he wants. Cancel should never give any message.
Task A (10p):
a) Build a Splash Screen with some appropriate text and a picture and an OK button that closes it. 5p
b) Build a button in a Excel worksheet that opens an Userform with eight buttons. The eight buttons must have the texts Task 1, Task 2, Task 3, Task 4, Task 5, Task 6, Task 7 and Task 8. Make the Userform free so it can be open while working in Excel. The Userform must be open while running the task macros. Only the end user should be able to close it with Cancel. 5p All tasks except task 6 must use one sheet only.
Task 1 (10p):
Type the name of ten optional products in cells A1:A10. When the user presses button number 1 (see task A), an Userform opens with a Listbox() with the products. The user can then select a product from the list in the list box. Next, the end user presses a button on the list box page (on the user form) and a message is given telling the end user which product the user has chosen. Finally, the chosen product is also printed in cell A12.
Task 2 (10p):
Under button number 2, you should have a macro asking for five optional integer numbers from the end user, one number at a time with an Inputbox(). If the end user gives the number 666 the macro stops, and the end user gets a message “Wicked number – Ending”. Next, the numbers are inserted into cells C2:C6. Then the numbers are sorted, and finally printed in cells D2:D6 in sorted order with smallest number first.
Task 3 (10p)
Under button number 3, you should have a macro that loads two integers for a range between 1 and 100 from the end user. If the user gives a number greater than one hundred or less than one, the user should receive an error message and the macro should ask for a new number. When the two range numbers have successfully been given (for example 6 and 91), the macro then asks the user to guess a number in the range (we assume that the user does not know the range). If the user guesses wrong, a message is given with a Message Box that the number was too large (in the example more than 91) or too small for the range (in the example less than 6), then the macro asks for a new number. This continues until the user guesses a number within the range. Note that the end user might give the bigger number for the range before the smaller one. For example, 50 and 5, range 5-55.
Task 4 (10p):
Under button number 4, you should have a macro that loads five numbers from the end user and five numbers from cells B2:B6. Ten numbers all together. The macro prints then any numbers bigger than twelve out of the ten numbers in cells B7 onwards. Finally, the sum and average of all the ten numbers is given in a Message Box. Start by inserting five integer numbers in B2:B6 manually (the macro should not do this).
Task 5 (12p):
Under button number 5, you must have a macro that opens an Userform where the end user can give a company name and then select from the same userform the city from a list with the possibilities Helsinki, Turku, Tampere, and Vaasa (it must be impossible to have other cities). Then, the macro asks for a cell reference for a cell where the company name is to be entered, note that the Cancel button here must work. Finally, the macro asks for a cell reference for a cell in which the city is to be entered, also here the Cancel button must work. The company name and the city must be given, and the macro must not go to any printing if they have not been given.
Task 6 (12p):
Create a Listbox() on a Userform for a new worksheet. Fill the list box with seven customer names and addresses. Under button number 6, you should have a macro that opens the Userform with the list box. The list box must be such that the end user can select several customers at once. Also build an OK button that writes the customer names you selected into cell E2 and onwards, and the customers' addresses into cell F2 and onwards. Insert appropriate headings in cells E1 and F1. Build the list box so it contains headers for both columns. If the end user runs the macro several times, the selected customers and addresses must be written in the following empty cells.
Task 7 (12p):
Write a macro that asks for numbers from the end user with an InputBox(), one number after another until the average of the given numbers is 200 or more. The InputBox() must have a working Cancel button, pushing it stops the macro with no further actions. Then the macro gives the average of the given numbers to the end user with a Message box and writes the sum of the numbers in cell P1. Put this macro under button number 7.
Task 8 (14p):
Write a macro that asks for eight numbers from the end user with an Userform. If any of the numbers in the User form is negative, the macro changes the number to zero. Then the macro gives the unique numbers, but not the number zero, one by one to the end user with a message box and writes all numbers (except zero) in cells Q1, R2, Q2, R3 and so on. You must use procedures. Note that if all numbers are zero before printing, nothing is given to the end user nor printed. If the end user has not given all the eight numbers (there are empty boxes on the Userform) the macro will give an error message to the end user and does not start with any printing until all eight numbers have been given.
With unique numbers means that if there is for example the number 7 twice or more, in the Userform, the macro only prints it one time. Put this macro under button number 8.
The working order for the macro is as follows; the macro opens an Userform with boxes for the input, then the end user fills the eight boxes with numbers, the end user pushes a button with the text “Fix numbers” and the macro first checks if the end user gave all the 8 numbers, then the macro changes any negative numbers to zero. Then the macro works with the message boxes, and finally do the printing in the Excel sheet.
The Userform with the eight numbers should not be closed by the macro. Only the end user should be able to close it with Cancel.
The Userforms and Splash Screen can use the default design elements provided by Excel.