Prev: macro based on text or number
Next: What kind of workbook function should I used?
From: Phil Hibbs on 12 May 2010 12:44
If I start Excel up from the Start Menu, everything is fine. My add-
ins work with no complaints. If I start Excel by opening a
spreadsheet, though, the add-ins don't load until I invoke their menu
item, and then I get a macro security message box. Am I doing
something wrong, or is this normal behaviour?

Phil Hibbs.
From: GS on 12 May 2010 19:29
Phil Hibbs presented the following explanation :
> If I start Excel up from the Start Menu, everything is fine. My add-
> ins work with no complaints. If I start Excel by opening a
> spreadsheet, though, the add-ins don't load until I invoke their menu
> item, and then I get a macro security message box. Am I doing
> something wrong, or is this normal behaviour?
>
> Phil Hibbs.

Sounds to me like your addin is using an 'attached' custom
menus/toolbar that you built via the Excel the UI, and if so then ditch
it and go with the suggestions that follow.

If your addin may also be creating 'Permanent' menus that persist in
the UI, whether the addin is loaded or not. The fact that it doesn't
load until you click one of its menus suggests the addin has not been
properly 'installed' via Addins Manager. Is it really an addin (.xla)
or just a workbook (.xls) with macros?

If Addin creating its own menus:
First thing I suggest is to set the IsTemporary property to TRUE, and
add code to delete the menus on shutdown. Compliment that by ensuring
your addin creates new menus on startup only AFTER it deletes them in
that same procedure. This will prevent duplication if the menus already
exist in cases, for example, restart after Excel crashes (if
AutoRecovery is enabled).

It's good programming practice to ensure that whatever changes to Excel
are made by your project (at startup or during runtime) that these get
undone at shutdown.

HTH
Garry


From: Phil Hibbs on 13 May 2010 05:31
The problem appears to have gone away today. Maybe Excel had crashed
earlier in the day and was just a bit messed up.

> If Addin creating its own menus:
> First thing I suggest is to set the IsTemporary property to TRUE, and

Is that a property of the menu item?

> add code to delete the menus on shutdown. Compliment that by ensuring
> your addin creates new menus on startup only AFTER it deletes them in
> that same procedure. This will prevent duplication if the menus already
> exist in cases, for example, restart after Excel crashes (if
> AutoRecovery is enabled).

I actually started doing that consistently a couple of days ago after
I started getting duplicates!

Sub add_menu()
Call remove_menu
CommandBars("Worksheet Menu Bar") _
.Controls(MENU_NAME) _
.Controls.Add(Type:=msoControlButton) _
.Caption = MENU_ITEM
CommandBars("Worksheet Menu Bar") _
.Controls(MENU_NAME) _
.Controls(MENU_ITEM) _
.OnAction = FUNCTION_NAME
End Sub

Sub remove_menu()
Dim ctl As CommandBarControl
For Each ctl In CommandBars("Worksheet menu
bar").Controls(MENU_NAME).Controls
If ctl.Caption = MENU_ITEM Then
ctl.Delete
End If
Next ctl
End Sub

Phil Hibbs.
 | 
Pages: 1
Prev: macro based on text or number
Next: What kind of workbook function should I used?