run macro whenever any workbook opens
 

Okay, I admit it; I don't get Event Class Modules, which it sounds like I
need to solve my problem.

I have a macro that checks for the existence of a particular font in a
workbook. If that font is found, a message box instructs the user what to do.
If the font isn't found, nothing happens.

I want to run that macro any time a user opens a workbook.

I looked up "Using Events with the Application Object" in the help files,
but I can't make it work. I also had a look at Charles Pearson's site
(http://www.cpearson.com/excel/AppEvent.htm), but that looks like the code is
getting added to the "This Workbook" module, which doesn't work for my
situation (unless I'm reading it wrong, which is possible).

Any help would be appreciated.

I'm using Excel 2003 for Windows XP, Service Pack 2.

Thanks.

run macro whenever any workbook opens
 

Chips site should get you going on this... The one thing you will want to do
is to put this code into an addin so that it is active as soon as Excel is
loaded. Don't feel bad if this is not going too well this is a tough thing to
get working right... Did you download Chips workbook? Is that working OK for
you? Can you post your code so that we can point you in the right direction
and get you back on track...
--
HTH...

Jim Thomlinson


"Tony Logan" wrote:

Okay, I admit it; I don't get Event Class Modules, which it sounds like I
need to solve my problem.

I have a macro that checks for the existence of a particular font in a
workbook. If that font is found, a message box instructs the user what to do.
If the font isn't found, nothing happens.

I want to run that macro any time a user opens a workbook.

I looked up "Using Events with the Application Object" in the help files,
but I can't make it work. I also had a look at Charles Pearson's site
(http://www.cpearson.com/excel/AppEvent.htm), but that looks like the code is
getting added to the "This Workbook" module, which doesn't work for my
situation (unless I'm reading it wrong, which is possible).

Any help would be appreciated.

I'm using Excel 2003 for Windows XP, Service Pack 2.

Thanks.

run macro whenever any workbook opens
 

Public WithEvents App As Application

Private Sub App_WorkbookOpen(ByVal Wb As Workbook)
'<=== put your code here
End Sub

Private Sub Workbook_Open()
Set App = Application
End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code


--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Tony Logan" wrote in message
...
Okay, I admit it; I don't get Event Class Modules, which it sounds like I
need to solve my problem.

I have a macro that checks for the existence of a particular font in a
workbook. If that font is found, a message box instructs the user what to
do.
If the font isn't found, nothing happens.

I want to run that macro any time a user opens a workbook.

I looked up "Using Events with the Application Object" in the help files,
but I can't make it work. I also had a look at Charles Pearson's site
(http://www.cpearson.com/excel/AppEvent.htm), but that looks like the code
is
getting added to the "This Workbook" module, which doesn't work for my
situation (unless I'm reading it wrong, which is possible).

Any help would be appreciated.

I'm using Excel 2003 for Windows XP, Service Pack 2.

Thanks.

run macro whenever any workbook opens
 

Thanks for the advice, Bob. Here's what I have so far:

In the add-in I'm working with, I added a Class Module called EventClass. I
added this code to it:

Public WithEvents App As Application

Private Sub Workbook_Open()
Set App = Application
End Sub

Then, in the Modules section, I added this code:

Private Sub App_WorkbookOpen(ByVal Wb As Workbook)
MsgBox "Workbook opened."
End Sub

I saved the add-in, closed Excel, then re-opened Excel. Didn't see my
message box.

I also tried putting all the code into the EventClass module, but that
didn't work either.

Not sure what to try next.

Thanks again.



"Bob Phillips" wrote:

Public WithEvents App As Application

Private Sub App_WorkbookOpen(ByVal Wb As Workbook)
'<=== put your code here
End Sub

Private Sub Workbook_Open()
Set App = Application
End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code


--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Tony Logan" wrote in message
...
Okay, I admit it; I don't get Event Class Modules, which it sounds like I
need to solve my problem.

I have a macro that checks for the existence of a particular font in a
workbook. If that font is found, a message box instructs the user what to
do.
If the font isn't found, nothing happens.

I want to run that macro any time a user opens a workbook.

I looked up "Using Events with the Application Object" in the help files,
but I can't make it work. I also had a look at Charles Pearson's site
(http://www.cpearson.com/excel/AppEvent.htm), but that looks like the code
is
getting added to the "This Workbook" module, which doesn't work for my
situation (unless I'm reading it wrong, which is possible).

Any help would be appreciated.

I'm using Excel 2003 for Windows XP, Service Pack 2.

Thanks.

run macro whenever any workbook opens
 

One additional question: Is this code I can add to the add-in (the .xla
file)? Or am I going to need to add it to an Excel file?

If I"m understanding Chip Pearson's sample file correctly, if I want to
trigger an action that occurs every time a workbook is opened, I'll need to
have some .xls file containing my code open, too.

And so my solution seems two-fold:
1. Write code that triggers an action whenever a workbook is opened.
2. Write code that opens the doc containing the code for #1 any time Excel
starts.

Just want to make sure I'm heading down the right path.

Thanks.

run macro whenever any workbook opens
 

Tony,

This code does not go into a class module, you put it in ThisWorkbook code
module. It uses a slightly different implementation of the technique than
Chip.

And yes, it is fine in the add-in.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Tony Logan" wrote in message
...
One additional question: Is this code I can add to the add-in (the .xla
file)? Or am I going to need to add it to an Excel file?

If I"m understanding Chip Pearson's sample file correctly, if I want to
trigger an action that occurs every time a workbook is opened, I'll need
to
have some .xls file containing my code open, too.

And so my solution seems two-fold:
1. Write code that triggers an action whenever a workbook is opened.
2. Write code that opens the doc containing the code for #1 any time Excel
starts.

Just want to make sure I'm heading down the right path.

Thanks.