Thursday, April 16, 2009

Macros in Excel : Auto Open Macro / Auto Run Macro

Many times we want certain task to be executed when you open a particular file. This can be achieved by auto open macro or auto run macro. This is the macro which executes itself when you open a excel file.

There are two methods to do this

Method 1. Auto open macro in VBA Project Module

In this case you add your macro code to VBA project module and it gets executed when you open the file. When you go to your VBA editor, you select a module as highlighted in the picture and copy following code to have auto run macro.

Private Sub Auto_Open()

MsgBox "This is auto open macro in Module"

End Sub

Method 2. Auto open macro in ThisWorkbook Section

In this method you add your code to Thisworkbook Section of your excel file. double click on highlighted potion in your VBA editor and paste following code

Private Sub Workbook_Open()

MsgBox "This is auto open macro in This workbook"

End Sub

You can replace the message box line with your code / action you want your macro to do. I have used this as example to keep my macro code short for better understanding.

Auto open Macro , Excel auto open macro , Auto run macro , Auto open excel , VBA excel workbook open


  1. how to get around enable content so the auto open macro will be enabled?

  2. how to get around enable content so the auto open macro will be enabled?

  3. Can I make an Auto_open macro conditional dependent upon a cell value contained within the spreadsheet?

    1. Of course. You can use "If .... Then .... Else", "Select Case" etc.

      If CONDITION 1 Then
      Your code
      Elseif CONDITION 2 Then
      Your other code
      Else Exit Sub

  4. can you call a "beforeclose macro" in the module too?