ThisDocument Macro in .xla file gives error




I have a few macros that I run when I open a workbook in Excel. They run
fine until I put them in a .xla file. There seems to be a problem
referencing the ActiveWorkbook during the Workbook_Open() macro.

I can setup custom menus fine using the .xla file. The problem occurs when
I use the ActiveWorkbook.Name function during the Workbook_Open(). I get an
"Object variable or with block variable not set" error messsage.

Any ideas? Thanks for your help.

-Lee Ann



Charles Williams

Hi Lee Ann,

If you have setup the XLA to load using the Excel Addin Manager then it will
be loaded before there is an Active workbook, so you can't get the name of
the Active workbook during the Workbook_Open event of the XLA. Even if you
could it would be something like Book1 anyway.

I suspect that what you want is an Application level event for workbook_open
that fires whenever any workbook is opened. If so see Chip Pearson's
excellent page on Application events.

The Excel Calculation Site

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question