Macro to run on fileopen for all workbooks

A

aarti

Iam creating a macro in VBA. I want this macro to run whenever Iopen
file in excel or whenever i create a new file in excel. How do i d
this ?

In word, I can achieve the above by creating the macro in Normal.do
template file.

How do I achieve this in Excel...how do i make the same macro availab
to all existing and new files in Exce
 
B

Bob Phillips

Aarti,

This is a bit more complex, but can be achieved with application events

Firstly, all of this code goes in a designated workbook, one that you will
open when you start Excel.

'========================================
In a standard code module, declare a public variable of thisWB

Public thisWB as Workbook

'========================================
Insert a class module, rename it to 'clsAppEvents', with this code

Option Explicit

Public WithEvents App As Application

Private Sub App_WorkbookOpen(ByVal Wb As Workbook)

'your code or a call to your macro

End Sub
'========================================
In ThisWorkbook code module, add this event code

Dim AppClass As New clsAppEvents

Private Sub Workbook_Open()

Set AppClass.App = Application

End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Top