Office TANAKA - Excel VBA Tips[状況に応じて変化するメニュー]

たとえば、コメントを挿入していないセルを選択して[挿入]メニューを開くと、[コメント]コマンドが表示されます。これをクリックすると新しいコメントがアクティブセルに挿入されます。では、すでにコメントを挿入しているセルを選択して[挿入]メニューを開くとどうでしょう。今度は同じ場所に[コメントの編集]コマンドが表示されます。クリックすると、既存のコメントが編集状態になります。

このように、状況に応じてメニューに表示する内容を変化させるテクニックをご紹介します。

実は簡単なんです。たとえば下図のように、状況に応じて変化しない固定されたメニューを作成するには、次のようなコードを実行します。

Sub AddMenu()
    Dim NewM As Variant, NewC As Variant
    ''(1)[新しいメニュー]というメニューを作成します
    Set NewM = CommandBars("Worksheet Menu Bar").Controls.Add(Type:=msoControlPopup)
    With NewM
        .Caption = "新しいメニュー(&C)"
    End With
    
    ''(2)作成したメニューに2つのコマンドを登録します
    Set NewC = NewM.Controls.Add
    With NewC
        .Caption = "1行目の処理"        ''1つ目コマンド名
        .OnAction = "Action_Row"        ''実行するマクロ
        .FaceId = 541
    End With
    Set NewC = NewM.Controls.Add
    With NewC
        .Caption = "1列目の処理"        ''2つ目コマンド名
        .OnAction = "Action_Column"     ''実行するマクロ
        .FaceId = 542
    End With
End Sub

コマンドをクリックしたときに実行するマクロは、OnActionプロパティにプロシージャ名を指定します。上記のコードでは[1行目の処理]コマンドと[1列目の処理]コマンドに、それぞれマクロを設定しています。これは固定されたメニューですので、状況に応じて変化はしません。

状況に応じて表示されるコマンドを変化させるには、[新しいメニュー(C)]がクリックされたときに状況を判定するマクロを実行してコマンド名などを書き換えます。それには、[新しいメニュー(C)]のOnActionプロパティにプロシージャ名を指定してやればいいんです。

つまり、こういうことです。

Sub AddMenu()
    With CommandBars("Worksheet Menu Bar").Controls.Add(Type:=msoControlPopup)
        .Caption = "新しいメニュー(&C)"     ''新しいメニューを登録する
        .OnAction = "ChangeMenu"            ''メニューがクリックされたとき実行するマクロ
        .Controls.Add                       ''空のコマンド1
        .Controls.Add                       ''空のコマンド2
    End With
End Sub

そして、[新しいメニュー(C)]がクリックされたとき実行される「ChangeMenu」を次のように作ります。

Sub ChangeMenu()
    With CommandBars("Worksheet Menu Bar").Controls("新しいメニュー(&C)")
        If Selection.Rows.Count > 1 Then
            With .Controls(1)
                .Caption = ActiveCell.Row & "行目の処理"
                .Enabled = False
                .FaceId = 330
                .OnAction = "Action"
            End With
        Else
            With .Controls(1)
                .Caption = ActiveCell.Row & "行目の処理"
                .Enabled = True
                .FaceId = 541
                .OnAction = "Action"
            End With
        End If
        If Selection.Columns.Count > 1 Then
            With .Controls(2)
                .Caption = ActiveCell.Column & "列目の処理"
                .Enabled = False
                .FaceId = 330
                .OnAction = "Action"
            End With
        Else
            With .Controls(2)
                .Caption = ActiveCell.Column & "列目の処理"
                .Enabled = True
                .FaceId = 542
                .OnAction = "Action"
            End With
        End If
    End With
End Sub

メニューを登録する「AddMenu」では、メニューバーに[新しいメニュー(C)]の登録と、空欄のコマンド2つを登録します。コマンドに表示する文字列やアイコンなどは、どうせ後で書き換えるのですから、この時点では何も指定しません。

メニューバーの[新しいメニュー(C)]がクリックされると、OnActionプロパティに設定した「ChangeMenu」が実行されます。これは[新しいメニュー(C)]が展開される前です。「ChangeMenu」では、選択されているセルの状況に応じて、コマンドの文字列((Caption)や、アイコン(FaceId)、実行可能かどうか(Enabled)を変化させてています。

2つのコマンドは、クリックされるとアクティブセルの列幅や行の高さを表示するという簡単なプロシージャを実行することにします。それぞれのコマンドで別々のプロシージャを実行してもいいのですが、ここではどのコマンドがクリックされたかを判定して処理を分岐するような共通プロシージャ「Action」を作りましょう。

Sub Action()
    Select Case True
    Case InStr(CommandBars.ActionControl.Caption, "行") > 0
        With ActiveCell
            MsgBox .Row & "行目の高さは" & .RowHeight, 64
        End With
    Case InStr(CommandBars.ActionControl.Caption, "列") > 0
        With ActiveCell
            MsgBox .Column & "列目の幅は" & .ColumnWidth, 64
        End With
    Case Else
    End Select
End Sub

実行した結果は次のようになります。

実際に動作を見た方がわかりやすいでしょうから、サンプルブックを用意しました。下記リンクからダウンロードできます。
サンプルブックのダウンロード「sample82.xls(29,696byte)」
右クリックして[対象をファイルに保存]を実行してください。