Excelのユーザーフォームを使って、全てのシートをリストボックスに表示し、そこで選択したシートへ移動するマクロを作成してみました。
シートの移動はExcelのショートカットキーなどで可能ですが、シートの数が20、30となると移動も面倒なので、あえてマクロでやってみました。
Excel内のすべてのシート名が表示され、選択したシートへ移動するユーザーフォームの概要
今回作成したマクロはこんな感じものです。言葉で表現するのがなかなか難しいので動画で見ていただいたほうが伝わりやすいかと思います。
こんな感じで、シート選択ボタンを押すとユーザーフォームが表示されます。
表示されたユーザーフォームのリスト内にはExcelのシートがすべて表示されるようになっており、「選択したシートへ移動」するボタンをクリックすると該当のシートに移動します。
シートの数が増えた場合、リストボックスにも自動で追加されます。
たとえばシートをこんな感じで３つほど追加した場合には↓
きちんとリストボックスにもデータが追加されます↓
で、各シートには「トップへ戻る」ボタンを設置しており、このボタンをクリックすると最初のシート選択を行うシートに戻るようにしています。
ユーザーフォームをざっくりデザイン。リストボックスとコマンドボタンの設置
まずはユーザーフォームをつくります。
こんな感じでリストボックスと、コマンドボックスがあればとりあえずは大丈夫でしょう。
今回上の画像のリストボックスには「myListBox1」という名前をつけて管理することとしています。名前はここから変更が可能です。
この要領で、２つのコマンドボックスについても名前をつけています。
上の「選択したシートへ移動」コマンドボックスには「mysheetchage」という名前を、下の「閉じる」コマンドボックスには「myend」という名前をつけました。
ユーザーフォームの見た目が完成したら、それぞれのボタンが押された時に実行されるマクロなどを組み込んでいきます。
ユーザーフォームを表示した時にリストボックスの中にデータを追加する
ユーザーフォームを表示した際にリストボックスに全てのシート名を表示させるように設定していきます。
コードはこんな感じです。
Private Sub UserForm_Initialize()
'■【ホーム】シートのシート選択ボタンを押した時の処理（ユーザーフォームの表示）
'■ユーザーフォームのリストボックスにExcelブックの全てのシート名を追加する
Dim i As Long
For i = 1 To Worksheets.Count
If Worksheets(i).Name <> "ホーム" Then
UserForm1.myListBox1.AddItem (Worksheets(i).Name)
End If
Next i
'■リストボックスの1番上のデータを選択状態にする
myListBox1.ListIndex = 0
End Sub
ユーザーフォームが表示された際にマクロを実行するにはUserForm_Initializeを使う
ユーザーフォームが表示された際にマクロを実行するにはUserForm_Initializeを使うと実現可能です。
UserForm_Initializeはマクロの記述画面のこの部分で設定します↓
ユーザーフォームのリストボックスにデータを追加するにはAddItemを使う
ユーザーフォームのリストボックスにデータを追加するにはAddItemを使うと実現できます。
今回は全てのシートの名前をリストボックスに追加したいのでForネクストステートメントをつかって繰り返しの処理を行っています。
この部分です。
繰り返す回数は「シートの数」だけ繰り返すので「Worksheets.Count」を使っています。
例えば下記の画像の場合、シートの数が６個あるので6回繰り返しが行われます。
で、「UserForm1.myListBox1.AddItem (Worksheets(i).Name)」とすることでリストボックスの中に、すべてのシートの名前（上の画像の場合、「ホーム」「西尾」、「徳永」、「河原」など）が追加されます。
今回は「ホーム」の名前はリストボックスに追加したくなかったのでifステートメントを使って追加しないようにしています。
うまくいくとユーザーフォームを表示したい際にこんな感じで表示されます↓
リストボックスの中から選択したシートに移動する。
ここまでの作業で、リストボックスにシート名を表示させることができました。
次に「選択したシートへ移動」ボタンをクリックするとこのリストから選択したシートへ移動するように設定していきます。
コードはこんな感じです。
Private Sub mysheetchange_Click()
'■【選択したシートへ移動】ボタンを押した場合の処理（該当のシートへ移動する）
Dim sh_name As String
sh_name = myListBox1.List(myListBox1.ListIndex)
With Worksheets(sh_name)
.Activate
.Range("a1").Select
End With
Unload Me
End Sub
リストボックスの選択されているデータを取得するにはList(.ListIndex)を使うと上手くいく
リストボックスで選択されているデータを取得するにはList(.ListIndex)を使うと実現することができます。
下の画像のハイライト部分がそうです。
今回は変数宣言をしておき、変数「sh_name」にリストボックスで選択された文字データを格納しています。
変数にリストボックスのデータを格納できたらあとは難しくありません。
「Worksheets(sh_name).Activate」とすることでリストボックスで選択したシートへ移動することができるようになります。
【おまけ】リストボックスの中で enter キーが押されたら選択したシートに移動する
移動したいシート名を選択してマウスでボタンをクリックするのがいちいち面倒だと感じる人もいるかもしれません。
この場合の対応策として、リストボックスで移動したいシート名を選択したらキーボードのエンターキーを押すと該当のシートへ移動する方法があります。
この場合には「keypress」イベントを使うことで実現が可能です。
マクロを記述するこの部分で「keypress」を選択します。
するとこんなコードが追加されます
ここにマクロを記述するとOKです。
まとめ
キーボード操作（ctrlキー＋pgupやpgdn）でシート移動や、Excelの左端で右クリックしてでてくるやつ（下の画像）でシートを選択すればイイジャン。
という意見はごもっともなんですが、こういうやり方のほうが使いやすい、わかりやすいという人もいると思い作ってみた次第です。
【本日の近況報告】
銀行口座の電子メール通知サービス申し込み。
【本日の１曲】
Yogee New Waves/CLIMAX NIGHT
ヨギーニューウェーブという日本のバンドの2014年にリリースされた1曲。
バンド名自体は聞いたことがありましたが、曲は知りませんでした。なんかの拍子で最近この曲を知りまして、最近よく聴いています。
今から8年前にこんないい曲があったとは。
オシャレ。
同じく日本のバンド、never young beachが好きな人は気に入ります多分。知らんけど。
