[[20191003112009]]　『Auto_Openで複数ブックの右クリックメニューに増ａx（はるき）　ページの最後に飛ぶ

『Auto_Openで複数ブックの右クリックメニューに増えない、Auto_Closeで消えない』（はるき）

みなさんよろしくお願いします。

任意のブック（Book1.xlsx)を開いた状態でマクロ入りブック(MBook1.xlsm)を開くことで
フォームだけが前面に出てブック本体は最小化し、フォームからの入力をBook1.xlsxに反映させられるものを作成中です。
現在Book1.xlsxのみ開いた状態ではそれが実現できていますが、複数のブックが開いた状態では問題が発生してしまいます。

今のところ主な問題は２つです。
１．複数ブックを開いた状態でMBook1.xlsmを開くと、アクティブなブックにだけしか右クリックメニューに追加されない
MBook1.xlsmを起動すると、Auto_Openマクロで右クリックメニューにフォームを起動するメニューを
追加するように設定していますが、複数ブックを開いた状態でMBook1.xlsmを起動すると
そのときアクティブだったブックの右クリックメニューにしかメニューが追加されません。
これをすべてのブックに追加するようにしたいです。

２．MBook1.xlsmを閉じても右クリックメニューにフォーム起動メニューが残っている
MBook1.xlsmを閉じるときにAuto_Closeで右クリックメニューからフォーム起動メニューを削除するようにしていますが
MBook1.xlsm起動後に新たなブックを開くと（ここにはフォーム起動メニューが追加されています）
新たに開いたブックからはフォーム起動メニューが消えてくれません。
また、MBook1.xlsmを閉じたはずなのに右クリックメニューからフォーム起動メニューを選択すると
フォームが起動し正常動作します（MBook1.xlsmが再び起動します）。
MBook1.xlsmを閉じたら開いているすべてのブックの右クリックメニューからフォーム起動メニューを消したいです。

実は他にも問題が発生中なのですが、再現性があるのがこの２つです。
（右クリックメニューからフォーム起動メニューが消えてしまったり、
閉じたはずのMBook1.xlsmが見た目では消えているのに残っていて再び起動しようとしたときにActivate扱いになりAuto_Openが働かない、など）
他にもお気づきの点がありましたらご指摘お願いします。

ちなみに右クリックメニューから起動にしているのは、
こうすることでアクティブウィンドウの下に隠れてしまうことを防げたからです。

起動時と終了時のマクロは以下の通りです。

(MBook1.xlsmのThisWorkbook)

 Private Sub Workbook_WindowActivate(ByVal Wn As Window)
     If Workbooks.Count = 1 Then
         Application.Quit
     Else
         Windows(ThisWorkbook.Name).WindowState = xlMinimized
     End If
 End Sub

