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 wrote:
>
> 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

[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 wrote:
> 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 wrote:
>
>>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]

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




Lyndon Rickards wrote:
>
> 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 wrote:
> > 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 wrote:
> >
> >>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

[[email protected]]

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/Mai...-users/2960933

Thanks - Lynn.


Dave Peterson wrote:
> 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->