PDA

View Full Version : Solved: VBA beginner: Code in the Workbook_Open() does not get called



Chamaco
06-05-2007, 04:12 AM
Hi all !

I am getting started with VBA programming for excel. I do have some experience in mathematical C/C++ and Fortran programming, but this time I am running into trouble.

I am following O'Reilly's textbook on VBA programming (Writing excel Macros in VBA, 2nd edition) and my version of MS Excel is Excel 2003 SP2

I followed all the tutorial instruction for building the Add-In given in an example (the first book example is simply the dynamic creation of a new Menu with a Menuitem that invokes a msgbox, followed by its deletion at the closure of the workbook)

However, when I load the add-ins, the code in the Private Sub Worksheet_Open event trap does not get called when a new workbook is actually opened (and neither the code in the Private Sub Worksheet_BeforeClose is called at the closure of the workbook)
The code itself works fine because if I just run the subroutine from the debugger, it does perform the job.

The same thing happen for the Worksheet_BeforeClose routine


I tried to simplify the code in the Worksheet_Open routine by commenting out all the instructions and leaving only a "Beep" insruction, but nothing happens, e.g. if I run it from the debugger, it is just fine, but If I save the AddIn and then open/close MS EXcel Worksheet, the subroutine is never invoked (I suspect the Workbook_Open and Workbook_BeforeClose events are never caught)

Does anyone have some suggestion about what I am missing ?

Thx all !!!

Bob Phillips
06-05-2007, 05:17 AM
However, when I load the add-ins, the code in the Private Sub Worksheet_Open event trap does not get called when a new workbook is actually opened (and neither the code in the Private Sub Worksheet_BeforeClose is called at the closure of the workbook)

The code must go in the ThisWorkbook code module, not a standard code module.

johnske
06-05-2007, 05:23 AM
The way I read your post - you have this code in an add-in and expect it to work each time you open another workbook. Is that correct?

Chamaco
06-05-2007, 06:19 AM
The code must go in the ThisWorkbook code module, not a standard code module.

Thank you very much, but there is something I am missing, so I review below the steps I follow:

1) I open a worksheet, let's call it foo.xls


2) I invoke the VBA editor: within the VBA editor, I add 2 modules, fooMain and fooMenus: foomain handles the action associated to my menuitem; fooMenus handles the following
-the creation of the new Menu and new NMenuitem when a Workbook_Open event is fired;
-the deletion of the menu and menuitem when a Workbook_BeforeClose event is fired;
-calls the appropriate Sub (defined in fooMain) when the menuitem is selected

Both fooMain and fooMenus code modules are within the foo.xls workbook

3) In the Tools/Properties menu of the VBA editor, I name "fooAddIn" the addin I will create

4) I compile the code and close the VBA editor


5) Back into Excel spreadsheet mode,
-First I save the changes into the "foo.xls" file.
-Then I "Save as" the document as "FooAddIn.xla", as an add in, in a predefined folder (say "foo" folder)

6) I close all excel document and reopen excel

7) In the Tools/Addin menu, I browse to the foo folder, select the fooAddin, and make sure that the associated checkbox is enabled

8) NOW I TRY TO OPEN/CLOSE Excel Workbooks (including the "foo.xls" original file), to see whether the creation/deletion of the menus works, but nothing happens....

Chamaco
06-05-2007, 06:22 AM
The way I read your post - you have this code in an add-in and expect it to work each time you open another workbook. Is that correct?

Yes, it is correct: ONCE THE ADD-IN HAS BEEN LOADED and the appropriate checkbox (from the dialog box appearing after the Tools/Addins menu called from Excel - in SPREADSHEET mode) is elected, the tutorial states that this shoud work.

Indeed, it does not work, neither in the original workbook nor in new workbooks

johnske
06-05-2007, 06:31 AM
Don't know if your post count will enable it yet, but it'd be best if you use Manage Attachments to post a copy of your addin for us to look at...

Chamaco
06-05-2007, 07:03 AM
Ty verymuch John,
Here is the file that causes me headache, let's see if the attachment works...:think:

lucas
06-05-2007, 07:28 AM
Try it now...
As John and Bob pointed out...your workbook open and close calls must be in the thisworkbook module....not in a standard module..

just above your two modules in the project explorer on the left of the vbe look for the thisworkbook module.

lucas
06-05-2007, 07:38 AM
This shows your thisworkbook module and the code I moved to it.

Chamaco
06-05-2007, 07:40 AM
It did work ! Thank you and sorry if the question was so basic (and myself so dumb I did not undestand the first correct reply):doh:

lucas
06-05-2007, 07:43 AM
No problem....be sure to mark your thread solved using the thread tools at the top of the page.