Microsoft Excel VBA - ユーザーフォームに配置したリストボックスでワークシートを選択する方法

◆Excel 2019 動作確認済
　ブックにワークシートが増えてくると、ワークシートタブだけでシートを選択することが煩わしくなります。
　そこで、下図のようなユーザーフォームのリストボックスにワークシートの一覧を表示し、そのリストからワークシートを選択すると、そのワークシートをアクティブにすることができれば便利です。その方法について説明します。
なお、Excel 2019で作成するときは、デフォルトではWorkSheet1しかないので、ワークシートを適当に追加しておきます。

ユーザーフォーム

◆ユーザーフォームを作成する
　まずは、ユーザーフォームを作成します。Visual Basic Editorを表示し、メニューから[挿入]-[ユーザーフォーム]を選択して、ユーザーフォームを作成します。
　ユーザーフォームが表示されたら、ツールボックスからリストボックスを選んでユーザーフォームに貼り付けます。このとき、リストボックスを適当な大きさにしてください。ここでは、Heightを110、Widthを157とし、このリストボックスの名前を「lstSheets」としています。
　ついでに、フォームを閉じるボタンを作っておきましょう。ここでは、ボタンの名前は「cmdClose」とします。

それでは、dmcCloseをダブルクリックしてcmdClose_Clickイベントプロシージャを次のようにします。このプロシージャは単にフォームを閉じるだけです。

Private Sub cmdClose_Click()
    ' ユーザーフォームを閉じる
    Unload Me
End Sub

キャンセルボタンを割り当てる
Visual Basic Editorのオブジェクトボックスから、UserFormを選択しプロシージャボックスでInitializeを選択するとUserForm_Initializeイベントプロシージャが作成されますので、次のように記述してください。
このプロシージャを記述しておくと、フォームが表示されているときにEscキーを押下すると、ボタンを押したことと同じになり、フォームが閉じられます。

Private Sub UserForm_Initialize()
    ' cmdCloseボタンをこのフォームのキャンセルボタンにする
    cmdClose.Cancel = True
End Sub

リストがクリックされたときの処理
　次にVisual Basic Editorのオブジェクトボックスから、lstSheetsを選択します。lstSheets_Clickイベントプロシージャが作成されますので、次のように記述してください。

Private Sub lstSheets_Click()
' *******************************************
' Summary:リストボックスがクリッ
'         クされたときのイベントプロシージャ
' Description:リストで選択されたワークシート
'             をアクティブにする
' Written by:White Tiger
' Date:2009/07/29
' *******************************************
    
    Dim Index As Integer
    Dim strBuf As String
    
    Index = lstSheets.ListIndex  'ワークシートリストの選択された位置
    strBuf = lstSheets.List(Index)  'ワークシート名を取得
    Worksheets(strBuf).Activate
    
    ' セルA1を左上端にする
    Application.Goto Reference:=Range("A1"), Scroll:=True

End Sub

◆ユーザーフォームを呼び出すマクロを作成する
　ユーザーフォームを作成しただけでは、フォームは表示されません。ユーザーフォームを呼び出すマクロを作成する必要があります。
　Visual Basic Editorのメニューから[挿入]-[標準モジュール]を選択して、標準モジュールを挿入します。そこにユーザーフォームを呼び出すマクロを書くわけです。
　ここでは、マクロ名はShowFormとし、次のように記述してください。なお、このマクロでは、シート名がSheet1のときはリストボックスに追加されないようにしています。

Sub ShowForm()
' *******************************************
' Summary:ユーザーフォームを表示する
' Written by:White Tiger
' Date:2009/07/29
' *******************************************
    Dim i As Integer
    For i = 1 To Worksheets.Count   'シート数分処理する
        If Worksheets(i).Name <> "sheet1" Then  'シート名がSheet1ならば表示しない
                 UserForm1.lstSheets.AddItem (Worksheets(i).Name)
        End If
    Next i
    
    With UserForm1
        ' ユーザーフォームのタイトルを設定
        .Caption = "ワークシート選択"
        ' ユーザーフォームを表示
        .Show
    End With
End Sub

　カーソルをShowFormマクロ内において、F5キーを押下すると先ほど作成したユーザーフォームが表示され、リストボックスにシート名が列挙されているはずです。そして、そのリストでシート名をクリックするとそのシートがアクティブになります。

　以上で最初に意図したようなフォームができあがりましたが、もうひと工夫してみましょう。

◆ショートカットでユーザーフォームを呼び出す
　ワークシートにボタンを作成してShowFormマクロを登録するのもよいのですが、すべてのワークシートにそのボタンを作成するのも煩わしいですし、ある特定のシートだけに作成するとそのシートを選択する必要があります。
　ここでは、ShowFormマクロにショートカットキーを割り当て、どのワークシートからも[Ctrl]+[Shift]+[S]を押下すれば、さきほどのユーザーフォームが表示されるようにします。

マクロで実現する場合
　WorkbookのOpenイベントプロシージャに次のように記述します。
　Application.OnKey メソッドは、特定のキー、またはキーの組み合わせを押したときに、指定されたプロシージャを実行します。
+はShiftキーを、＾はCtrlキーを表します。つまり、ShowFormマクロに[Ctrl]+[Shift]+[S]をショートカットキーに割り当てているのです。
OnKeyメソッドの詳しい説明はヘルプを参照してください。

Private Sub Workbook_Open()
    ' ShowFormというマクロにCtrl+Shit+Sのショートカットキーを割り当てる
    Application.OnKey "+^S", "ShowForm"
End Sub

　一度ブックを保存して閉じてから、再度開くとマクロにショートカットキーが割り当てられます。

Excelの標準機能で実現する場合

メニューの「ツール」から「マクロ」－「マクロ」を選ぶ。(Excel 2007 の場合は、[開発タブ]の[マクロ]をクリックする。)
「マクロ名」欄で、ショートカットキーを割り当てたいマクロを選択する。
「オプション」ボタンをクリック。
「ショートカットキー」欄に割り当てたいキーを入力する。
「OK」ボタンをクリック
「マクロ」ダイアログボックスに戻ったら、「キャンセル」ボタンをクリックして完了。

ショートカットキーに割り当てることができる組み合わせ

Ctrlキーとアルファベット
Ctrl＋Shiftキーとアルファベット

Ctrl＋Shiftキーとアルファベットを組み合わせるには、Shiftキーを押しながらアルファベットのキーを押します。

▼ページトップへ

White Tiger

Microsoft Excel VBA - ユーザーフォームに配置したリストボックスでワークシートを選択する方法

Link

design: TempNate