Workbook properties code in the xla addin

[Subodh]

I have to make code applicable to the workbook.
Like workbook_beforeprint code.
Now, after i have made the code work well,
i want to save the workbook as an addin
so that the code that i have written will be applicable to all
the workbook that will be open after the addin is loaded.
Is there any tips for that
Thanks in advance.

 

[Javed]

I have to make code applicable to the workbook.
Like workbook_beforeprint code.
Now, after i have made the code work well,
 i want to save the workbook as an addin
so that the code that i have written will be applicable to all
the workbook that will be open after the addin is loaded.
Is there any tips for that
Thanks in advance.

You can use application level event application_workbookbeforeprint(Wb
As Workbook, Cancel As Boolean).For this you have to use class
mdule.Can get the details from Microsoft Site.If you need I can give
the link after a while.

 

[Javed]

You can use application level event application_workbookbeforeprint(Wb
As Workbook, Cancel As Boolean).For this you have to use class
mdule.Can get the details from Microsoft Site.If you need I can give
the link after a while.

The link is http://www.tek-tips.com/faqs.cfm?fid=4195

 

[Subodh]

The link ishttp://www.tek-tips.com/faqs.cfm?fid=4195

Thanks Javed.
I tried the link
But it didn't worked.
I am using excel 2007 and XP
This is what i did
Made a new microsoft excel file (Macro Enabled)
Inserted a class module
Pasted following code in the class module

Public WithEvents xlapp as Application

Public Sub xlapp_WorkbookOpen(ByVal Wb As Workbook)
MsgBox " Testing Trap Events! "
End Sub

