いろいろなコントロールがあるので、全てを紹介することはできないが、最後に、使用頻度が高いと思われる、ListBoxコントロールについて解説しておこう。
では、先のサンプルで入力したデータの「氏名」を取り出して「リストボックス」に表示し、「リストボックス」に登録されたデータを検索し、該当するデータが見つかった場合、その行を選択するサンプルを作ってみよう。
図1から図4の手順で新しくフォームを作成する。フォームの[プロパティ]から、[Caption]に「名前のリスト」と指定する。
[ツールボックス]から「リストボックス」をフォーム上に配置し、[(オブジェクト名)]を「名前リストボックス」としておく。また「Font」[プロパティ]にも「Meiryo UI」を指定しておく。
次に「テキストボックス」を1個配置し、[プロパティ]の[(オブジェクト名)]に「検索名前テキストボックス」と指定する。[プロパティ]の[IMEMode]に「fmIMEModeOn」を選択して、IMEモードをオンにしている、次に「コマンドボタン」を1個配置し、[Caption][プロパティ]に「検索」、[(オブジェクト名)]に「検索ボタン」と指定する。
フォームがアクティブになったときに、Excelに登録されている「氏名」を取得し、入力ボックスに検索したい人物名を完全一致で入力して「検索」ボタンをクリックすると、その該当する行が選択状態になる。
レイアウトすると図8のようになる。
Excel上に「名前の取得」というボタンを配置して、VBEから「標準モジュール(Module2)」を追加して、リスト4のマクロを書いておく。
Option Explicit Sub 名前の取得() UserForm2.Show End Sub
この、「名前の取得」マクロを「名前の取得」ボタンと関連付けておく。
UserForm2内、[Caption]プロパティは「名前のリスト」とする。
「名前のリスト」フォームにはリスト5のマクロを記述する。
Option Explicit Private lastRow As Long Private Index As Integer Private Sub UserForm_Activate() Dim i As Long lastRow = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row + 1 If lastRow <= 3 Then MsgBox "データがありません。" Exit Sub End If For i = 3 To lastRow 名前リストボックス.AddItem Cells(i, 1) Next End Sub Private Sub 検索ボタン_Click() Dim searchName As String searchName = 検索名前テキストボックス.Text If searchName = "" Then MsgBox "検索する名前を入力してください。" Else Dim i As Long Dim no As Long For i = 0 To 名前リストボックス.ListCount - 1 If 名前リストボックス.List(i) = searchName Then no = i 名前リストボックス.ListIndex = no Exit For ElseIf i >= 名前リストボックス.ListCount - 1 Then MsgBox "該当なし。" Exit For End If Next Index = no + 3 Rows(Index).Select End If End Sub
まず、3行目では、データの入力されている、セルの行数を格納するLong型変数「lastRow」を宣言している。4行目では、「名前リストボックス」名前と検索入力欄に入力された「氏名」が一致した場合の、「名前リストボックス」のインデックス番号を格納するInteger型変数「Index」を宣言している。
「UserForm_Activate()」では、まず変数lastRowにExcelに入力されているデータの個数を格納する(8行目)。Excel上でデータは3行目から入力されるため、変数lastRowが「3」か、それより小さい場合は、警告メッセージを出す(9〜12行目)。
AddItemメソッドで、1列目の変数i行目に該当する「氏名」を「名前リストボックス」に追加する(14〜16行目)。
「検索ボタン_Click()」では、まず文字列型変数「searchName」に「検索名前テキストボックス」に入力された値を格納している(19〜21行目)。
「検索名前入力ボックス」にデータが未入力の場合は警告メッセージを表示する(22〜23行目)。
27〜36行目では、「名前リストボックス」に登録された「氏名」の個数分、変数iを使って反復処理を行っている。
「名前リストボックス」の値とsearchNameに格納されている値が一致したなら、変数「no」に変数「i」を代入し、名前リストボックス内で該当する名前を選択状態にし、処理を抜ける(28〜31行目)。
「名前リストボックス」の個数だけ反復処理が実行された場合は、searchNameと一致するデータがなかったことになるため、警告メッセージを表示する(32〜33行目)。
変数Indexには変数noに3を加算した値を代入する(37行目)。3を加算するのはExcel上でデータは3行目から入力されるためだ。
最後に、Selectメソッドで変数Indexに該当する行を選択する(38行目)。
リスト4を実行すると、図9のようになる。
こういったことができると、何が便利なのか? 大量のデータがあるときに、検索したい人物を検索すると、一瞬にして該当人物の行が選択される。
ここまでは「完全一致」検索にしているが、「あいまい検索」も作ってみた。フォームに「あいまい検索」ボタンを追加して(図10)、リスト6のコードを「あいまいボタン_Click()」の中に記述すると、入力した文字を含むデータが全て表示される。
Private Sub あいまいボタン_Click() Dim last As Long If 検索名前テキストボックス.Text = "" Then MsgBox "あいまい抽出する名前を入力してください。" Exit Sub End If last = Range("A500").End(xlUp).Row Range("A2:D" & last).AutoFilter Field:=2, Criteria1:="=*" & 検索名前テキストボックス.Text & "*" End Sub
実行手順としては、図10の「名前の取得」ボタンをクリックする。「名前のリスト」フォームが表示されるので、入力欄に、あいまいな文字を入力する。今回は「深」と入力してみた。その後、「あいまい検索」ボタンをクリックすると、図12のように表示される。
今回はフィルターを使っているので、表示させた後は「解除」ボタン(図11)でフィルターを解除する。
フィルターを解除するコードはリスト7だ。このリスト7のコードはModule2の中に記述し、図11の「解除」ボタンと関連付ける。
Sub フィルターの解除() If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilter.Range.AutoFilter End If End Sub
コードの解説は、ここまで読み進んできた皆さんなら分かると思うので割愛させていただく。
これまで「Excelマクロ/VBAで始める業務自動化プログラミング入門」として、毎回異なるサンプルを作っていたが、今回でいったん一区切りにする。この連載で紹介した内容はごく基本的な事柄であるが、明日にでも業務に応用できるサンプルも紹介したつもりだ。この連載を足掛かりにして、毎日の業務のマクロ化にぜひ挑戦してもらいたい。
手間と時間の削減をせずに、残業ばかり強制している企業に未来はない。全ての業務は定時までに終わらせる。そのために、この連載はきっと読者のお役に立つと思う。ぜひ頑張っていただきたい。
次回からは、これまでの連載で紹介した内容を基に応用編として、もう少し作り込んだ「個人情報管理システム」を作ってみたいと思う。そんなに複雑なことはできないが、紹介するシステムは明日からでも、読者の会社で利用可能なものとなるだろう。
「データ入力」「データ検索」「データ削除」「印刷設定」「印刷またはPDF化」のメニューを作り、メニューをクリックすることで、各フォームが開き、該当する処理ができるようにしていきたいと思う。次回もお楽しみに。
【2016/9/30】Windows 10、Excel 2016に対応しました。
PROJECT KySS 薬師寺 国安(やくしじ くにやす)
1950年生まれ。フリーVBプログラマ。高級婦人服メーカーの事務職に在職中、趣味でVBやActiveXに取り組み、記事を執筆。2003年よりフリー。.NETやRIAに関する執筆多数。Windowsストアアプリも多数公開中(約270本)。
Microsoft MVP for Development Platforms - Client App Dev (Oct 2003-Sep 2012)。
Microsoft MVP for Development Platforms - Windows Phone Development(Oct 2012-Sep 2013)。
Microsoft MVP for Development Platforms - Client Development(Oct 2013-Sep 2014)。
PROJECT KySSは、1997年に薬師寺聖と結成したコラボレーション・ユニット
Copyright © ITmedia, Inc. All Rights Reserved.