Table of contents

Introduction

Some Basic Events

2.1.

Workbook Open Event

2.2.

Worksheet Change Event

2.3.

BeforeDoubleClick Event

2.4.

Highlight Active Cell

2.5.

Create a Footer

Application of Events

3.1.

Bills and Coins

3.2.

Rolling Average Table

FAQs

4.1.

What do you mean by currency format in excel?

4.2.

What is the ribbon in excel?

4.3.

What do you understand by Macro in Ms excel?

Conclusion

Last Updated: Mar 27, 2024

Easy

Events in Excel

Author Naman Kukreja

Do you think IIT Guwahati certified course can help you in your career?

Yes

Introduction

Data is an essential key to success in this technologically enhancing world, and if you want to be an active part of it, you should be good at dealing with data. Dealing with data involves monitoring, analyzing, gathering, consolidating, and connecting the data.

Excel is an excellent tool for dealing with data and performing different operations with the given data. We can either perform each operation manually or write the code in the Visual Basic Editor and let it deal with all the new and old data. Using the Visual Basic Editor mode may be difficult initially, but it is the most used technique in the long term as it saves a lot of time and effort.

So how are events related to Visual Basic Editor? We will learn about this while moving further in this blog. So without wasting any further time, let’s get on with our topic.

Some Basic Events

Before moving on to examples of events, let us first understand what an event is.

Events are the actions performed by the users that directly or indirectly trigger the Excel VBA to execute the code.

Workbook Open Event

Follow the steps given below to execute the code in the workbook.

Open the Visual Basic Editor and double click on this workbook in the project explorer.
Select open from the right drop-down list and workbook from the left drop-down list.

Add the following code in the open event of the workbook:

MsgBox "Good Night"

Now save and close the excel file and open it again. The result will be as follows:

Result:

Worksheet Change Event

As the name suggests, the code for worksheet change will be executed when you change a cell on the worksheet.

Open the Visual Basic Editor and double click on this sheet in the project explorer.
Select change from the right drop-down list and worksheet from the left drop-down list.

Add the following code. This will perform when we change the value of cell B2:

If Target.Address = "$B$2" Then

 
End If

Now we will add the condition for the change in cell B2 like the below code:

If Target.Value > 60 Then MsgBox "Goal Achieved"

When you enter a value greater than 60 in cell B2 the result will be like this:

Result:

BeforeDoubleClick Event

It will execute the code when you double-click on the cell on the worksheet. Follow the steps given below:

Open the Visual Basic Editor and double click on this sheet in the project explorer.
Select BeforeDoubleClick from the right drop-down list and worksheet from the left drop-down list.

Now add the following lines of code:

Target.Font.Color = vbRed
Cancel = True

The above code will change the color of the cell to red when doubleclicked on the cell, and the cancel statement is true when you don't want this to be your default variable.
Test the given code. The result will be like this:

Result:

Highlight Active Cell

In this blog section, we will learn about the program that highlights the column and row of the active cell. Each time you select a new cell, the corresponding column and row change.

Open the Visual Basic Editor and double click on this sheet in the project explorer.
Select SelectionChange from the right drop-down list and worksheet from the left drop-down list.

Add the following lines of code first, and we will declare four variables like this:

Dim rowNumberValue As Integer, columnNumberValue As Integer, i As Integer, j As Integer

First, we add the line that changes the background color of the corresponding cells to 'No Fill.'

Cells.Interior.ColorIndex = 0

Now we take the active cell row and column into different variables shown below:

rowNumberValue = ActiveCell.row
columnNumberValue = ActiveCell.column

Now, we highlight the row and column that has the value less than our active cell row and column respectively with blue color by using for loops like below:

For i = 1 To rowNumberValue
    Cells(i, columnNumberValue).Interior.ColorIndex = 37
Next i
For j = 1 To columnNumberValue
    Cells(rowNumberValue, j).Interior.ColorIndex = 37
Next j

Now run the code. The result will be like the below:

Result:

Create a Footer

Here, we will learn the program that creates a footer before printing the workbook. Follow the steps given below to perform the above operation.

Open the Visual Basic Editor and double click on this workbook in the project explorer.
Select before print from the right drop-down list and workbook from the left drop-down list.

To create a footer before printing of workbook, add the following code in the Visual Basic Editor.

ActiveSheet.PageSetup.LeftFooter = ActiveWorkbook.FullName

Now run the code. The output will be as follows:

Result:

Source

Application of Events

This blog section will learn about the different applications of events in excel with good examples and step-by-step explanations.

Bills and Coins

Here, we will look at a program that divides the amount into proper bills and coins.

Open the Visual Basic Editor and double click on this sheet in the project explorer.
Select change from the right drop-down list and worksheet from the left drop-down list.

The initial situation is like this:

Now add the code in your VBA first. We will declare two variables and then select the cell where we will add the amount like this:

Dim amount As Double, I As Integer
If Target.Address = "$B$2" Then

We initialize the amount with the value in cell B2 and then empty the range of frequencies B5:B16.

amount = Range("B2").Value
Range("B5:B16").Value = ""

Now we will split the entered amount, and inside that, we will use a do-while loop for performing functions like this:

For i = 5 To 16

 
Do While amount >= Cells(i, 1).Value
Cells(i, 2).Value = Cells(i, 2).Value + 1
amount = amount - Cells(i, 1).Value
Loop
 Next i
End if

Now run the program the result will be like this:

Result:

Rolling Average Table

This will give the average result of the numbers present in the given range.

Before directly moving to the code, place a command button on the screen.

Now follow the steps below to make a rolling average table.

Open the Visual Basic Editor and double click on this sheet in the project explorer.
Select change from the right drop-down list and worksheet from the left drop-down list.

First, declare three variables to work with and then store the value of the target cell-like shown below:

Dim newvalue As Integer, firstfourvalues As Range, lastfourvalues As Range

 
If Target.Address = "$B$3" Then

In the code below first, we initialize the value then we update them because when the user enters a new value the earlier values will roll down and then end the if statement.

newvalue = Range("B3").Value
Set firstfourvalues = Range("D3:D6")
Set lastfourvalues = Range("D4:D7")
lastfourvalues.Value = firstfourvalues.Value
Range("D3").Value = newvalue
End if

Now, enter the formula AVERAGE(D3:D7) into cell D8, and check the result.

Result:

In the images above, you can see after entering a new value, the previous values are rollbacked.

FAQs

What do you mean by currency format in excel?

When you change the format to the currency format of any column, then excel adds the currency symbol in front of your data.

What is the ribbon in excel?

In MS-Excel, the ribbon refers to the uppermost part of the program, which comprises menu items and toolbars. CTRL+F1 may be used to reveal or hide the ribbon. The ribbon replaces the toolbars and menus and runs across the top of the program. On the top of the ribbons are numerous tabs, each with its own instructions.

What do you understand by Macro in Ms excel?

Iterating through a set of jobs is done via macros. Users may develop macros to automate their repetitive tasks and instructions. Depending on the user, macros may be created or recorded.

Conclusion

In this article, we have extensively discussed Events in excel with its different types and examples from scratch with step-by-step explanation, and along with that, we have also discussed its different applications with examples and explanation.

We hope that this blog has helped you enhance your knowledge of excel. If you are interested in learning how to work with VBA with an example of date and time, you must refer to this blog. Here you will get and complete idea about the date and time and the different functions we can perform with them using VBA. If you would like to learn more, check out our articles on Code studio. Do upvote our blog to help other ninjas grow.

“Happy Coding!”