Office TANAKA - Excel VBA Tips[新しいツールバーを作る]

マクロでツールバーを作る方法がわかりません…という質問を受けました。なるほど、メニュー関係の操作は慣れないと訳がわからないものです。ここでは、マクロで新しいツールバーを作るテクニックを解説しましょう。

新しいツールバーを作る

ツールバー(とメニュー全般)はCommandBarオブジェクトです。CommandBarオブジェクトの集合がCommandBarsコレクションですから、新しいツールバーを作成するということは、CommandBarsコレクションに新しいCommandBarオブジェクトを追加するということになります。これにはAddメソッドを使います。次のコードは、新しいツールバーを作成します。

Sub Sample1()
    CommandBars.Add
End Sub

実行してみましたか？実行しても何も起きませんよね。でも実は新しいツールバーが作成されているんです。[ツール]-[ユーザー設定]を実行して[ユーザー設定]ダイアログボックスを開いてください。[ツールバー]タグを開き[ツールバー]リストボックスを一番下までスクロールすると「ユーザー設定 1」というツールバーがあるはずです。これが今作成した新しいツールバーです。

クリックすると拡大します

なお、ツールバー名の最後に付く番号はExcelが管理していて、自動的に増えていきます。また、よく間違えるのですが「ユーザー設定」と「1」の間に半角スペースが1つ入ります。

さて、作成したツールバーを表示するには、VisibleプロパティにTrueを設定します。このとき「どのツールバーを操作するか」ということを明確に指示しなければいけませんので、作成したツールバーを変数myBarに格納して、以降の操作に使いましょう。次のコードは、新しいツールバーを作成して表示します。

Sub Sample2()
    Dim myBar As CommandBar
    Set myBar = CommandBars.Add
    myBar.Visible = True
End Sub

変数を使いたくない…というワガママな人は、次のようにWithステートメントを使ってもいいですよ。

Sub Sample2_2()
    With CommandBars.Add
        .Visible = True
    End With
End Sub

あるいは、これだけで完了する珍しいマクロでしたら、次のように簡素化する手もあります。

Sub Sample2_3()
    CommandBars.Add.Visible = True
End Sub

まぁ、一般的にはこのあと、作成したツールバーに対してあれこれ作業をするわけですから、変数やWithステートメントを使った方が楽ですね。

ボタンを追加する

操作できないツールバーは、ただの飾りです。ツールバーにはボタンやテキストボックス、コンボボックスなどを追加できますが、まずは一般的に使われるボタンの追加方法を解説します。ツールバー上のボタンはCommandBarControlオブジェクトです。CommandBarControlオブジェクトの集合がCommandBarControlsコレクションですから、新しいボタンを追加するには、このCommandBarControlsコレクションのAddメソッドを実行してやればいいんです。ただし、CommandBarControls.Addとしてはいけません。CommandBarControlsコレクションを操作するときは、Controlsプロパティを使うのがVBAのルールです。ControlsプロパティはCommandBarControlsコレクションを返します。次のコードは、新しいツールバーを作り、3つのボタンを追加します。

Sub Sample3()
    Dim myBar As CommandBar, i As Long
    Set myBar = CommandBars.Add
    For i = 1 To 3
        myBar.Controls.Add
    Next i
    myBar.Visible = True
End Sub

一見、何もないように見えますが、マウスポインタを合わせるとボタンの存在を確認できます。
さて、ツールバーに追加するボタンには2種類あります。

　1. Excelが持つ標準機能のボタン
　2. 独自のマクロを実行するボタン

1.は、ファイルを[開く]ボタンや、[上書き保存]ボタンなどExcelが標準で持っているボタンのことです。こうした標準のボタンを追加するには、Addメソッドの引数IDに、追加したい標準ボタンのID番号を指定します。次のコードは、新しいツールバーを作り、[開く]ボタン、[上書き]ボタン、[印刷]ボタンを追加します。

