Events in Excel VBA

Home/Excel VBA/Events in Excel VBA

Events in Excel VBA help us to write event enabled programming. In this tutorial we are explaining basic of Event Programming and frequently used events with examples. You can download the example file and explore it.
In This Section:


What Are Events?

Event programming is the most useful tool which helps to monitor specific user actions within Excel. For example, if the user Opening a Workbook, Selects a Worksheet, enters data into a Cell, or saves a Workbook, these actions are all Excel Events.

What Are The Different Types of Events?

Events are linked to Excel Worksheets, Charts, Workbooks, UserForms, or to the Excel Application itself. We can mainly classify these events as following:

Workbook Events:

Events that linked to a particular workbook.
For examples, Open (when the workbook is opened or created) , BeforeSave (when the workbook is about to be saved),etc…

Worksheet Events:

Events that linked to a particular worksheet.
For examples, Change (when a Cell on the sheet is changed), SelectionChange (when the user moves selection from one the cell to another), etc…

Chart Events:

Events that linked to a particular chart.

For examples, Select (when an object in the chart is selected), SeriesChange (when a value of a data point in a series is changed). etc…

Application Events:

Events that linked to the application (Excel).
For examples, WorkbookBeforeClose (when any workbook is about to be closed), and SheetChange (a cell in any open workbook is altered).

UserForm and ActiveX Contorls Events:

Events that linked to a particular UserForm or an object contained on
the UserForm.
For example, Initialize (before an userform is displayed), Click (when an ActiveX control is clicked), etc…

Events in Excel VBA – Practical Learning: Workbook_Open Event

If you want to execute a macro on opening your Excel File, you can write the code for Workbook_Open event. Follow the below steps to do this:

Step 1: Open your Workbook and Open the VBE Editor by pressing Alt+F11

events - examples 1

Step 2: Double Click On “ThisWorkbook” module -> Select “Workbook” From the left side drop-down list -> Select “Open” from the right side drop-down list as shown below:

You will see the the code for open event as follows:

Private Sub Workbook_Open()
End Sub

Events in Excel VBA- examples 2

Step 3: You can write the statements which you wants to execute while opening the workbook in this event procedure:
Private Sub Workbook_Open()
'write the statements here to execute while opening this workbook
End Sub

For example if you wants to show a message box while opening the workbook, your code will be something like this:

Private Sub Workbook_Open()
MsgBox "Workbook Event: Auto Run or Workbook_Open (You can run any procedure automatically while opening the workbook"
End Sub
Step 4: You are done!Yes- Save your workbook as a macro enabled file (i.e; .xlsm), Close and Open to check it

A message box should appear while opening your workbook (after enabling the macros) as shown below:

events - examples 3

Example File with Workbook Events, Worksheet Events and Form Events

Download the below file and see the several examples on Workbook Events, Worksheet Events and Form Events

Download Now: Events

By |August 11th, 2013|Excel VBA|6 Comments

About the Author:

PNRao is a passionate business analyst and having close to 10 years of experience in Data Mining, Data Analysis and Application Development. This blog is his passion to learn new skills and share his knowledge to make you expertise in Data Analysis (Excel, VBA, SQL, SAS, Statistical Methods, Market Research Methodologies and Data Analysis Techniques).

6 Comments

  1. Chandra Shekhar Jha November 12, 2013 at 4:56 PM - Reply

    Your way of Teaching is marvelous.Thanks a lot. I wanted to ask one question Suppose that if i click through command button on sheet 1 then asked to me how many sheets do you want to added is it possible then how?

  2. PNRao November 12, 2013 at 11:17 PM - Reply

    You are welcome!

    Yes! It is possible to add worksheets based on your criteria. Here is the example code:

    Example to add number of worksheets based on user requirement

    [code language=”vb”]

    Sub sbAddWorksheets()

    Start:
    ‘Get the number from user
    iNumberOfSheets = Application.InputBox("How many number of worksheet do you want to add:", "Enter a number", 3, , , , , 1)

    ‘if you want to restict user: User should enter any nuber betwen 1 and 100
    If Not (CInt(iNumberOfSheets) >= 1 And CInt(iNumberOfSheets) <= 100) Then
    MsgBox "Please enter valid number between 1 and 100", vbInformation, "Hello, please check"
    GoTo Start
    End If

    For iCntr = 1 To CByte(iNumberOfSheets)

    ‘If you want to add new worksheets after activesheets
    ‘Sheets.Add After:=ActiveSheet

    ‘If you want to add new worksheets from last worksheet
    Sheets.Add After:=Sheets(ActiveWorkbook.Worksheets.Count)

    Next

    End Sub

    [/code]

    Call this procedure using command button.

    Hope this helps!
    Thanks!PNRao!

    • Chandra Shekhar Jha January 16, 2014 at 6:34 PM - Reply

      Thanks a lot for your help. It’s working fine. Thanks a lot.

      • PNRao January 16, 2014 at 8:44 PM - Reply

        Thanks Chandra!

        I have created new Forum for our readers. You can ask any Excel/ VBA questions and help other to answering their questions. I appreciate and encourage our readers to register and participate in our FORUM discussions.

        Forum

        Thanks-PNRao!

  3. Chandra Shekhar Jha January 16, 2014 at 6:33 PM - Reply

    I m big fan of you.You have made vba so easy. I have one question. Through Command button User asked in which format you want to save the file as a word template or excel template. Is it possible? If possible then how? Means before saving the particular cell value(“A1:A8”) Copy this particular value and asked where do you want to save as a word or excel template.

    • PNRao January 16, 2014 at 8:40 PM - Reply

      Hi Chandra,
      Thanks and welcome to ANALYSISTABS.

      Yes this is possible, you can show form and to ask user to choose the option in BeforeSave Events of the workbook. And based on the user selection you can open a file dialog-box to ask user to choose the folder to save the file (Please check File handling examples in our most useful list of codes).

      so now you have what format user wants to save and the folder to save. Next steps, based on these selection you can save the file in either Word or Excel (Check our examples to how to copy the data into excel or word and Save in a director.)

      Hope this helps.
      Thanks-PNRao.

Leave A Comment