|
|
|
Tips27 : CommandBar Menu & Add-in Tab with add-in of Excel2013/2016. 
( Because I who am poor at English am translating into English while using translation software,
there may be an odd expression. The mistranslation
revises it sequentially. )
| (Jpn. 1st Edition : 6 Dec. 2016 ) | |
| (Jpn. Last Edition : 8 May 2018 ) | |
| (Eng. 1st Translation: 21 Dec. 2016 ) | |
| (Eng. Last Translation: 8 May 2018 ) |
This is a procedure manual for trouble-free operation of CommandBar menu ( Add-in Tab )[ AddinBox Home (Japanese) ] [ English Home ]
created by Add-in book in SDI ( Single Documents Interface ) environment of Excel2013 / 2016.
Please note that in the case of "Save type menu ( Temporay: = False )", differences in execution
procedure of add-in registration / cancellation cause troubles that are not reflected as expected.
The distinction between [ Temporary: = False or True ] can be judged by whether or not the
add-in tab immediately appears at the time of registering the add-in. (see Sec.5 / Sec.6)
1. CommandBar menu and Ribbon UI
2. How to create a CommandBar menu in Add-in book
3. MDI and SDI
4. Macro for verification
5. Temporary type menu ( Temporary: = True ) and Procedure for Add-in registration / cancellation
6. Save type menu ( Temporary: = False ) and Procedure for Add-in registration / cancellation
6-1. Procedure in which Add-in menu is created / deleted as expected
6-2. Procedure in which Add-in menu is not created / deleted as expected
7. Bug ? or Specification ?
8. Effect of "temporary add-in cancellation process" on menu of [ Temporary: = True ] type ( 8 May 2018 addition )
[ Link to here ]
1. CommandBar menu and Ribbon UI
The menu by CommandBar is used in Excel 2003 and earlier. In Excel 2007 and later, menus
created by CommandBar are displayed in "Add-in" tab (The CommandBar menu created in a
normal book that is not an add-in book is also accommodated in the "add-in" tab.)
There is a temporary display problem with the ribbon display of the menu (accommodated in the
add-in tab) added on CommandBars ("WorkSheet Menu Bar").
[ KB 2648925 ] https://support.microsoft.com/ja-jp/kb/2648925 ( English edition is not released. )
If you select the Add-Ins tab of the ribbon while the graph object is active,
commands on [Add-in] - [Menu command] may not be displayed.
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
While the graph object is active (selected state), the menu temporarily disappears.
If you activate another object such as a sheet, the menu will be displayed again.
If only the hidden menu is registered in the add-in tab, the add-in tab itself also temporarily disappears.
It merely temporarily disappears, and the menu itself is not deleted.
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
If you create it under the menu of "Tools" etc. (not on "WorkSheet Menu Bar"),
you can avoid this problem.
[ Link to here ]
2. How to create a CommandBar menu in Add-in book
( 1 ) Temporary type menu ( Temporary := True )
By writing a macro in Workbook_Open event procedure, it is created every time Excel is started.
This menu will be deleted automatically at the end of Excel.
Since it is automatic deletion at the end of Excel, even if you cancel add-ins, the menu will remain
during that excel process. Therefore, it is necessary to delete the menu in Workbook_AddinUninstall
or Workbook_BeforeClose event procedure.
( Note ) In the CommandBar menu, duplicate names can be registered. Therefore, you should make sure "existence of
menu name to be created" beforehand (part of 'existence confirmation' is omitted in the following macro).
--- ThisWorkbook module ---
Private Sub Workbook_Open()
Dim MyCB As CommandBar
Dim MyCBTool As CommandBarControl
Dim MyCBMenu As CommandBarControl
On Error Resume Next
Set MyCB = Application.CommandBars("WorkSheet Menu Bar")
Set MyCBMenu = MyCB.Controls.Add(Type:=msoControlPopup, Temporary:=True)
MyCBMenu.Caption = "Menu1"
Set MyCBTool = MyCB.FindControl(ID:=30007) '[Tool (&T)] ID=30007
Set MyCBMenu = MyCBTool.Controls.Add(Type:=msoControlPopup, Temporary:=True)
MyCBMenu.Caption = "Menu2"
End Sub
Private Sub Workbook_AddinUninstall()
' or Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim MyCB As CommandBar
Dim MyCBTool As CommandBarControl
On Error Resume Next
Set MyCB = Application.CommandBars("WorkSheet Menu Bar")
MyCB.Controls("Menu1").Delete
Set MyCBTool = MyCB.FindControl(ID:=30007) '[Tool (&T)] ID=30007
MyCBTool.Controls("Menu2").Delete
End Sub
( 2 ) Save type menu ( Temporary := False )
Menus of this type are not automatically deleted.
You create menus of this type with Workbook_AddinInstall and delete it with Workbook_AddinUninstall.
In other words, it is executed only when add-in registration / add-in cancellation (check box of add-in
dialog is turned on / off).
Menus of this type are recorded as a permanent menu in the following Excel configuration file.
C:\Users\USER ACCOUNT\AppData\Roaming\Microsoft\Excel\Excel15.xlb( in Windows10 )
Excel2013/2016/2019 ... Excel15.xlb , Excel2010 ... Excel14.xlb
Excel2007 ... Excel12.xlb , Excel2003 ... Excel11.xlb
( Note ) In the CommandBar menu, duplicate names can be registered. Therefore, you should make sure "existence of
menu name to be created" beforehand (part of 'existence confirmation' is omitted in the following macro).
--- ThisWorkbook module ---
Private Sub Workbook_AddinInstall()
Dim MyCB As CommandBar
Dim MyCBTool As CommandBarControl
Dim MyCBMenu As CommandBarControl
On Error Resume Next
Set MyCB = Application.CommandBars("WorkSheet Menu Bar")
Set MyCBMenu = MyCB.Controls.Add(Type:=msoControlPopup, Temporary:=False)
MyCBMenu.Caption = "Menu3"
Set MyCBTool = MyCB.FindControl(ID:=30007) '[Tool (&T)] ID=30007
Set MyCBMenu = MyCBTool.Controls.Add(Type:=msoControlPopup, Temporary:=False)
MyCBMenu.Caption = "Menu4"
End Sub
Private Sub Workbook_AddinUninstall()
Dim MyCB As CommandBar
Dim MyCBTool As CommandBarControl
On Error Resume Next
Set MyCB = Application.CommandBars("WorkSheet Menu Bar")
MyCB.Controls("Menu3").Delete
Set MyCBTool = MyCB.FindControl(ID:=30007) '[Tool (&T)] ID=30007
MyCBTool.Controls("Menu4").Delete
End Sub
[ Link to here ]
3. MDI and SDI
( 1 ) The window of Excel2007/2010 is MDI (Multi Document Interface).
The MDI contains multiple child windows that are nested within a single parent window and
the menu or tool bar is only in the parent window and each book uses a common ribbon UI.
Therefore, even if you open multiple workbooks or restart Excel, you can always use menu
commands from the "Add-in" tab.
( 2 ) From Excel2013, the window has changed to SDI (Single Document Interface).
In SDI, each Excel window can contain only one workbook, and each has its own ribbon UI.
When you open a new workbook, it will be displayed in another Excel window, even though
it is the same Excel instance.
Therefore, when you open multiple workbooks or restart Excel, the "Add-in" tab does not
appear in the window of each workbook (or the menu command in the add-in tab is not
displayed) in some cases.
( 3 ) Effect of SDI conversion (Excel 2013 or later) on Add-in Tab.
( a ) The CommandBar menu created with the macro of the normal workbook is effective only
in the window of this workbook. The menu appears in the window of the workbook in Add-in
tab, but when you switch to another workbook's window, the menu disappears.
(If there is no other valid menu, Add-in tab itself will also disappear.)
( b ) The CommandBar menu created with the macro of the add-in workbook is effective in all
workbook windows (The method of creating the menu is as described above).
However, depending on the execution procedure of add-in registration, Add-in tab may
not appear. This is "Problem on the CommandBar menu in SDI".
[ Link to here ]
4. Macro for verification
SDI_AddinTab_Test_E.zip ( 54 kb )
+-- MenuTestOpen.xlam
+-- MenuTestAddinInst.xlam
+-- MenuTestCheckDelete.xlsm
( 1 ) MenuTestOpen.xlam
It is an add-in workbook that creates a Temporary type CommandBar menu (Temporary: = True).
It creates a menu with Workbook_Open and deletes it with Workbook_BeforeClose.
( a ) WkshOpenTempT ..... Menu to create in "WorkSheet Menu Bar"
( b ) ToolOpenTempT ..... Menu to be created under "Tools" menu
A macro to check the menu state ( MenuCheck4Open ) is included.
( 2 ) MenuTestAddinInst.xlam
It is an add-in workbook that creates a Save type CommandBar menu (Temporary: = False).
It creates a menu with Workbook_AddinInstall and deletes it with Workbook_AddinUninstall.
( a ) WkshAddinTempF ..... Menu to create in "WorkSheet Menu Bar"
( b ) ToolAddinTempF ..... Menu to be created under "Tools" menu
A macro to check the menu state ( MenuCheck4AddinInst ) is included.
( 3 ) MenuTestCheckDelete.xlsm
It is a macro to check the menu state of both ( 1 ) and ( 2 ).
[ Link to here ]
5. Temporary type menu ( Temporary: = True ) and Procedure for Add-in
registration / cancellation ( This section is targeted for Excel 2013 and later. )
The distinction between [ Temporary: = False or True ] can be judged by whether or not the add-in tab
immediately appears at the time of registering the add-in. (see Sec.5 / Sec.6)
This type of add-in menu appears by newly creating by add-in workbook every Excel startup.
Since the add-in workbook is opened prior to the normal workbook at Excel startup, the add-in
menus are also created before the normal workbook is opened. As a result, add-in menus are
enabled for all workbooks that will be opened after that.
In addition, this type of add-in menu has the problem that it is not immediately reflected on
the ribbon (add-in tab) during the Excel process that registered / cancelled the add-in.
This type of add-in menu is always reflected in all workbook windows after restarting Excel.
( There are exceptions depending on the contents of the macro )
[ Temporary := True ]
Case of restarting immediately after add-in registration / cancellation
( 1 ) Start Excel ( Open Book1 ) ( 2 ) Add-in registration in Book1 Menu disappears ( 3 ) Exit Excel in Book1 ( 4 ) Restart Excel ( Open Book1 ) Menu appears ( 5 ) Open Book2 additionally Menu appears in Book2 ( 6 ) Start Excel ( Open Book1 ) Menu appears ( 7 ) Add-in cancellation in Book1 Menu remains ( 8 ) Exit Excel in Book1 ( 9 ) Restart Excel ( Open Book1 ) Menu disappears
[ Temporary := True ]
Case of not restarting immediately after add-in registration / cancellation
( 1 ) Start Excel ( Open Book1 ) ( 2 ) Add-in registration in Book1 Menu disappears ( 3 ) Open Book2 additionally Menu disappears in Book2 ( 4 ) Close Book1 & Book2 ( Empty Excel window ) Menu appears ( 5 ) Open Book3 additionally Menu appears in Book3 ( 6 ) Exit Excel in Book3 ( 7 ) Restart Excel ( Open Book1 ) Menu appears ( 8 ) Open Book2 additionally Menu appears in Book2 ( 9 ) Start Excel ( Open Book1 ) Menu appears ( 10 ) Add-in cancellation in Book1 Menu remains ( 11 ) Open Book2 additionally Menu remains in Book2 ( 12 ) Close Book1 & Book2 ( Empty Excel window ) Menu remains ( 13 ) Open Book3 additionally Menu remains in Book3 ( 14 ) Exit Excel in Book3 ( 15 ) Restart Excel ( Open Book1 ) Menu disappears
[ Link to here ]
6. Save type menu ( Temporary: = False ) and Procedure for Add-in
registration / cancellation ( This section is targeted for Excel 2013 and later. )
The distinction between [ Temporary: = False or True ] can be judged by whether or not the add-in tab
immediately appears at the time of registering the add-in. (see Sec.5 / Sec.6)
This type of add-in menu is recorded in the Excel configuration file ( Excel15.xlb ).
By doing this, the add-in menu will appear even the next time Excel starts up.
However, menu information may not be recorded in Excel 15.xlb depending on the
operation procedure for registering / cancelling the add-in. As a result, the add-in menu
may not be appeared / disappeared as expected.
( Note ) In the following verification, I actually confirmed the update status of Excel15.xlb
(whether updating is done or not) in the editor.
[ Link to here ]
[ Temporary := False ] Add-in Registration
Operating Procedure in which Add-in Menu appears as expected.
-- Register Case 1 --
( It is only this procedure that menu will appear as expected in add-in registration )( 1 ) Start Excel ( Open Book1 ) ( 2 ) Add-in registration in Book1 Menu appears ( 3 ) Open Book2 additionally ( Required ) Menu appears in Book2 ( 4 ) First, close Book1 that add-in was
registered ( Required )Menu remains in Book2 ( 5 ) Next, exit Excel in Book2 that menu
is appearing ( Required )Menu data is recorded in Excel15.xlb ( 6 ) Restart Excel ( Open Book1 ) Menu appears ( 7 ) Open Book2 additionally Menu appears in Book2 ( 8 ) Close Book1 / Book2 ( Empty Excel window ) Menu appears
[ Temporary := False ] Add-in Cancellation
Operating Procedure in which Add-in Menu disappears as expected.
-- Cancel Case 1 -- ( 1 ) Start Excel ( Open Book1 ) Menu appears ( 2 ) Add-in cancellation in Book1 Menu disappears ( 3 ) Exit Excel in Book1 Menu data is deleted from Excel15.xlb ( 4 ) Restart Excel ( Open Book1 ) Menu disappears -- Cancel Case 2 -- ( 1 ) Start Excel ( Open Book1 ) Menu appears ( 2 ) Add-in cancellation in Book1 Menu disappears ( 3 ) Open Book2 additionally Menu disappears in Book2 ( 4 ) Close Book1 Menu disappears in Book2 ( 5 ) Exit Excel in Book2 Menu data is deleted from Excel15.xlb ( 6 ) Restart Excel ( Open Book1 ) Menu disappears -- Cancel Case 3 -- ( 1 ) Start Excel ( Open Book1 ) Menu appears ( 2 ) Open Book2 additionally Menu appears in Book2 ( 3 ) Add-in cancellation in Book2 Menu disappears in Book2
/ Menu remains in Book1( 4 ) First, close Book1 that menu is appearing Menu disappears in Book2 ( 5 ) Next, Exit Excel in Book2 Menu data is deleted from Excel15.xlb ( 6 ) Restart Excel ( Open Book1 ) Menu disappears
= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
[ Link to here ]
[ Temporary := False ] Add-in Registration
Operating Procedure in which Add-in Menu does not appear as expected.
-- Register Case 2 -- ( 1 ) Start Excel ( Open Book1 ) ( 2 ) Add-in registration in Book1 Menu appears ( 3 ) Exit Excel in Book1 Menu data is not recorded in Excel15.xlb ( 4 ) Restart Excel ( Open Book1 ) Menu disappears -- Register Case 3 -- ( 1 ) Start Excel ( Open Book1 ) ( 2 ) Add-in registration in Book1 Menu appears ( 3 ) Open Book2 additionally Menu appears in Book2 ( 4 ) Close Book2 Menu remains in Book1 ( 5 ) Exit Excel in Book1 Menu data is not recorded in Excel15.xlb ( 6 ) Restart Excel ( Open Book1 ) Menu disappears -- Register Case 4 -- ( 1 ) Start Excel ( Open Book1 ) ( 2 ) Add-in registration in Book1 Menu appears ( 3 ) Close Book1 ( Empty Excel window ) Menu disappears ( 4 ) Open Book2 additionally Menu disappears in Book2 ( 5 ) Exit Excel in Book2 Menu data is not recorded in Excel15.xlb ( 6 ) Restart Excel ( Open Book1 ) Menu disappears -- Register Case 5 -- ( 1 ) Start Excel ( Open Book1 ) ( 2 ) Add-in registration in Book1 Menu appears ( 3 ) Open Book2 additionally Menu appears in Book2 ( 4 ) Close Book1 Menu remains in Book2 ( 5 ) Close Book2 ( Empty Excel window ) Menu disappears ( 6 ) Open Book3 additionally Menu disappears in Book3 ( 7 ) Exit Excel in Book3 Menu data is not recorded in Excel15.xlb ( 8 ) Restart Excel ( Open Book1 ) Menu disappears -- Register Case 6 -- ( 1 ) Start Excel ( Open Book1 ) ( 2 ) Open Book2 additionally ( 3 ) Add-in registration in Book2 Menu appears in Book2
/ Menu disappears in Book1( 4 ) Close Book1 Menu remains in Book2 ( 5 ) Exit Excel in Book2 Menu data is not recorded in Excel15.xlb ( 6 ) Restart Excel ( Open Book1 ) Menu disappears -- Register Case 7 -- ( 1 ) Start Excel ( Open Book1 ) ( 2 ) Open Book2 additionally ( 3 ) Add-in registration in Book2 Menu appears in Book2
/ Menu disappears in Book1( 4 ) Close Book2 Menu disappears in Book1 ( 5 ) Exit Excel in Book1 Menu data is not recorded in Excel15.xlb ( 6 ) Restart Excel ( Open Book1 ) Menu disappears -- Register Case 8 -- ( 1 ) Start Excel ( Open Book1 ) ( 2 ) Open Book2 additionally ( 3 ) Add-in registration in Book2 Menu appears in Book2
/ Menu disappears in Book1( 4 ) Open Book3 additionally Menu appears in Book2 & Book3
/ Menu disappears in Book1( 5 ) Close Book2 Menu remains in Book3
/ Menu disappears in Book1( 6 ) Exit Excel with Book1 & Book3 closed all at once Menu data is not recorded in Excel15.xlb ( 7 ) Restart Excel ( Open Book1 ) Menu disappears If you leave Book3 to the end, menu data will be recorded ( same as Register:Case 1 ) ( 5 ) Close Book2 Menu remains in Book3
/ Menu disappears in Book1( 6-1 ) Close Book1 Menu remains in Book3 ( 6-2 ) Exit Excel in Book3 that menu is appearing Menu data is recorded in Excel15.xlb ( 7 ) Restart Excel ( Open Book1 ) Menu appears
[ Temporary := False ] Add-in Cancellation
Operating Procedure in which Add-in Menu does not disappear as expected.
-- Cancel Case 4 -- ( 1 ) Start Excel ( Open Book1 ) Menu appears ( 2 ) Open Book2 additionally Menu appears in Book2 ( 3 ) Add-in cancellation in Book2 Menu disappears in Book2
/ Menu remains in Book1( 4 ) Open Book3 additionally Menu disappears in Book2 & Book3
/ Menu remains in Book1( 5 ) Close Book2 & Book3 Menu remains in Book1 ( 6 ) Exit Excel in Book1 Menu data is not deleted from Excel15.xlb ( 7 ) Restart Excel ( Open Book1 ) Menu remains
( The state of Add-in is canceled )( 8 ) Add-in registration in Book1 Menu is registered with the same
name ( Duplicate )-- Cancel Case 5 -- ( 1 ) Start Excel ( Open Book1 ) Menu appears ( 2 ) Open Book2 additionally Menu appears in Book2 ( 3 ) Add-in cancellation in Book2 Menu disappears in Book2
/ Menu remains in Book1( 4 ) Close Book2 Menu remains in Book1 ( 5 ) Open Book3 additionally Menu appears in Book1 & Book3 ( 6 ) Exit Excel with Book1 & Book3 closed all at once Menu data is not deleted from Excel15.xlb ( 7 ) Restart Excel ( Open Book1 ) Menu remains
( The state of Add-in is canceled )( 8 ) Add-in registration in Book1 Menu is registered with the same
name ( Duplicate )
( Note )
The problem of "being registered duplicately with the same name" can be avoided
by setting the macro on the add-in side to "check the existence of the menu before
creating the CommandBar menu".
[ Link to here ]
7. Bug ? or Specification ?
The point that it is not reflected on the ribbon immediately after registering the add-in is described
in the following document, so I think that it will be "specification".
[ Programming for the Single Document Interface in Excel ]
https://msdn.microsoft.com/en-us/library/office/dn251093.aspx
Solutions for SDI Issues
Toolbars added to Excel workbooks via command bar code and XLA files
will not be displayed until after you close and reopen the workbook.
[ KB2761240 ]
Command bars of Excel add-ins are not displayed or removed automatically
in Excel 2013 or later when you load or unload the add-ins
https://support.microsoft.com/en-us/kb/2761240
Workaround
To work around issue 1, close all open workbooks, and then restart Excel.
However, I think that the following behavior is a bug.
Without this problem, the save type menu ( Temporary := False ) should always be reflected in the
The menu is not appeared ( or disappeared ) on the add-in tab
due to "Update content is not recorded" in the Excel configuration
file ( Excel15.xlb ).
add-in tab by simply restarting Excel after updating the CommandBar menu.
However, even if it is "Bug", there is no possibility that this problem will be fixed (CommandBar
is already treated as "old function"). To users using add-ins, please instruct them to perform
add-in registration in the above procedure.
[ Link to here ]
WDEffect of "temporary add-in cancellation process"
on menu of [Temporary: = True] type ( 8 May 2018 addition )
( This section is targeted for Excel 2013 and later. )
In Section 5 , I explained as follows for the menu of [Temporary: = True] type.
This type of add-in menu will always be reflected
in all workbook windows when restarting Excel.
However, in the additional survey, it turned out that there are the following cases
which do not result in the above result.
" In the add-in workbook, temporary cancellation processing of
the add-in (IsAddin = False / True) is also performed together
with the menu creation processing. "
There are the following 4 patterns for the two processes (add-in cancellation
and menu creation), depending on the context of the execution.
-- [Nothing] type -- There is no add-in cancellation processing
Private Sub Workbook_Open()
[ CommandBar menu creation process ]
End Sub
-- [Inside] type -- Menu creation inside add-in cancellation processing
Private Sub Workbook_Open()
If (ThisWorkbook.IsAddin = True) Then
Application.ScreenUpdating = False
ThisWorkbook.IsAddin = False
[ MacroOptions etc. ]
[ CommandBar menu creation process ]
ThisWorkbook.IsAddin = True
ThisWorkbook.Saved = True
Application.ScreenUpdating = True
End If
End Sub
-- [After] type -- Menu creation after add-in cancellation processing
Private Sub Workbook_Open()
If (ThisWorkbook.IsAddin = True) Then
Application.ScreenUpdating = False
ThisWorkbook.IsAddin = False
[ MacroOptions etc. ]
ThisWorkbook.IsAddin = True
ThisWorkbook.Saved = True
Application.ScreenUpdating = True
End If
[ CommandBar menu creation process ]
End Sub
-- [Before] type -- Menu creation before add-in cancellation processing
Private Sub Workbook_Open()
[ CommandBar menu creation process ]
If (ThisWorkbook.IsAddin = True) Then
Application.ScreenUpdating = False
ThisWorkbook.IsAddin = False
[ MacroOptions etc. ]
ThisWorkbook.IsAddin = True
ThisWorkbook.Saved = True
Application.ScreenUpdating = True
End If
End Sub
--- Macro for verification ---
SDI_AddinTab_Test2E.zip ( 99KB )
I
+-- MenuTestOpen1_Nothing.xlam
+-- MenuTestOpen2_Inside.xlam
+-- MenuTestOpen3_After.xlam
+-- MenuTestOpen4_Before.xlam
I
+-- MenuTestOpen0_FirstDummy.xlam
+-- MenuTestOpen9_LastDummy.xlam
I
+-- MenuAliveCheck.xlsm
Menu name created with the above add-in : [ WkshOpenTempT1 and ToolOpenTempT1 ]
The last number will be 1 to 4.
Open0_FirstDummy and Open9_LastDummy are dummies for setting order of add-in execution.
MenuAliveCheck.xlsm is a macro that checks the existence of the menu object.
Next, there are the following three patterns for the situation
where the menu creation add-in is registered as add-in.
( a ) Only the menu creation add-in is registered as add-in.
( b ) Multiple add-ins including add-in for menu creation are registered as add-in.
( b-1 ) The menu creation add-in is the first in order of add-in execution at Excel startup.
( b-2 ) The menu creation add-in is second and later in order of add-in execution at Excel startup.
There are the following rules regarding the order of add-in execution at Excel startup.
( 1 ) If you check multiple add-ins at the same time in the add-in dialog,
those add-ins will be executed in the "display order of add-in dialogs".
( 2 ) If you open the add-in dialog multiple times during the same excel process and check
several add-ins each time, those add-ins will be executed in the "display order of
add-in dialogs".
( 3 ) If you check add-ins for each of several excel processes, those add-ins will be
executed in "Order checked regardless of display order of add-in dialog".
"Excel Launch .... Check XYZ.xlam .... Excel Restart .... Check ABC.xlam .... Excel Restart"
The order executed in this case is XYZ.xlam >> ABC.xlam.
( ABC.xlam >> XYZ.xlam in display order of add-in dialog )
When I tested with these 4 patterns x 3 patterns, the following results were obtained.
( a ) ( b-1 ) ( b-2 ) [ Nothing ] Menu is no displayed when registering an add-in.
Menu is always displayed after Excel restart.[ Inside ] Menu is no displayed when registering an add-in.
Menu is always displayed after Excel restart.[ After ] Menu is an immediate displayed even when registering an add-in.
Menu is always displayed after Excel restart.[ Before ] Menu is no displayed
when registering an add-in.
Menu is always displayed
after Excel restart.Menu is no displayed
when registering an add-in.
Menu is not displayed at all even
after Excel restart.
What pattern should we adopt when creating an add-in workbook with CommandBar menu?
First of all, if there is no processing requiring add-in cancellation such as MacroOptions,
there is no problem at all. It is OK with the recognition that "It will always be reflected
if you restart Excel."
Next, when there is a process requiring add-in cancellation, which of the remaining
3 patterns ( Inside, After, Before ) should be adopted.
It is clear to everyone that [ Before ] is useless without thinking.
When comparing remaining [ Inside ] and [ After ], the menu will be displayed immediately
when registering the add-in, so [ After ] seems to be more advantageous.
However, if you compare the whole with the exception of the case of [ Before / b-2 ],
[ Menu is no displayed when registering an add-in / Menu is always displayed after
Excel restart ] is the standard of the menu of [ Temporary = True ] type.
If you take a view that [ After ] is an exceptional behavior, it is now such an advantageous
behavior, but this behavior collapses when some future function modifications are made ,
The possibility of zero will not be.
--- As Conclusion ---
For the above reasons, if there is a process requiring add-in cancellation such as MacroOptions,
you should program it with the [ Inside ] pattern to create the CommandBar menu.
Considering during macro development ( xlsm ), it is good to do as follows.
Private Sub Workbook_Open()
If (ThisWorkbook.IsAddin = True) Then
Application.ScreenUpdating = False
ThisWorkbook.IsAddin = False
Call MacroOptions_etc()
Call Create_CommandBar_Menu()
ThisWorkbook.IsAddin = True
ThisWorkbook.Saved = True
Application.ScreenUpdating = True
Else
Call MacroOptions_etc()
Call Create_CommandBar_Menu()
End If
End Sub
- - - - - - - - - - - - - - - - - -
Private Sub MacroOptions_etc()
End Sub
- - - - - - - - - - - - - - - - - -
Private Sub Create_CommandBar_Menu()
End Sub
( Notes )
Ignore the problem described in this section and look at only this macro.
Ah ! "Add-in cancellation" is not necessary for the menu creation.
We can put the menu creation outside the If statement !
You will soon notice this.
However, it will cause a bug ( the menu will not be displayed due to the [ Before ] type ).
When you modify the macro in the future, you need to be careful not to cause problems by
thinking like this. I recommend that you write down the reasons for doing this as comments.
Actually, I thought like this, and as a result I got a problem. I write commentary on this
section from that experience.
Finally, when tracking the progress of the created menu object, it seems that
the situation is that "the menu object itself is erased" instead of "there is a menu
but the add-in tab ( menu ) is not displayed" .
At the end of the Workbook_Open event procedure ( just before End Sub ), the created
menu object certainly exists, but after exiting the Open event procedure the menu
object has disappeared.
Since it failed to hand over the menu object from the add-in to "the menu management
system of Excel" ( The management system did not receive it ), the menu object seems
to have disappeared as it is.
