Excel VBA Macros | Loops and conditions exercise | Validate Dating Agency Details

This exercise is provided to allow potential course delegates to choose the correct Wise Owl Microsoft training course, and may not be reproduced in whole or in part in any format without the prior written consent of Wise Owl.

Software ==> Excel VBA Macros  (52 exercises)
Version ==> Any version of Excel
Topic ==> Loops and conditions  (5 exercises)
Level ==> Average difficulty
Subject ==> VBA training
Before you can do this exercise, you'll need to download and unzip this file (if you have any problems doing this, click here for help).

You need a minimum screen resolution of about 700 pixels width to see our exercises. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Open the file called WODA Application.xlsm.  Your task is to check that valid details have been entered in Sheet1 before they are copied to Sheet2.

Enter details

You can click the button to copy the details to Sheet2, but we should check that sensible details have been entered first.

 

In the CopyApplicationDetails procedure, add an If statement to check if C4 is empty.  If so, change the cell colour, display a message and exit from the subroutine.

Validate name

It should be fairly obvious what the user has done wrong.

Add an Else clause which removes the cell fill colour if a name has been entered.

Range("C4").Interior.ColorIndex = xlNone

Add a new If statement to check if the value in C5 is a date and, if not, indicate this to the user and exit from the procedure.  You can use the IsDate function to do this:

If Not IsDate(Range("C5").Value) Then

Check that your code works when C5 is empty or contains a value which isn't a date.

Check date

You shouldn't be able to proceed until you've entered a valid date.

We'd like to ensure that a user doesn't add themselves multiple times.  Add an If statement to check if the user name already exists in Sheet2 and prevent them from continuing if so.  You can use the Find method to do this:

If Not Worksheets("Sheet2").Range("A:A").Find( _

Worksheets("Sheet1").Range("C4").Value) Is Nothing Then

Test that your code works if you enter an existing name.

Duplicate name

Names that already exist should be detected.

Check that you can enter a new name then save and close the file.

You can unzip this file to see the answers to this exercise, although please remember this is for your personal use only.
This page has 0 threads Add post