午後わてんのブログ

ベランダ菜園とWindows用アプリ作成(WPFとC#)

右クリックメニューからシートを選択

エクセルExcel2007 #Windows

エクセル2007

f:id:gogowaten:20191014154210p:plain

エクセルでシートが増えてくると選択する手順が増えてめんどくさくなる
一体何回クリックすれば目的のシートが表示できるんだよ！ってなるので
右クリックメニューにボタンを追加してそこに目的のシートを表示するマクロを
登録して楽をしようってなった

f:id:gogowaten:20191014154224p:plain

 
右クリックメニューに「シート一覧」とシートの数だけボタンを作成するマクロ
 
Public Sub 右クリックメニューにシート一覧作成2() '2014/05/28 21:29

    Dim cb As CommandBar
    Dim b1 As CommandBarPopup
    Dim b2 As CommandBarButton
    Dim ws As Sheets
    Set ws = ActiveWorkbook.Sheets    
    Set cb = Application.CommandBars(36)
    Set b1 = cb.Controls.Add(Type:=msoControlPopup, temporary:=True, before:=1)
    
    j = 0
    With b1
        .Caption = "シート一覧"
        For i = 1 To ws.Count
            Set b2 = .Controls.Add(Type:=msoControlButton, temporary:=True, parameter:=ws(i).Name)
            b2.Caption = ws(i).Name
            b2.OnAction = "シート選択"
            ReDim Preserve CBButton(j)
        Next i
    End With
End Sub
 
 
ボタンを押した時に実行されるマクロ
 
Public Sub シート選択()
    Dim sName As String
    sName = Application.CommandBars.ActionControl.Caption
    On Error Resume Next
    ActiveWorkbook.Sheets(sName).Activate ’選択シート表示
End Sub
 
画像だと

f:id:gogowaten:20191014154300p:plain

 
一覧を作りたいブックを表示しておいて
ボタンを作成するマクロを実行

f:id:gogowaten:20191014154322p:plain

実行すると右クリックメニューが

f:id:gogowaten:20191014154336p:plain

こうなってできあがり
 
不具合とか仕様
追加したボタンはエクセルを終了した時に消すようにしている
 
ボタンを作った後にワークシートの挿入とかで新しく作ったシートは表示されない
表示させるにはボタンを作成するマクロをもう一回実行するしかないけど
実行すると前回作ったボタンが残ったままになるので余計に増えてしまうので
ブックを保存してからエクセルを再起動してボタン作成すればいい
 
ボタンを作った後にシートの名前を変更してもボタンの方は変更されないので
そのボタンは無効になる、これもエクセルを再起動してボタン作成マクロ実行になる
 
複数のブックを開いていてそれぞれのボタンを作成した状態でも
右クリックしたブック以外のシートは選択できない
 
参照した所
VBA：「OnAction で呼び出す関数に引数を渡す方法」 - 意の中のカワズ（35歳の壁 別館）
 
OnAction と引数 - 徒然なるままに MS Excel（たまにOffice）
 
Excel VBA を学ぶなら moug モーグ | 即効テクニック | OnActionで実行するプロシージャに引数を渡す
 
右クリックメニューの変更（CommandBars）.エクセルマクロVBAサンプル集
 
Excel VBA を学ぶなら moug モーグ | 即効テクニック | 新しいコマンドバーを作成する
 
 
今回作ったものより便利なアドインが公開されていてエクセル2000の時は
それを使っていたんだけど、自分でも作ってみたくなって挑戦した結果www
かなりの劣化版になった…
Classモジュールとかも使ってみようとしたけど無理でした
Application.CommandBars.ActionControl.Caption
このactioncontrolっていうのでどのボタンが押されたか取得できる
これに辿り着くまでが長かった

f:id:gogowaten:20191014154354p:plain

Classはほんとわからん、いつかは使えるようになりたいなあ
 
2014/05/30追記
続き↓
エクセル2007、右クリックメニューからシート一覧を表示してシートを選択 - 午後わてんのブログ - Yahoo!ブログ