Chip suggests using a class module, but there are other ways, too.

You can use this for a shell--it all goes into the ThisWorkbook module:

Option Explicit
Public WithEvents xlApp As Excel.Application
Private Sub Workbook_Open()
Set xlApp = Application
End Sub
Private Sub Workbook_Close()
Set xlApp = Nothing
End Sub
Private Sub xlApp_NewWorkbook(ByVal Wb As Workbook)
MsgBox "Hey you created a workbook named: " & Wb.Name
End Sub
Private Sub xlApp_WorkbookOpen(ByVal Wb As Workbook)
MsgBox "Hey you opened a workbook named: " & Wb.Name
End Sub



Sergiy wrote:

Many thanks, Dave!

Dave Peterson wrote:
If you want the macro to run each time you open an existing workbook (or create
a new workbook), you'll need an application event.

I just did it. I was trying to make it works for some time and I was
failed, because I'm not familiar with VBA (from time to time I am
coding with Delphi).
So for me was quite difficult to get known the syntax how to make event
tracking in VBA

As the result of my efforts now mo code is

'Here was most difficult lines for me :-)
' ---- cut here <----
Public WithEvents App As Application

'Initialisation the Application object to trace the events
Private Sub Workbook_Open()
Set App = Application
End Sub
' ---- cut here <----

And other I allready can do ...
' ---- cut here <----
'Tracing the event when Workbook opened
Private Sub App_WorkbookOpen(ByVal Wb As Workbook)
If Application.Workbooks.Count 0 Then
On Error Resume Next

Application.Interactive = False
Application.DisplayAlerts = False
Application.ScreenUpdating = False

'do some stuff here

For Each Wb In Application.Workbooks
MsgBox Wb.Name
Next

Application.Interactive = True
Application.DisplayAlerts = True
Application.ScreenUpdating = True

On Error GoTo 0
Else
' MsgBox "No workbook opened"
End If

End Sub
' ---- cut here <----

--

Dave Peterson