Read our blogs, tips and tutorials
Try our exercises or test your skills
Watch our tutorial videos or shorts
Take a self-paced course
Read our recent newsletters
License our courseware
Book expert consultancy
Buy our publications
Get help in using our site
545 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owl trainers only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Search our website
We also send out useful tips in a monthly email newsletter ...
In the previous part of this lesson you saw how to use an If statement to perform a single action when a condition is met. In practice, you'll often want to perform a series of instructions based on the result of a condition. This part of the lesson shows you how to use a Block If statement to do exactly that.
You can click here to download the file for this page.
You can click here to download a file containing the sample code.
Extract and open the file linked to in the Files Needed section above. The workbook contains the same example as you used in the previous part of this lesson:
You can click the button to add the details you have entered in column B to the table starting in column D.
Our aim, as in the previous example, is to prevent the user from adding incomplete records to the table.
Open the VBE and locate the subroutine called Add_To_List. You'll find that it contains a simple If statement to check if cell B2 is empty:
If cell B2 is empty, the code will execute the Exit Sub statement.
If we want to do more than just exit the subroutine when cell B2 is empty we'll find it difficult to add more instructions to the same line of code. Instead, we'll convert the existing simple If statement into a block If:
To begin, move the Exit Sub statement to a new line below the If statement. It's conventional to indent this line one extra tab space.
When you separate the If statement from the instruction you want it to perform, you must also add an End If statement. Try running your code without the End If statement:
You'll see this fairly descriptive error message. Click OK and then stop running the procedure.
Add the End If statement as shown below:
Write the End If statement on a new line. It's conventional to write this at the same indent level as the original If statement.
Make sure that cell B2 is empty and then use the F8 key to step through the code:
Press F8 until you reach this line.
If B2 is empty, pressing F8 again will cause the code to move to the instruction below the If statement:
You can press F8 again to end the subroutine.
Now try adding a value to cell B2 (and optionally, B4):
Enter any value into cell B2.
Return to the VBE and press F8 to begin stepping through the subroutine:
Press F8 until you reach this line.
If B2 isn't empty, pressing F8 again will jump to the End If statement:
The code will skip any instructions between the If and End If lines when the condition is not met.
You can press F5 to continue running the code to the end of the procedure:
Return to Excel to see that the details have been added to the list.
You can add as many instructions as you like between the If and End If statements. Let's make it more obvious to the user that they can't proceed without adding a film name to cell B2:
Add a line which changes the colour of cell B2. It's important that this line appears above the Exit Sub statement.
We can also help the user by selecting the cell into which they need to write a film name:
Automatically selecting cell B2 means the user can simply begin typing.
Finally, we can display an error message in another cell - we'll use cell A6 for this example:
You can enter a different message if you prefer.
In Excel, remove the value from cell B2 and then click the button on the worksheet:
After clicking the button, your worksheet should look like this. Cell A6 has already been formatted with a red font colour.
It's worth adding some code to remove your problem indicators when the user has correctly filled in a film name. Add two instructions below the End If statement:
The two new instructions will only be executed when the Exit Sub statement is not executed, i.e. when cell B2 is not empty.
Add a new film name to cell B2 and click the button on the worksheet:
When you have entered a value in cell B2 your code will automatically tidy up the worksheet.
To practise writing block If statements:
Sub Add_To_List()
If Range("B2").Value = "" Then
Range("B2").Interior.Color = rgbPink
Range("B2").Select
Range("A6").Value = "Enter a film name!"
Exit Sub
End If
If Range("B3").Value = "" Then
Sub Add_To_List()
If Range("B2").Value = "" Then
Range("B2").Interior.Color = rgbPink
Range("B2").Select
Range("A6").Value = "Enter a film name!"
Exit Sub
End If
If Range("B3").Value = "" Then
Range("B3").Interior.Color = rgbPink
Range("B3").Select
Sub Add_To_List()
If Range("B2").Value = "" Then
Range("B2").Interior.Color = rgbPink
Range("B2").Select
Range("A6").Value = "Enter a film name!"
Exit Sub
End If
If Range("B3").Value = "" Then
Range("B3").Interior.Color = rgbPink
Range("B3").Select
Range("A6").Value = "Enter a date!"
Sub Add_To_List()
If Range("B2").Value = "" Then
Range("B2").Interior.Color = rgbPink
Range("B2").Select
Range("A6").Value = "Enter a film name!"
Exit Sub
End If
If Range("B3").Value = "" Then
Range("B3").Interior.Color = rgbPink
Range("B3").Select
Range("A6").Value = "Enter a date!"
Exit Sub
End If
Sub Add_To_List()
If Range("B2").Value = "" Then
Range("B2").Interior.Color = rgbPink
Range("B2").Select
Range("A6").Value = "Enter a film name!"
Exit Sub
End If
If Range("B3").Value = "" Then
Range("B3").Interior.Color = rgbPink
Range("B3").Select
Range("A6").Value = "Enter a date!"
Exit Sub
End If
Range("B2:B3").Interior.ColorIndex = xlNone
Range("A6").ClearContents
This is what you should see if you haven't entered a value in cell B3.
The worksheet should be reset when you have entered a value in cell B3.
Sub Add_To_List()
If Range("B2").Value = "" Then
Range("B2").Interior.Color = rgbPink
Range("B2").Select
Range("A6").Value = "Enter a film name!"
Exit Sub
End If
If Range("B3").Value = "" Then
Range("B3").Interior.Color = rgbPink
Range("B3").Select
Range("A6").Value = "Enter a date!"
Exit Sub
End If
Range("B2:B3").Interior.ColorIndex = xlNone
Range("A6").ClearContents
If Range("B4").Value = "" Then
Range("B4").Value = "None"
End If
If Range("B5").Value = "" Then
Range("B5").Value = "Unknown"
End If
'Select the next blank cell in column D
Range("D1048576").End(xlUp).Offset(1, 0).Select
'Copy the entered values into the table
ActiveCell.Value = Range("B2").Value
ActiveCell.Offset(0, 1).Value = Range("B3").Value
ActiveCell.Offset(0, 2).Value = Range("B4").Value
ActiveCell.Offset(0, 3).Value = Range("B5").Value
End Sub
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2024. All Rights Reserved.