How to Auto-Run VBA Project When Excel Workbook is Opened

Home More Videos
















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.




Watch the video on youtube