Sub Sample4()
    Dim myBar As CommandBar
    Set myBar = CommandBars.Add
    myBar.Controls.Add ID:=23    ''[開く]ボタン
    myBar.Controls.Add ID:=3     ''[上書き保存]ボタン
    myBar.Controls.Add ID:=2521  ''[印刷]ボタン
    myBar.Visible = True
End Sub

標準ボタンのID番号は公開されていませんので、自分で調べましょう。次のコードは、[標準]ツールバーにあるボタンの名前(Captionプロパティ)とID番号(Idプロパティ)一覧をアクティブシートに書き出します。

Sub Sample5()
    Dim i As Long
    With CommandBars("Standard")
        For i = 1 To .Controls.Count
            Cells(i, 1) = .Controls(i).Caption
            Cells(i, 2) = .Controls(i).ID
        Next i
    End With
End Sub

さて、2番目の「独自のマクロを実行するボタン」は、クリックされたときに実行するマクロを指定しなければなりません。それがOnActionプロパティです。次のコードは、新しいツールバーを作り、クリックされると「myMacro」を実行するボタンを追加します。

Sub Sample6()
    Dim myBar As CommandBar, myButton As CommandBarButton
    Set myBar = CommandBars.Add
    Set myButton = myBar.Controls.Add       ''新しいボタンを追加します
    myButton.OnAction = "myMacro"           ''ボタンがクリックされたとき実行するマクロを設定します
    myBar.Visible = True
End Sub

Sub myMacro()
    MsgBox "Hello!"
End Sub

ボタンのイメージを設定する

今まで作成したボタンにはイメージがありません。これで納得する人はいないでしょうから、次にボタンイメージの設定方法を解説します。

ボタンイメージの設定には、3種類の方法があります。

　1. 標準ボタンのイメージをコピーする
　2. FaceIdプロパティにイメージ番号を指定する
　3. 独自の画像を貼り付ける

1.の方法は、新しいツールバーに標準のボタンを追加したのと同じように、標準ボタンのイメージだけを任意のボタンに貼り付けます。
これには、

　(1) 標準ボタンのイメージをコピーする(CopyFaceメソッド)
　(2) コピーしたイメージを貼り付ける(PasteFaceメソッド)

という2段階の処理が必要です。次のコードは、新しいツールバーを作り、ボタンを1つ追加します。追加したボタンには、[上書き保存]ボタンのイメージを貼り付けます。

Sub Sample7()
    Dim myBar As CommandBar, myButton As CommandBarButton
    Set myBar = CommandBars.Add
    Set myButton = myBar.Controls.Add                               ''新しいボタンを追加します
    CommandBars("Standard").Controls("上書き保存(&S)").CopyFace     ''ボタンイメージをコピーします
    myButton.PasteFace                                              ''ボタンイメージを貼り付けます
    myBar.Visible = True
End Sub

Excelは内部に膨大な数のボタンイメージを持っています。それらには固有の番号が付けられていて、Excel自身が利用しています。このイメージ番号を使ってボタンイメージを設定するのが2番目の方法です。これにはFaceIdプロパティを使います。次のコードは、新しいツールバーを作り、ボタンを1つ追加します。追加したボタンには♪を表示します

Sub Sample8()
    Dim myBar As CommandBar, myButton As CommandBarButton
    Set myBar = CommandBars.Add
    Set myButton = myBar.Controls.Add                           ''新しいボタンを追加します
    myButton.FaceId = 272                                       ''ボタンイメージの番号を指定します
    myBar.Visible = True
End Sub

FaceIdプロパティに指定するイメージ番号は公開されていません。これも自分で調べるしかありませんね。

3番目の方法はちょっとした裏技です。標準ボタンのイメージを流用するとき、一度CopyFaceメソッドでイメージをクリップボードにコピーし、そのデータをPasteFaceメソッドで貼り付けました。ということは、独自の画像をクリップボードに格納しておけば、好きなイメージをボタンに表示できるというわけです。実際にやってみましょう。まずワークシートに画像を1つ挿入します。画像の大きさは16×16程度がいいでしょう。この状態で次のコードを実行します。

