VBA Excel Programming Prompt the user to enter if they wish to search for orders by Order ID or Zip Code. o In the situa
Posted: Sun May 15, 2022 12:16 pm
VBA Excel Programming
Prompt the user to enter if they wish to search for orders by
Order ID or Zip Code.
o In the situation the user does not enter a valid way to search
for orders, the user should be provided with an error message.
o In the situation the user does enter a valid way to search for
orders, additional inputs should be gathered depending on how the
user wants to search. For example, if the user enters they wish to
search for orders by Order ID, they should be prompted for the
Order ID number they want to search for. The correct subroutine
should then be called (based on how the user wants to search) to
perform the search.
o Create multiple subroutines to handle each of the search
options. The subroutine will either show order data (i.e., Order
ID, Category, Sub-Category and Quantity) matching the search
criteria, with a professional, well-formatted message to the user,
or inform the user their search did not result in any matches. For
example, if the user searched by zip code for 22234, the results
would inform the user that results were found and show the details
for Order IDs 4 and 5, along with their categories, sub-categories,
and quantities.
• Create a second button on the worksheet linked to a macro that
performs the following task aimed at helping the company determine
low-ordering states to increase their marketing efforts:
o For states that have completed at least one order, determine
the state(s) that have the lowest number of orders submitted. Hint:
Investigate the Worksheet Function object for methods available.
Note: You are only considering states that have at least one order.
For example, no order has been made from Alaska, so this state
should not be considered.
o List all states, in alphabetic order, in a user-professional,
well-formatted message, that match the lowest number of orders
submitted
Create a user-defined function that will calculate an order
total based on a unit price and quantity ordered.
• Within the worksheet, fill column M using the user-defined
function you created. Note: You should do this in the worksheet
itself and not in VBA.
Prompt the user to enter if they wish to search for orders by
Order ID or Zip Code.
o In the situation the user does not enter a valid way to search
for orders, the user should be provided with an error message.
o In the situation the user does enter a valid way to search for
orders, additional inputs should be gathered depending on how the
user wants to search. For example, if the user enters they wish to
search for orders by Order ID, they should be prompted for the
Order ID number they want to search for. The correct subroutine
should then be called (based on how the user wants to search) to
perform the search.
o Create multiple subroutines to handle each of the search
options. The subroutine will either show order data (i.e., Order
ID, Category, Sub-Category and Quantity) matching the search
criteria, with a professional, well-formatted message to the user,
or inform the user their search did not result in any matches. For
example, if the user searched by zip code for 22234, the results
would inform the user that results were found and show the details
for Order IDs 4 and 5, along with their categories, sub-categories,
and quantities.
• Create a second button on the worksheet linked to a macro that
performs the following task aimed at helping the company determine
low-ordering states to increase their marketing efforts:
o For states that have completed at least one order, determine
the state(s) that have the lowest number of orders submitted. Hint:
Investigate the Worksheet Function object for methods available.
Note: You are only considering states that have at least one order.
For example, no order has been made from Alaska, so this state
should not be considered.
o List all states, in alphabetic order, in a user-professional,
well-formatted message, that match the lowest number of orders
submitted
Create a user-defined function that will calculate an order
total based on a unit price and quantity ordered.
• Within the worksheet, fill column M using the user-defined
function you created. Note: You should do this in the worksheet
itself and not in VBA.