Add-in must be 'run' one time to stick? How to auto invoke?

[Lyndon Rickards]

Thanks to Dave Peterson's recent sample and tips to get started I have
gotten past the notion of a script-holding template file and
gotten all the functionality I need into a .xla addin menu

The functions in the addin are made available thru a toolbar
menu created like...

Set cbcCutomMenu = _
cbMainMenuBar.Controls.Add(Type:=msoControlPopup, _
Before:=iHelpMenu)

'(5)Give the control a caption
cbcCutomMenu.Caption = "M&y Menu"

....

and added by these subs in the 'View Code' thingy:

Private Sub Workbook_Activate()
Run "AddMenus"
End Sub

Private Sub Workbook_Deactivate()
Run "DeleteMenu"
End Sub

Now, the sources I found indicate the menu should appear
once the Add-in has been loaded. It doesn't....I seem forced to
Run the AddMenus sub one time. After that, the menu is available
in subsequent Excel sessions. Is there a way to avoid that one-time run?

TIA - Lynn.

 

[Dave Peterson]

Make sure the Workbook_activate and workbook_deactivate are both under the
ThisWorkbook module (not under a worksheet.

And since your Addmenus and DeleteMenu routines are in General modules of the
same workbook's project, you can just call then (instead of using Run).

Private Sub Workbook_Activate()
Call AddMenus
End Sub

Private Sub Workbook_Deactivate()
Call DeleteMenu
End Sub

(The Call statement isn't required--but I like it (other's don't).)

 

[Lyndon Rickards]

Thanks again Dave, now I got it. Next question - Since the
Activate...Deactivate subs must be in ThisWorkbook, is there a method
available to insert them programmaticaly.

Guess I'm looking for an OLE method here, to do the job from outside of
Excel.

It seems the M$ method is M3 on the excel icon next to File menu, See
Code' and type in the subs. I really don't want to have my users do that
if at all avoidable...

- Lynn.

 

[Dave Peterson]

I would think that it would be much easier to create the code while you're
creating the workbook.

But Chip Pearson has some notes that will show you how to write code that writes
code:

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

 

[lynn]

Final thanks to Dave for the guidance and references. The Chip Pearson
page finally set me right, and FWIW I wound up using plain 'ole perl
to achieve the design spec.

To avoid irrelevant xposting, working example is over on..

http://aspn.activestate.com/ASPN/Mail/Message/perl-win32-users/2960933

Thanks - Lynn.

I would think that it would be much easier to create the code while you're
creating the workbook.

But Chip Pearson has some notes that will show you how to write code that writes
code:

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

<--snip->