ツールバーのコントールの種類

ツールバー上のコントロールは「Button」の他に「ComboBox」「PopUp」があります。
「ツールバー」という用語はなくなったのですが．．．．   Office2007以降では「ツールバー」に変わって「リボン」が採用されました。
ですがVBA上では「ツールバー(CommandBar)」は存続しており、 「ツールバー(CommandBar)」を操作する在来マクロは問題なく動作しています。 「ツールバー(CommandBar)」自体はリボンの「アドイン」タブ内のメンバとして表示されます。



 では「今後」としてはどうなのかで「リボン」に移行するのかを考えた場合、「リボン」では動的操作に向かない問題が残ります。
ここでは従来の「ツールバー」を利用する上での説明となりますが、従来の「ツールバー」がリボンの「アドイン」タブに格納されるため、 この「アドイン」タブを選択状態にする対応を追加させています。

前ページの「ボタンが3つ」のサンプルにComboBoxとPopUpを追加したサンプルです。(一部表示です)
前ページのソースコードサンプルの⑦の手前に以下の主要部分が追加されます。


'***************************************************************************************************
'   ■■■ 起動・終了 ■■■
'***************************************************************************************************
'* 処理名　：Auto_Open
'* 機能　　：立ち上げ時自動実行処理
'---------------------------------------------------------------------------------------------------
'* 返り値　：(なし)
'* 引数　　：(なし)
'---------------------------------------------------------------------------------------------------
'* 作成日　：2019年12月03日
'* 作成者　：井上　治
'* 更新日　：2019年12月03日
'* 更新者　：井上　治
'* 機能説明：
'* 注意事項：
'***************************************************************************************************
Private Sub Auto_Open()
    '-----------------------------------------------------------------------------------------------
    Dim objBar As CommandBar                                        ' CommandBar
    Dim objCont As CommandBarControl                                ' CommandBarControl
    Dim objCombo As CommandBarComboBox                              ' CommandBarComboBox
    Dim objPopUp As CommandBarPopup                                 ' CommandBarPopup
    Dim intIx As Integer                                            ' テーブルINDEX
    Dim intIxT As Integer                                           ' テーブルINDEX(当月)
    Dim intY As Integer                                             ' 年
    Dim intM As Integer                                             ' 月
    Dim blnTrue As Boolean                                          ' 境界判定
    Dim tblYM(11) As String                                         ' 年月テーブル

                     ・
                     ・
                     ・

    ' 年月初期値
    intY = Year(Date)
    intM = 4
    ' 1～3月は年を減算
    If Month(Date) < 4 Then intY = intY - 1
    ' 年月テーブル作成(4月から翌年3月まで)
    For intIx = 0 To 11
        tblYM(intIx) = CStr(intY) & "年" & Format(intM, "00") & "月"
        ' 当月か
        If intY = Year(Date) And intM = Month(Date) Then intIxT = intIx
        ' 月を加算
        intM = intM + 1
        ' 月あふれ時は年を加算し､月は1に戻す
        If intM > 12 Then
            intY = intY + 1
            intM = 1
        End If
    Next intIx

                     ・
                     ・
                     ・

    '-----------------------------------------------------------------------------------------------
    ' 年月ComboBox
    Set objCont = objBar.Controls.Add(Type:=msoControlComboBox)     ' ①
    objCont.BeginGroup = True
    Set objCombo = objCont
    With objCombo                                                   ' ②
        .Style = msoComboLabel
        .Width = 120                ' ←この記述は効果がない！？
        .Caption = "年月"
        For intIx = 0 To 11
            .AddItem tblYM(intIx)
        Next intIx
        .ListIndex = intIxT
        .OnAction = "CBO_Click"
    End With
    '-----------------------------------------------------------------------------------------------
    ' PopUp
    Set objCont = objBar.Controls.Add(Type:=msoControlPopup)        ' ③
    objCont.BeginGroup = True
    Set objPopUp = objCont
    objPopUp.Caption = "サブメニュー"
    ' 1番目はボタンの境界をなしにする
    blnTrue = False
    ' ボタンを3つ追加する
    For intIx = 0 To 2                                              ' ④
        ' まずボタンを指定してコントロールを追加
        Set objCont = objPopUp.Controls.Add(Type:=msoControlButton) ' ⑤
        ' ボタンの境界を設定
        objCont.BeginGroup = blnTrue
        ' CommandBarButtonオブジェクトを取得
        Set objBtn = objCont
        objBtn.Style = msoButtonCaption         ' ボタン名を表示
        objBtn.Caption = vntCaption2(intIx)     ' 表示名を設定
        objBtn.TooltipText = vntTipText(intIx)  ' マウスを当てた時のツールチップテキスト
        objBtn.OnAction = vntOnAction(intIx)    ' 動作マクロを設定
        ' 2番目以降はボタンの境界を設定
        blnTrue = True
    Next intIx

                     ・
                     ・
                     ・

End Sub



