This page has 0 threads | Add post

Exercises

Finding our exercises useful? Please consider helping us to share more!

Software ==>	Excel VBA Macros (52 exercises)
Topic ==>	Loops and conditions (5 exercises)
Level ==>	Average difficulty
Subject ==>	VBA training

Use If statements in Excel VBA to validate a set of user inputs in a Dating Agency workbook.

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.

Before you can do this exercise, you'll need to download and unzip this file .

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.

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.

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:

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: