Excelに入力フォームを作成、コントロールを追加、表示、ボタンでイベント実行：Excelマクロ／VBAで始める業務自動化プログラミング入門（12）（3/3 ページ）

» 2016年09月30日 05時00分公開

[薬師寺国安，PROJECT KySS]

前のページへ 1|2|3 　　　　　　

「リストボックス」コントロールを使う、検索アプリを作る

　いろいろなコントロールがあるので、全てを紹介することはできないが、最後に、使用頻度が高いと思われる、ListBoxコントロールについて解説しておこう。

　では、先のサンプルで入力したデータの「氏名」を取り出して「リストボックス」に表示し、「リストボックス」に登録されたデータを検索し、該当するデータが見つかった場合、その行を選択するサンプルを作ってみよう。

フォームの作成と「リストボックス」の配置

　図1から図4の手順で新しくフォームを作成する。フォームの［プロパティ］から、［Caption］に「名前のリスト」と指定する。

　［ツールボックス］から「リストボックス」をフォーム上に配置し、［（オブジェクト名）］を「名前リストボックス」としておく。また「Font」［プロパティ］にも「Meiryo UI」を指定しておく。

　次に「テキストボックス」を1個配置し、［プロパティ］の［（オブジェクト名）］に「検索名前テキストボックス」と指定する。［プロパティ］の［IMEMode］に「fmIMEModeOn」を選択して、IMEモードをオンにしている、次に「コマンドボタン」を1個配置し、［Caption］［プロパティ］に「検索」、［（オブジェクト名）］に「検索ボタン」と指定する。

　フォームがアクティブになったときに、Excelに登録されている「氏名」を取得し、入力ボックスに検索したい人物名を完全一致で入力して「検索」ボタンをクリックすると、その該当する行が選択状態になる。

　レイアウトすると図8のようになる。

図8　フォーム上に「リストボックス」と「コマンドボタン」を配置

フォームを表示するマクロ

　Excel上に「名前の取得」というボタンを配置して、VBEから「標準モジュール（Module2）」を追加して、リスト4のマクロを書いておく。

Option Explicit
Sub 名前の取得()
  UserForm2.Show
End Sub

リスト4　「名前のリスト」というフォームを表示するマクロ

　この、「名前の取得」マクロを「名前の取得」ボタンと関連付けておく。

　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

リスト5　「リストボックス」に「氏名」を表示し、任意の「氏名」を検索すると、その行が選択状態になるマクロ

　まず、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のようになる。

図9　「名前リストボックス」に登録された「氏名」を検索して、該当する行を選択した

　こういったことができると、何が便利なのか？大量のデータがあるときに、検索したい人物を検索すると、一瞬にして該当人物の行が選択される。

「あいまい検索」も作ってみた

　ここまでは「完全一致」検索にしているが、「あいまい検索」も作ってみた。フォームに「あいまい検索」ボタンを追加して（図10）、リスト6のコードを「あいまいボタン_Click()」の中に記述すると、入力した文字を含むデータが全て表示される。

図10　「あいまい検索」ボタンを追加した

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

リスト6　あいまい検索を実行するマクロ

　実行手順としては、図10の「名前の取得」ボタンをクリックする。「名前のリスト」フォームが表示されるので、入力欄に、あいまいな文字を入力する。今回は「深」と入力してみた。その後、「あいまい検索」ボタンをクリックすると、図12のように表示される。

　今回はフィルターを使っているので、表示させた後は「解除」ボタン（図11）でフィルターを解除する。

図11　フィルターを解除するボタンを追加した

　フィルターを解除するコードはリスト7だ。このリスト7のコードはModule2の中に記述し、図11の「解除」ボタンと関連付ける。

Sub フィルターの解除()
  If ActiveSheet.AutoFilterMode Then
    ActiveSheet.AutoFilter.Range.AutoFilter
  End If
End Sub

リスト7　フィルターを解除するマクロ

　コードの解説は、ここまで読み進んできた皆さんなら分かると思うので割愛させていただく。

図12　「深」で「あいまい検索」をした結果

次回からは応用編。もう少し作り込んだものを

　これまで「Excelマクロ／VBAで始める業務自動化プログラミング入門」として、毎回異なるサンプルを作っていたが、今回でいったん一区切りにする。この連載で紹介した内容はごく基本的な事柄であるが、明日にでも業務に応用できるサンプルも紹介したつもりだ。この連載を足掛かりにして、毎日の業務のマクロ化にぜひ挑戦してもらいたい。

　手間と時間の削減をせずに、残業ばかり強制している企業に未来はない。全ての業務は定時までに終わらせる。そのために、この連載はきっと読者のお役に立つと思う。ぜひ頑張っていただきたい。

　次回からは、これまでの連載で紹介した内容を基に応用編として、もう少し作り込んだ「個人情報管理システム」を作ってみたいと思う。そんなに複雑なことはできないが、紹介するシステムは明日からでも、読者の会社で利用可能なものとなるだろう。

　「データ入力」「データ検索」「データ削除」「印刷設定」「印刷またはPDF化」のメニューを作り、メニューをクリックすることで、各フォームが開き、該当する処理ができるようにしていきたいと思う。次回もお楽しみに。