How to Auto Run VBA Project When Excel Workbook is Opened

A website visitor query about MS Excel VBA:
‘Hi
I’ve created a new VBA project. I know I can assign it to a command button or go to the Visual Basic editor and ‘Run’ it. But I want that the VBA project or macro runs automatically when I open my Excel workbook. I’ve tried to search on the net but couldn’t find any help. Please help me out. Thank you so much for your help in advance!
Alex’

First we create the macro VBA code by inserting a module in the VBA editor:

Click on the Developer tab
Select Visual Basic from the Code group
In the new window called Visual Basic for Applications that opens up click on the Insert menu
Select Module
A Module 1 is inserted under the Modules folder on the left-hand side of the window
In the workspace on the right-hand side start writing the subroutine or macro as shown below
We first write the name of the macro ‘mymacro’. Macro names with multiple words cannot have a space

Sub mymacro()
‘We wish to display a message using a message box that will display the date having a specific format – day – month in 2 digits – day in 2 digits – year in 4 digits and the time will be displayed in hours and minutes
MsgBox “Today is ” & Format(Date, “dddd – mm/dd/yyyy” & ” ” & Format(Time, “h:mm”))
End Sub

We double-click on ‘ThisWorkbook’ on the left-hand side and we are presented a workspace where we can write the code related tou Excel file or workbook
You can also right-click on ‘ThisWorkbook’ and select ‘View Code’ from the menu to see the workspace
From the drop-down arrow next to ‘(General’) above the workspace we select Workbook
The Declarations text on the right automatically changes to the ‘Open’ option and we see two lines of code in the workspace
Private Sub Workbook_Open()
End Sub
Between these two lines of code we write the macro name as shown below

Private Sub Workbook_Open()
mymacro
End Sub

We save the file as ‘auto-run-vba-project’ as a ‘Excel-Macro-Enabled Workbook’ with the file extension ‘.xlsm’.
We close the Visual Basic Editor window
We close our workbook
When we open the workbook again the macro mymacro will run automatically
MS Excel will give out a message ‘Security Warning. Macros have been Disabled.’
Just click on the button ‘Enable Content’ and you’ll see the message displayed as described below and also shown in the video

These three lines of code will run the macro when you open your saved workbook. The message box will display the message: Today is Saturday – 12/29/2012 19:13

Watch the MS Excel training video below to understand the concept.


2 thoughts on “How to Auto Run VBA Project When Excel Workbook is Opened

  1. Pingback: Best Training on Advanced Excel and VBA in Bangalore | jaseemblog

  2. Alan Loe

    I do not want to display a message box. i want to run the VB code I created in the Excel sheet. Please help me to do that.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *