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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Lyndon Rickards - 27 Dec 2005 00:01 GMT

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.

Reply to this Message

Dave Peterson - 27 Dec 2005 00:16 GMT

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).)

> 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
[quoted text clipped - 28 lines]
>
> TIA - Lynn.

Signature

Dave Peterson

Reply to this Message

Lyndon Rickards - 29 Dec 2005 03:26 GMT

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.

> Make sure the Workbook_activate and workbook_deactivate are both under the
> ThisWorkbook module (not under a worksheet.
[quoted text clipped - 44 lines]
>>
>>TIA - Lynn.

Reply to this Message

Dave Peterson - 29 Dec 2005 12:17 GMT

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

> Thanks again Dave, now I got it. Next question - Since the
> Activate...Deactivate subs must be in ThisWorkbook, is there a method
[quoted text clipped - 57 lines]
> >>
> >>TIA - Lynn.

Signature

Dave Peterson

Reply to this Message

lynn@debscave.com - 02 Jan 2006 16:40 GMT

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.
[quoted text clipped - 3 lines]
>
> http://www.cpearson.com/excel/vbe.htm

<--snip->

Reply to this Message