Messages and User Inputs
Exercise 5.03

Exercise 5.03

The aim of this exercise is to use input boxes to allow a user to compare mortgage deals.  Exciting stuff!

Files Needed

Click here to download the file need for this exercise.

Instructions

  1. Open the Mortgage calculator.xlsm file, insert a new module and create a subroutine.
  2. Write code to display an input box which asks the user to type in an amount to borrow.
  • Ensure that the input box will only accept a number.
  • Set the default value using the number in B2 on the Calculator worksheet.
  • Capture the result in a variable.
  • Exit from the procedure if the user clicks Cancel.
Loan input

Users in London may need to stick a couple of zeroes on the end of that.

 
  1. If the user enters a negative number, display a message and exit the procedure.
Loan error

Your message could resemble this one.

 
  1. Add a second input box which asks the user how many years they would like to repay the loan over.
  • Ensure that the input box will only accept a number.
  • Set the default value using the number in B3 on the Calculator worksheet.
  • Capture the result in a variable which can store a whole number.
Term input

I wonder if VBA will still be used in 50 years?

 
  1. Display a message and exit from the procedure if the user enters a number less than 1 or greater than 50.
Term error

It even said so on the input box. Tut.

 
  1. Add an input box which asks the user to select a cell containing the reference code of a mortgage deal on the Inputs worksheet.
Select deal

Capture the result of the input box in a Range variable.

 
  1. Display a message and exit from the procedure if the user has selected more than one cell.
Multi cell error

One mortgage is boring enough.

 
  1. Display a message and exit from the procedure if the user selects a cell outside the list of mortgage reference codes.
Non mortgage error

Come on guys, it can't be that difficult.

 
  1. Write code to write the values of the variables and the details for the selected mortgage to the relevant cells on the Calculator worksheet.  Select the worksheet so that the user can see the results.
Copy details

Write the details into the pale yellow cells.

Answer Files

Click here to download a file containing a suggested answer.

This page has 0 threads Add post