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?
- What Are The Different Types of Events?
- Practical Learning: Workbook_Open Event
- Example File with Workbook Events, Worksheet Events and Form Events
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:
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…
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…
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…
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
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
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
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:
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
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?
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
‘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"
For iCntr = 1 To CByte(iNumberOfSheets)
‘If you want to add new worksheets after activesheets
‘If you want to add new worksheets from last worksheet
Call this procedure using command button.
Hope this helps!
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.
Thanks a lot for your help. It’s working fine. Thanks a lot.
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.
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.