Excel Visual Basic for Applications online training

This page has 0 threads | Add post

Many people around the world enjoy and benefit from our tutorials. If you're one of them - and can afford to - please make a small donation to help keep this and future blogs, exercises, skills assessment tests, tips, tutorials and videos freely available to everyone!

You must enable JavaScript to be able to view our online training courses (the tabbed menu below doesn't work otherwise).

Messages and User Inputs

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

Open the Mortgage calculator.xlsm file, insert a new module and create a subroutine.
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.

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

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

Your message could resemble this one.

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.

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

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

It even said so on the input box. Tut.

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

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

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

One mortgage is boring enough.

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

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

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.