Named the class module as ClsAppEvents (Don't know if it was needed as
i think these names don't matter)

Again, I made a new module file (standard) renamed it as TrapAppEvents
and typed the following code in it

Public xlApplication As New ClsAppEvents

Public Sub TrapApplicationEvents()
Set xlApplication.xlapp = Application
End Sub

Saved it and kept it open
Now,I expected that if i open new excel files then the message should
trigger..
But that was not the case.
Do i need some more thing to do?
Anything in the Thisworkbook Object
Or, Did i missed something.
Plz Help.

 

[AB]

I'm not a master of event programming but a couple of considerations:
(1) I'm not sure if you'd be better off declaring 'as new'. Unless I
missed something I'd declare it:
Public xlApplication As ClsAppEvent
i.e., without the NEW in it and then create the object in the code.
As far as I know it's not the best practice to declare as New as
otherwise you've got no way of testing if the variable has been
instantiated although for this particular purpose the NEW could be
good enough.

(2) I think that your problem could be in firing this code of yours:
Public Sub TrapApplicationEvents()
Set xlApplication.xlapp = Application
End Sub
i.e., this code must be run before all the event trapping can happen -
if this code hasn't run you don't have your xlApplication variable
created yet and because of that it obviously can't trigger any events.
So, I think that you don't run the code in the beginning.
To test it - just run the code separately - in VBE locate this code
and run it (F5) - it would create the xlApplication variable and from
then on it should trap the events BUT bear in mind that this variable
will cease to exist if you edit the code or close the file so what you
need to do is to make sure that this code runs before you want to
start trapping the events - so, perhaps you could put it in the
Workbook_Open event of the xla file something like this:
Private Sub Workbook_Open()
Set xlApplication.xlapp = Application
End Sub
This would be then in the ThisWorkbook module of the xla file - this
way whenever the xla opens it creates your variable for event trapping
and it should be fine now.

 

[Subodh]

I'm not a master of event programming but a couple of considerations:
(1) I'm not sure if you'd be better off declaring 'as new'. Unless I
missed something I'd declare it:
Public xlApplication As ClsAppEvent
i.e., without the NEW in it and then create the object in the code.
As far as I know it's not the best practice to declare as New as
otherwise you've got no way of testing if the variable has been
instantiated although for this particular purpose the NEW could be
good enough.

(2) I think that your problem could be in firing this code of yours:
Public Sub TrapApplicationEvents()
     Set xlApplication.xlapp = Application
End Sub
i.e., this code must be run before all the event trapping can happen -
if this code hasn't run you don't have your xlApplication variable
created yet and because of that it obviously can't trigger any events.
So, I think that you don't run the code in the beginning.
To test it - just run the code separately - in VBE locate this code
and run it (F5) - it would create the xlApplication variable and from
then on it should trap the events BUT bear in mind that this variable
will cease to exist if you edit the code or close the file so what you
need to do is to make sure that this code runs before you want to
start trapping the events - so, perhaps you could put it in the
Workbook_Open event of the xla file something like this:
Private Sub Workbook_Open()
     Set xlApplication.xlapp = Application
End Sub
This would be then in the ThisWorkbook module of the xla file - this
way whenever the xla opens it creates your variable for event trapping
and it should be fine now.

I am sorry
I tried the second one also.
ie. WIthout the new keyword
But it didn't work
Can't find out what the mistake was or anything i missed out.

 

[AB]

How do you ensure that this code runs:
Public Sub TrapApplicationEvents()
Set xlApplication.xlapp = Application
End Sub

i.e., when do you run this code?
The trick is to make sure that the above code is run right before you
want to start trapping the events.
So - when/how do you make the code run?

 

[Subodh]

How do you ensure that this code runs:
Public Sub TrapApplicationEvents()
     Set xlApplication.xlapp = Application
End Sub

i.e., when do you run this code?
The trick is to make sure that the above code is run right before you
want to start trapping the events.
So - when/how do you make the code run?

Let me say that
Assume i have one workbook with the above code that is open and the
trapped event is workbook_open
Now, if i open another workbook then i want that there should be a
message box
display as the code shows.

 

[AB]

So, if it's not trapping your events that means that this code:
Public Sub TrapApplicationEvents()
Set xlApplication.xlapp = Application
End Sub
hasn't been run. Actually all that matters is that this single line:

Set xlApplication.xlapp = Application

MUST be run before your trapping can start.

This is what you need:
1. have your xla with this code in ThisWorkbook module:
Private Sub Workbook_Open()
Set xlApplication.xlapp = Application
End Sub

2. save the xla (to make sure it keeps the above code). Install it
(i.e, so that it would open up when you start Excel) - the regular
routine - Tools>Add-ins etc.

3. Close your Excel (all the files) - the entire application.

4. Start Excel anew (this would open up the xla file and would make
this code fire
Private Sub Workbook_Open()
Set xlApplication.xlapp = Application
End Sub

5. Now that this code has fired your public variable (xlApplication)
and it's WithEvent variable (xlapp) has finally been created and then
it will start trapping your other workbook_open events.

Notice that there are two distinct workboo_open events in your setup -
the first is part of your xla - that will create the variable which in
turn will start trapping all the other OpenWB events (after the
variable has been created).

If it still doesn't work that means you're not doing something from
the above or doing something in different order.

 

[Subodh]

So, if it's not trapping your events that means that this code:
Public Sub TrapApplicationEvents()
      Set xlApplication.xlapp = Application
End Sub
hasn't been run. Actually all that matters is that this single line:

Set xlApplication.xlapp = Application

MUST be run before your trapping can start.

This is what you need:
1. have your xla with this code in ThisWorkbook module:
Private Sub Workbook_Open()
     Set xlApplication.xlapp = Application
End Sub

2. save the xla (to make sure it keeps the above code). Install it
(i.e, so that it would open up when you start Excel) - the regular
routine - Tools>Add-ins etc.

3. Close your Excel (all the files) - the entire application.

4. Start Excel anew (this would open up the xla file and would make
this code fire
Private Sub Workbook_Open()
     Set xlApplication.xlapp = Application
End Sub

5. Now that this code has fired your public variable (xlApplication)
and it's WithEvent variable (xlapp) has finally been created and then
it will start trapping your other workbook_open events.

Notice that there are two distinct workboo_open events in your setup -
the first is part of your xla - that will create the variable which in
turn will start trapping all the other OpenWB events (after the
variable has been created).

If it still doesn't work that means you're not doing something from
the above or doing something in different order.




- Show quoted text -

Thanks AB.
But i cannot still go.
I don't know what i missed.
I was stopped at the first step
I made addin *.xlam with the following code
and its loaded every time the excel file is opened
But it generates an error with the following code



Private Sub Workbook_Open()
Set xlApplication.xlapp = Application
End Sub
and
it also generates error with the following line added at the top

Public WithEvents xlapp As Application

and the error is as follows
Run-time error '424':
Object Required

Sorry, i think i couldn't get what u said
Plz help
Or, can u send me the excel files at the email
(e-mail address removed)
IF its not a problem to you.

 

[AB]

Ok, i think one thing got confused there (and that's (also) my fault
as I wasn't clear enough) - you have 2 choises:

1. - you either keep the public declaration with the 'New' in it (in
this case the xla open code stays as is)
Public xlApplication As New ClsAppEvents

2. - you do not declare the public variable as 'New' and then you need
to have the xla open code amended like this:
Public xlApplication As ClsAppEvents

Private Sub Workbook_Open()
Set xlApplication = New ClsAppEvents'You need to create it
somewhere
Set xlApplication.xlapp = Application
End Sub

Clarificiation: you need that 'New' somewhere as it creates the
variable - so, you can either declare it as 'New' (as per my first
post - not a good practice) or you can create it as new somewhere in
the code (Set xlApplication = New ClsAppEvents).

I hope it helps.

And don't put the 'Public WithEvents xlapp As Application ' in the
standard module as 'WithEvents' declarations are for calss modules
only.

Post back if still doesn't go.