Many times we want to run some predefined and repetitive steps as the workbook opens or closes. For example, opening up a connection to the database, refreshing the pivot tables in the workbook or simply greeting the user. This can be done using Workbook Events of Excel.
In this article we will learn how you can run a macro as the workbook opens and closes in the most easy way.
To run macro as the workbook opens we will use Workbook Event Workbook_Open. The syntax of this event is this.
Private Sub Workbook_Open() 'Your Code here. End Sub
To write in a workbook event do this.
Open VBE using Alt+F11. Now in project explorer, locate the workbook in which you want to have the code. Now find ThisWorkbook object in project explorer. Double click on it.
Now you can see two drop down above the coding area. Click on the first one and select
Workbook. Now all the available events related to the workbook can be seen in the right drop down. Click on it and choose open. This will write a pseudo or empty workbook_open sub routine on the coding area.
All the code that you want to run as the workbook opens needs to be written here. You can simply call subroutines written in any module in this event. This code will trigger the event to run anything written between these two lines.
Let's use the Workbook_Open event to verify a user and greet the user as the workbook opens.
Private Sub Workbook_Open()ps = 12345 pw = InputBox("Please enter the password.") + 0 If pw = ps Then MsgBox ("Welcome Sir!") Else MsgBox ("Goodbye") ThisWorkbook.Close End If End Sub
Save the workbook and run the code once. Close the workbook. Now try to open it. The code will run as soon as the workbook opens.
You can put anything in this code. We can even call any subroutine from any module in the workbook.
Private Sub Workbook_Open() Call SplitWorkbook ' calling subroutine that splits workbook End Sub
So yeah, that is it. It is this simple to auto run a macro as the workbook opens. You can use it to refresh data, pivot tables and charts automatically. I hope my writing was explanatory enough and the site served you well. If this didn't help, write your query in the comments section below. I will reply to it as soon as possible.
Workbook events using VBA in Microsoft Excel : Here you can find all the workbook events that can be triggered to run specific code on specific events.
Using Worksheet Change Event To Run Macro When any Change is Made | So to run your macro whenever the sheet updates, we use the Worksheet Events of VBA.
Run Macro If Any Change Made on Sheet in Specified Range | To run your macro code when the value in a specified range changes, use this VBA code. It detects any change made in the specified range and will fire the event.
Simplest VBA Code to Highlight Current Row and Column Using | Use this small VBA snippet to highlight the current row and column of the sheet.
50 Excel Shortcuts to Increase Your Productivity | Get faster at your task. These 50 shortcuts will make your work even faster on Excel.
The VLOOKUP Function in Excel | This is one of the most used and popular functions of excel that is used to lookup value from different ranges and sheets.
COUNTIF in Excel 2016 | Count values with conditions using this amazing function. You don't need to filter your data to count specific values. Countif function is essential to prepare your dashboard.
How to Use SUMIF Function in Excel | This is another dashboard essential function. This helps you sum up values on specific conditions.
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.