BeforePrint Add In

[Daniel McCollick]

Hello Everybody,
I have a custom header and footer currently in code. I want to pull this
code to a add in so I can distribute any changes, etc. When I put the code
into xla, the thisworkbook.beforeprint is no longer valid. I.e. How do I
call beforeprint to the active sheet?

Current Code:
Private Sub beforePrint(Cancel As Boolean)

ActiveSheet.PageSetup.RightHeader = "Blah Blah Blah"
ActiveSheet.PageSetup.LeftFooter = "Blah Blah Blah Blah"
ActiveSheet.PageSetup.RightFooter = "Blah Blah, Blahblah blah"
end sub

 

[Tom Ogilvy]

Instantiate Application Level Events in the Addin

http://www.cpearson.com/excel/appevent.htm
A Chip Pearson's site will give you a start point.

 

[ben]

That only applies to the workbook that the code is in. Since you never
actually print the XLA it never activates.

see the following website to see how to apply to all workbooks

http://www.cpearson.com/excel/AppEvent.htm

ben

 

[ben]

I agree. Good site . Posted to fast for me there.

 

[Daniel McCollick]

So if I add the class module(as instructed in the link) to the add in's xla
will this work, or must I add it to personal.xls?


Thanks for the help

 

[ben]

If you add it to the XLA you must load the XLA every time you run excel, or
install it as a load up xla (not sure how to do that myself), but installing
into personal.xls will run it too.

 

[Daniel McCollick]

If it is in the personal it will force a load everytime correct? If it is
only in the xla, then it will only run when the add in is used. I need it
when the add in is installed....I'll mess with it.

To get my xla to load everytime I have a reg file that loads the add in.
Something to the extent of
Windows Registry Editor Version 5.00

[HKEY_CURRENT_USER\Software\Microsoft\Office\10.0\Excel\Options]

"OPEN"="/R \"C:\\Program Files\\Microsoft
Office\\Office10\\Library\\Analysis\\ANALYS32.XLL\""
"OPEN1"="\"<location><filename>""
"OPEN2"="\"<location><filename2>""


if you search regedit for a add in name you should be able to export that
key. I am sure there is a way to programatically do this, but I do not have
the need.

Thanks Again

 

[ben]

right, personal will force load everytime, but hte xla will only run when
loaded.
--
When you lose your mind, you free your life.

If it is in the personal it will force a load everytime correct? If it is
only in the xla, then it will only run when the add in is used. I need it
when the add in is installed....I'll mess with it.

To get my xla to load everytime I have a reg file that loads the add in.
Something to the extent of
Windows Registry Editor Version 5.00

[HKEY_CURRENT_USER\Software\Microsoft\Office\10.0\Excel\Options]

"OPEN"="/R \"C:\\Program Files\\Microsoft
Office\\Office10\\Library\\Analysis\\ANALYS32.XLL\""
"OPEN1"="\"<location><filename>""
"OPEN2"="\"<location><filename2>""


if you search regedit for a add in name you should be able to export that
key. I am sure there is a way to programatically do this, but I do not have
the need.

Thanks Again


--
Dan McCollick

If you add it to the XLA you must load the XLA every time you run excel, or
install it as a load up xla (not sure how to do that myself), but installing
into personal.xls will run it too.

 

[Daniel McCollick]

This still requires you to program at the thisworkbook level?! My ultimate
goal is to have any excel spreadsheet that has this add in installed, print
the specified header/footer, from the xla. If I need to put code into the
thisworkbook of each spreadsheet, why even bother creating an xla?

 

[Tom Ogilvy]

If I need to put code into the

thisworkbook of each spreadsheet, why even bother creating an xla?

You don't. That is the point of using application level events.

I am not sure why you are having an extended discussion with Ben then
posting below my post as if my suggestion was something different.. We both
suggested the same thing. Just continue your discussion with Ben.

--
Regards,
Tom Ogilvy

 

[Daniel McCollick]

I tried modifying the example workbook provided by the link above. When I
open the zip file the application level events work fine, but if i try to add
an event, or even just change what the msg box displays, it no longer works?
--
Dan McCollick

right, personal will force load everytime, but hte xla will only run when
loaded.
--
When you lose your mind, you free your life.

If it is in the personal it will force a load everytime correct? If it is
only in the xla, then it will only run when the add in is used. I need it
when the add in is installed....I'll mess with it.

To get my xla to load everytime I have a reg file that loads the add in.
Something to the extent of
Windows Registry Editor Version 5.00

[HKEY_CURRENT_USER\Software\Microsoft\Office\10.0\Excel\Options]

"OPEN"="/R \"C:\\Program Files\\Microsoft
Office\\Office10\\Library\\Analysis\\ANALYS32.XLL\""
"OPEN1"="\"<location><filename>""
"OPEN2"="\"<location><filename2>""


if you search regedit for a add in name you should be able to export that
key. I am sure there is a way to programatically do this, but I do not have
the need.

Thanks Again


--
Dan McCollick

If you add it to the XLA you must load the XLA every time you run excel, or
install it as a load up xla (not sure how to do that myself), but installing
into personal.xls will run it too.
--
When you lose your mind, you free your life.


:

So if I add the class module(as instructed in the link) to the add in's xla
will this work, or must I add it to personal.xls?


Thanks for the help
--
Dan McCollick


:

That only applies to the workbook that the code is in. Since you never
actually print the XLA it never activates.

see the following website to see how to apply to all workbooks

http://www.cpearson.com/excel/AppEvent.htm

ben

--
When you lose your mind, you free your life.


:

Hello Everybody,
I have a custom header and footer currently in code. I want to pull this
code to a add in so I can distribute any changes, etc. When I put the code
into xla, the thisworkbook.beforeprint is no longer valid. I.e. How do I
call beforeprint to the active sheet?

Current Code:
Private Sub beforePrint(Cancel As Boolean)

ActiveSheet.PageSetup.RightHeader = "Blah Blah Blah"
ActiveSheet.PageSetup.LeftFooter = "Blah Blah Blah Blah"
ActiveSheet.PageSetup.RightFooter = "Blah Blah, Blahblah blah"
end sub