Sub Sample9()
    Dim myBar As CommandBar, myButton As CommandBarButton
    Set myBar = CommandBars.Add
    Set myButton = myBar.Controls.Add       ''新しいボタンを追加します
    ActiveSheet.Pictures(1).Copy            ''シートの画像をコピーします
    myButton.PasteFace                      ''コピーした画像をボタンに貼り付けます
    myBar.Visible = True
End Sub

ツールバーの名前を設定する

今までのコードは、ツールバーの作成と、そのツールバーに対する操作を同じプロシージャ内で行っていました。この場合は作成したツールバーを変数に格納したり、あるいはWithステートメントなどを使って操作の対象となるツールバーを特定できました。しかし、作成したのとは別のプロシージャでツールバーを操作するときは、ツールバーの名前を使って操作対象のツールバーを特定しなければなりません。Addメソッドでは「ユーザー設定 1」とか「ユーザー設定 10」などと便宜的な名前が設定されていまいますので、これを独自の名前に変更する必要があるのです。新しいツールバーの名前を設定するには、いくつかの方法があります。次のコードは、新しいツールバーを作り、そのツールバーに「Test」という名前をつけます。

Sub Sample10()
    Dim myBar As CommandBar, i As Long
    Set myBar = CommandBars.Add
    myBar.Name = "Test"                 ''ツールバーの名前を設定します
    For i = 1 To 3
        myBar.Controls.Add ID:=i + 1    ''ダミーのボタンを追加します
    Next i
    myBar.Visible = True
End Sub

ツールバーのタイトルバーが見やすいように、ダミーのボタンを3つ追加しました。
このコードに間違いはありませんが、成功するのは最初の1回だけです。同じコードを2回実行するとエラーになります。すでに存在するツールバーと同じ名前を設定することはできないからです。

新しく作ったツールバーに名前をつけるときは、すでに同じ名前のツールバーが存在するかどうかを調べなければなりません。もし同じ名前のツールバーが存在するときは、既存のツールバーを削除するなどの処理をしなければなりませんが、ここで注意が必要です。そのツールバーは本当にあなたが作ったツールバーですか？たとえば次のコードは、すでに「Test」という名前のツールバーが存在するときは、そのツールバーを削除して、新しい[Test]ツールバーを作成します。

Sub Sample11()
    Dim myBar As CommandBar, i As Long, C
    Set myBar = CommandBars.Add
    For Each C In CommandBars
        If C.Name = "Test" Then         ''「Test」という名前のツールバーが存在したら
            CommandBars("Test").Delete  ''そのツールバーを削除します
            Exit For
        End If
    Next C
    myBar.Name = "Test"                 ''ツールバーの名前を設定します
    For i = 1 To 3
        myBar.Controls.Add ID:=i + 1    ''ダミーのボタンを追加します
    Next i
    myBar.Visible = True
End Sub

削除した[Test]ツールバーは、名前が「Test」というだけです。あなたのマクロが以前に作成したツールバーという保証はありません。もしかすると、何か別のマクロが作成したツールバーかもしれません。Deleteメソッドは、そんなことおかまいなしにサクッと削除しちゃいます。削除したツールバーは、ザオラルやレイズを唱えても復活できません。

これは運用面で解決するしかありません。「ツール」など簡単で誰でも考えそうな名前は使わず、「ツール - Office TANAKA」とか「ツール(田中亨専用)」のように、他のマクロが作成するツールバーとバッティングしないユニークな名前を使うといいでしょう。

長くなりましたので、このへんにしときます。
ツールバーの表示位置を指定したり、移動できなくするなど制限を設定する方法は「ツールバーを変更できないようにする」をご覧ください。