(MBook1.xlsmのModule1（抜粋））

 Sub フォーム表示()
     DrawingForm.Show (vbModeless)
     Windows(ThisWorkbook.Name).WindowState = xlMinimized
 End Sub

 Sub Auto_Open()
     Windows(ThisWorkbook.Name).WindowState = xlMinimized
     Call AddMenu
     If Workbooks.Count = 1 Then
         MsgBox "「罫線ユーティリティ」だけを開くことはできません" & vbCrLf & "他のブックを開いてから起動させてください", vbInformation, "罫線ユーティリティ　起動エラー"
     Else
         MsgBox "右クリックメニューから「罫線ユーティリティ」を起動させてください", , "罫線ユーティリティ"
     End If
 End Sub

 Sub Auto_Close()
     Dim cmdb_ctrl As CommandBarControl
     For Each cmdb_ctrl In Application.CommandBars("Cell").Controls
         With cmdb_ctrl
             If .Caption = "罫線ユーティリティ" Then .Delete
         End With
     Next cmdb_ctrl
 End Sub

 Sub AddMenu()
     Dim cmdb_ctrl As CommandBarControl
     For Each cmdb_ctrl In Application.CommandBars("Cell").Controls
         With cmdb_ctrl
             If .Caption = "罫線ユーティリティ" Then Exit Sub
         End With
     Next cmdb_ctrl
     With Application.CommandBars("Cell").Controls.Add(before:=1)
         .Caption = "罫線ユーティリティ"
         .OnAction = "フォーム表示"
     End With
 End Sub

どうぞよろしくお願いします。

※この内容を記したあと、AddMenuマクロをFor each wb in workbooksで各ブックの右クリックメニューを指定するとどうなるか調べてみました。

 Sub AddMenu()
     Dim cmdb_ctrl As CommandBarControl, wb As Workbook, a As Boolean
     For Each wb In Workbooks
         For Each cmdb_ctrl In wb.Application.CommandBars("Cell").Controls
             With cmdb_ctrl
                 If .Caption = "罫線ユーティリティ" Then a = True
             End With
         Next cmdb_ctrl
         Debug.Print wb.Name; a
         If a <> True Then
             With wb.Application.CommandBars("Cell").Controls.Add(before:=1)
                 .Caption = "罫線ユーティリティ"
                 .OnAction = "フォーム表示"
             End With
         End If
         a = False
     Next wb
 End Sub

の結果（イミディエイトウィンドウ）

 Book1 False
 Book2 True
 Book3 True

という処理になっていましたが、実際右クリックメニューを見るとBook3のみメニューが追加されていました。

＜使用 Excel：Excel2016、使用 OS：Windows10 ＞

 回答ではありませんが

 コマンドバーのメニューは　状況依存で、コロコロ変わります
 試しにこんなコードを走らせてみてください
 いろいろわかるかもです

 Sub test()
     Dim cmdb_ctrl As CommandBarControl
     For Each cmdb_ctrl In Application.CommandBars("Cell").Controls
         With cmdb_ctrl
            Debug.Print .Index, .Caption, .Visible, .Enabled, .BuiltIn
         End With
     Next cmdb_ctrl
 End Sub

（渡辺ひかる） 2019/10/03(木) 12:16

渡辺ひかるさんありがとうございます。

さっそくやってみたところ、Book3だけこれが追加されていました。
(余分なスペースは省きました）

 1 罫線ユーティリティ True True False

Book1とBook2にはありませんでした。

また、もう一つわかったことがあるので報告します。
「罫線ユーティリティ」が追加されるのは、MBook1.xlsmを実行したときにアクティブだったブックだけでした。
　Book1がアクティブの時はBook1のみ
　Book2がアクティブの時はBook2のみ
　Book3がアクティブの時はBook3のみ
です。
いずれの場合も

 1 罫線ユーティリティ True True False
はメニューに追加されたブックのみ加わります。

ただ、どの場合でもイミディエイトウィンドウは

 Book1 False
 Book2 True
 Book3 True
です。
（はるき） 2019/10/03(木) 13:22

 結果だけで評価しないで、私ならＦ８ステップ実行で地道にチェックしてるけど、
 なんで、こういうことをしないんですかね？

 あてずっぽうだけれど、CommandBars("Cell")って２つあるのご存知ですか？
 （2007以降は調べた事がないけど、2003以前と同じだと思う。）
 また、条件付き書式の使用は？
 全て記憶だけで書いてますので、外れてたらすみません。
（BJ） 2019/10/03(木) 14:06

 一部BJさんと被りましたが・・

 私の予想では、メニューは追加されていて非表示になっているだけではないかなと思ったのですが
そうでもなさそうですね。

 ＞「罫線ユーティリティ」が追加されるのは、MBook1.xlsmを実行したときにアクティブだったブックだけでした。

 試してみていただきたいのですが
 メニューを追加するときに　そのブックをActivate　してから　追加したらどうなるでしょう？

 話は飛びますがこんなコードを実行すると

 Sub test2()
    Dim mycb As CommandBar

    For Each mycb In Application.CommandBars
        With mycb
            If .Name = "Cell" Then
                Debug.Print .Index, .Name
            End If
        End With
    Next

 End Sub

 下記のように2つ表示されます

 35           Cell
 38           Cell

 また　理由がよくわかりませんが　オブジェクトを比較すると　理解困難な結果になります
 複数のブックを開いておいて

 Sub test3()

    Debug.Print Workbooks(1) Is Workbooks(1) 'True

    Debug.Print Workbooks(1).Application Is Workbooks(2).Application 'True

    Debug.Print Workbooks(1).Application.CommandBars("Cell") Is _
                Workbooks(1).Application.CommandBars("Cell") 'False

    Debug.Print Workbooks(1).Application.CommandBars("Cell") Is _
                Workbooks(2).Application.CommandBars("Cell") 'False

    Debug.Print Application.CommandBars("Cell") Is _
                Application.CommandBars("Cell") 'False
 End Sub

 3番目以降もTrueになりそうなものですが、Falseになります。
 indexを直接指定しても同様な結果です
 リボンになってから、コマンドバーはほとんどいじっていませんが、
 メニューを追加するにはxmlファイルを使用するのが推奨されているようです。

（渡辺ひかる） 2019/10/03(木) 14:22

渡辺ひかるさん、BJさん、ありがとうございます。
.CommandBars("Cell")が２つあるとは知りませんでした。何かの機会にまた勉強したいと思います。
また、条件付き書式については今回の相談とは関係ありません。

渡辺ひかるさんのアドバイスのとおり、それぞれのシートをその都度アクティブにすることで
すべてのシートの右クリックメニューに追加、抹消が実現できました。
本当に助かりました。ありがとうございました！
また壁に当たってしまったら相談しに来たいと思いますので、そのときはまたよろしくお願いします。
（はるき） 2019/10/03(木) 15:17

コメント返信：

[ 一覧(最新更新順) ]

[[20191003112009]] 『Auto_Openで複数ブックの右クリックメニューに増ａx（はるき） ページの最後に飛ぶ

[[20191003112009]]　『Auto_Openで複数ブックの右クリックメニューに増ａx（はるき）　ページの最後に飛ぶ