①
ツールバーにコンボを追加します。ボタンの追加の場合とは「Type」が違うだけです。
②
コンボのリストにアイテムを追加します。UserFormのComboBoxコントロールと全く同じ方法です。
③
ツールバーにポップアップを追加します。
④
ポップアップ上にボタンを３つ追加します。この処理は以前のツールバーにボタンを３つ追加したのと全く同じで、追加(Add)する相手⑤が「objBar」から「objPopUp」に変わるだけです。

このサンプルを実行すると、
Excelのツールバーの表示
(画像をクリックすると、このサンプルがダウンロードできます)
マクロで追加されるツールバーについてはリボンに「アドイン」タブが出現してこのように表示されます。
デフォルトで「アドイン」タブを選択した状態になるようにしています。

ボタンやコンボがクリックされた時の実行されるプロシージャは、OnActionで登録されたものが実行されます。

'***************************************************************************************************
'* 処理名　：BTN_TOUROKU
'* 機能　　：｢登録｣ボタンがクリックされた時の処理
'---------------------------------------------------------------------------------------------------
'* 返り値　：(なし)
'* 引数　　：(なし)
'---------------------------------------------------------------------------------------------------
'* 作成日　：2019年12月03日
'* 作成者　：井上　治
'* 更新日　：2019年12月03日
'* 更新者　：井上　治
'* 機能説明：
'* 注意事項：
'***************************************************************************************************
Private Sub BTN_TOUROKU()
    '-----------------------------------------------------------------------------------------------
    MsgBox "「登録」が押されました"
End Sub

'***************************************************************************************************
'* 処理名　：BTN_KOUSHIN
'* 機能　　：｢更新｣ボタンがクリックされた時の処理
'---------------------------------------------------------------------------------------------------
'* 返り値　：(なし)
'* 引数　　：(なし)
'---------------------------------------------------------------------------------------------------
'* 作成日　：2019年12月03日
'* 作成者　：井上　治
'* 更新日　：2019年12月03日
'* 更新者　：井上　治
'* 機能説明：
'* 注意事項：
'***************************************************************************************************
Private Sub BTN_KOUSHIN()
    '-----------------------------------------------------------------------------------------------
    MsgBox "「更新」が押されました"
End Sub

'***************************************************************************************************
'* 処理名　：BTN_SAKUJO
'* 機能　　：｢削除｣ボタンがクリックされた時の処理
'---------------------------------------------------------------------------------------------------
'* 返り値　：(なし)
'* 引数　　：(なし)
'---------------------------------------------------------------------------------------------------
'* 作成日　：2019年12月03日
'* 作成者　：井上　治
'* 更新日　：2019年12月03日
'* 更新者　：井上　治
'* 機能説明：
'* 注意事項：
'***************************************************************************************************
Private Sub BTN_SAKUJO()
    '-----------------------------------------------------------------------------------------------
    MsgBox "「削除」が押されました"
End Sub

'***************************************************************************************************
'* 処理名　：CBO_Click
'* 機能　　：ComboBoxがクリックされた時の処理
'---------------------------------------------------------------------------------------------------
'* 返り値　：(なし)
'* 引数　　：(なし)
'---------------------------------------------------------------------------------------------------
'* 作成日　：2019年12月03日
'* 作成者　：井上　治
'* 更新日　：2019年12月03日
'* 更新者　：井上　治
'* 機能説明：
'* 注意事項：
'***************************************************************************************************
Private Sub CBO_Click()
    '-----------------------------------------------------------------------------------------------
    Dim xlAPP As Application                                        ' Excel.Application
    Dim objBar As CommandBar                                        ' CommandBar
    Dim objCont As CommandBarControl                                ' CommandBarControl
    Dim objCombo As CommandBarComboBox                              ' CommandBarComboBox
    Set xlAPP = Application
    Set objBar = xlAPP.CommandBars(g_cnsTitle)
    Set objCombo = objBar.Controls(4)
    MsgBox "「コンボ」が選択されました(" & objCombo.Text & ")"
End Sub
このそれぞれのプロシージャがツールバーのボタンやコンボの登録時にOnActionプロパティで設定されているものです。
ボタンについては、それぞれのボタンが別個のプロシージャを呼び出しますが、コンボについてはリスト上のどのアイテムをクリックしても同じプロシージャを呼び出します。従って呼び出されたプロシージャの方でどのアイテムが選択されたのかを判断しなければなりません。これにはコンボのオブジェクト(ツールバーの4番目のコントロール)を取得してから、その選択インデックス(ListIndex)や選択されたテキスト(Text)で判定します。

このサンプルでは、ツールバーの名称を固定していますが、ツールバー名称はApplicationレベルで「択一」となります。つまり同じマクロが実装されているワークブックを複数開くと後で開いた方のワークブックではツールバーが既に存在してしまうため実行時エラーを起こします。
これを避けるためにはツールバーの名称を動的にコントロールしなければなりません。
例えば拡張子を抜いたワークブック名とするなどが考えられますが、名前を付けて保存させると以降はワークブック名は変わってしまうので、開いた時のツールバーの名称を隠しセルに保存するなどの対応が必要です。