EXERCISE TOPIC▼
VBA EXERCISES▼
EXCEL VBA MACROS EXERCISES▼
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 |
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.
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.
Names that already exist should be detected.
Check that you can enter a new name then save and close the file.