Excel Office VBA

Excel2016 : CommandBar各種のNameを取得する

先日田中先生のサイトを読んでいましたら、セルのコンテキストメニューに項目を追加する方法が紹介されていました。

ポイントは、CommandBarsコレクションで "Cell" を指定すれば、セルのコンテキストメニューオブジェクトの現在のインスタンスを取得できるという点っぽいです。
でまあ、取得したインスタンスにあれこれ手を加えりゃいいよーという趣旨だと私は読みました。

そいえば私、シートタブのコンテキストメニューでやりたいことあったんだよなーと思いまして。
シートタブのコンテキストメニューってCommandBarsコレクションではなんて名前で登録されているのかなーと思って調べてみたんですが、

そんな資料はない。

2007の頃は用意だけはされていたけど実際のインスタンスとは何も紐づいていないオブジェクトがあったなど、このへんの実装はけっこういい加減だったような記憶があります。
そのへんをツッコまれるのが嫌で公開していないのかなーという気もしますが、まあとりあえずないものはない。海外のサイト(英語/中国語あたりでいい情報がヒットすることも多いのです)も探してみたんですが、少なくとも私には見つけられませんでした。

どうしよう。

公開されていないなら、自分で調査すればいいじゃない？

という経緯で、CommandBarのNameプロパティを列挙するコードをVBAで組んでみました。


CommandBarのNameプロパティを列挙する

で、作ってみたコードがこちらになります。
'*********************
' コンテキストメニューの列挙
'*********************
Public Sub psList_CommandBars()

    Dim bar As CommandBar
    Dim FNo As Integer: FNo = FreeFile
    
    Open "E:\blog\20161231\CommandBarsList.txt" For Output As FNo
    For Each bar In Application.CommandBars
        Print #FNo, bar.Name
    Next
    Close #FNo

End Sub
この結果をExcelで取り込んでアルファベット順にソートかけてみたんですが、
…やべぇ。どのNameがどのCommandbarのものなのか全然わからない。
しかも同じNameが複数あったりする。なんだこれなんだこれ。

なんとかもう少し手がかりがほしいなー、ということで、各CommandBarが持つControl(メニューの各項目を表すオブジェクトなわけですね)も一緒に列挙させてみることにしました。


CommandBarとControlのNameプロパティを列挙する

で、先ほどのコードを2重ループにしてCommandBarごとに含まれるControlのCaptionも一緒に出力するコードがこちらになります。
'******************************************
' コンテキストメニューと､それぞれに含まれる項目の列挙
'******************************************
Public Sub psList_CommandBars2()

    Dim bar As CommandBar
    Dim itm As CommandBarControl
    Dim FNo As Integer: FNo = FreeFile

    Open "E:\blog\20161231\CommandBarsList2.txt" For Output As FNo
    For Each bar In Application.CommandBars
        For Each itm In bar.Controls
            Print #FNo, bar.Name & vbTab & itm.Caption
        Next
    Next
    Close #FNo

End Sub
結果はこちら。
お、いいんじゃないこれ。

今回探したい「シートタブのコンテキストメニュー」は
こんな項目なわけですから、「シートの保護」とか「シート見出しの色」とかが含まれるCommandBarを探してやればいいことになります。
ということで、シートタブを右クリックしたときに表示されるコンテキストメニューのオブジェクト名は「Ply」だとわかりました。

Ply…うーん、Plyねぇ…。何の略だかぜんぜんわからなくて、今ひとつピンと来ないなー。
しかも、「CommandBars("Ply")」でネット検索かけてみるとけっこういろんなサイトがヒットしたり。
探し方が悪かったのかなー。なんだかちょっぴり悔しいなー。
参考サイト
動作確認環境
Excel2016 16.0.7571.7063 64bit

0 件のコメント :

コメントを投稿