Dear colleagues,

Please advise with the subject issue. Here are details.

I need to run a certain macro with any user created workbooks. At the
beginning I've used a Workbook_Open sub and copied my macro to all
workbooks. With this I'am having a lot of limitations and
inconveniences.

I want to move my code inside the Add-In and run it with any workbooks
user created.

Please advise how to get known using Add-Ins macro that user opened a
workbook without adding even a single line of code inside user created
workbooks?

Thank you.

Sergey.

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.

See Chip Pearson's site:
http://www.cpearson.com/excel/AppEvent.htm

Sergiy wrote:

Dear colleagues,

Please advise with the subject issue. Here are details.

I need to run a certain macro with any user created workbooks. At the
beginning I've used a Workbook_Open sub and copied my macro to all
workbooks. With this I'am having a lot of limitations and
inconveniences.

I want to move my code inside the Add-In and run it with any workbooks
user created.

Please advise how to get known using Add-Ins macro that user opened a
workbook without adding even a single line of code inside user created
workbooks?

Thank you.

Sergey.

--

Dave Peterson

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 <----

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

Dear Dave,

What is the difference between Option 1 ant Option 2 and what technique
is preferable to use?


Option 1
Public WithEvents App As Application
Private Sub Workbook_Open()
Set App = Application
End Sub


Option 2
Public WithEvents App As Application
Dim AppClass As New EventClass

Private Sub Workbook_Open()
Set AppClass.App = Application
End Sub

One uses a special class module. One uses the ThisWorkbook module--which itself
is a special class module, too.

I learned from Chip's site first, but there have been a few posts (KeepItCool
and Bob Phillips, IIRC) who suggested using ThisWorkbook.

I guess I find the ThisWorkbook module easier to use and explain. But Chip's
site is still very useful to learn about application events.

Sergiy wrote:

Dear Dave,

What is the difference between Option 1 ant Option 2 and what technique
is preferable to use?

Option 1
Public WithEvents App As Application
Private Sub Workbook_Open()
Set App = Application
End Sub

Option 2
Public WithEvents App As Application
Dim AppClass As New EventClass

Private Sub Workbook_Open()
Set AppClass.App = Application
End Sub

--

Dave Peterson

Just to add, some recommend minimizing code in the Thisworkbook module. Or
if code is to be placed there in an xla, after developing rebuild a new
Workbook with fully tested code and distribute the new untarnished workbook.

Here's a comment from Rob Bovey -

"I tend to use Auto_Open simply to avoid placing any code in the code module
behind the ThisWorkbook object. Any code in this area has the possibility of
becoming corrupt. If corruption does occur in this area, there's no way to
fix it short of rebuilding the whole workbook. This obviously doesn't apply
if you're using a WithEvents class module to trap the Workbook_Open event
from some other workbook, though."

Regards,
Peter T

"Dave Peterson" wrote in message
...
One uses a special class module. One uses the ThisWorkbook module--which
itself
is a special class module, too.

I learned from Chip's site first, but there have been a few posts
(KeepItCool
and Bob Phillips, IIRC) who suggested using ThisWorkbook.

I guess I find the ThisWorkbook module easier to use and explain. But
Chip's
site is still very useful to learn about application events.

Sergiy wrote:

Dear Dave,

What is the difference between Option 1 ant Option 2 and what technique
is preferable to use?

Option 1
Public WithEvents App As Application
Private Sub Workbook_Open()
Set App = Application
End Sub

Option 2
Public WithEvents App As Application
Dim AppClass As New EventClass

Private Sub Workbook_Open()
Set AppClass.App = Application
End Sub

--

Dave Peterson

Thanx Dave.

Can you suggest me with the link to Chip's site?

Sergey

Hi Dave!

Did you mean Private Sub Workbook_*Before*Close() instead of Private
Sub Workbook_Close() in your example?

Yep!

Sorry about the typo.

Sergiy wrote:

Hi Dave!

Did you mean Private Sub Workbook_*Before*Close() instead of Private
Sub Workbook_Close() in your example?

--

Dave Peterson

From that initial response:

See Chip Pearson's site:
http://www.cpearson.com/excel/AppEvent.htm



Sergiy wrote:

Thanx Dave.

Can you suggest me with the link to Chip's site?

Sergey

--

Dave Peterson