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

入力フォームで実用的なExcelアプリを作ろう

　プログラミング初心者を対象にExcelマクロ／VBAを使った業務自動化プログラムの作り方を一から解説する本連載「Excelマクロ／VBAで始める業務自動化プログラミング入門」。今回は、フォームを作って、そのフォームに入力したデータをExcelに追加していく方法を紹介する。フォームを使用すると、いかにもアプリケーションらしいUI（ユーザーインターフェース）を作ることができる。

　日々の業務で、自分の作ったフォームでデータの入力をしていると、社内でのあなたを見る目が尊敬の眼差しに変わるかもしれない。フォームを作成することは、特に難しいことでもないので、マスターして毎日の業務に取り入れてほしい。

　また、これまでの連載「Excelマクロ／VBAで始める業務自動化プログラミング入門」で紹介してきたさまざまな文法が使われているので、連載の総集編として挑戦してほしい。

まさにVisualな開発！ VBEで入力フォームを作成する

　フォームを作成する手順から、順を追って解説していこう。

　まず、Excelを起動し、VBE（Visual Basic Editor）を起動する。もうVBEの起動方法は分かるはずだ。もし分からない方がいたら、連載第2回の「初心者でも図解で分かる！ VBEの基本的な使い方とVBAプログラムの基礎文法」を読み直してほしい。

フォームを作成する手順

図1　 VBEのメニューから［挿入］→［ユーザーフォーム］と選択する

　VBEの［プロジェクト］エクスプローラー内に、「Sheet1（Sheet1）」というプロジェクトがある。それを選択して、VBEのメニューから［挿入］→［ユーザーフォーム］と選択する（図1）。

　すると図2のように「UserForm1」のフォームが表示される。このフォームは表示されている四角をドラッグして幅を広くしたり、縦を長くしたりすることが可能だ。何を作るかによって適当な大きさのフォームを作るといいだろう。

図2　フォームと［ツールボックス］が表示された

　フォームの横には［ツールボックス］が表示される。この［ツールボックス］にはフォーム上に配置できる「コントロール」が登録されている。

　「コントロール」とはフォーム上に配置する「部品」のようなものだと考えてほしい。

　図2のフォームに付いている「UserForm1」という名前は変更可能だ。フォームのプロパティから変更する。

フォームのプロパティを設定

　フォームが選択された状態であると、［プロパティ］ウインドウ内にUserForm1の［プロパティ］が表示されている。その中の［Caption］に今回は「個人情報」と入力した。

　すると、フォームのタイトルが「個人情報」に変わる。プロパティにはいろいろ種類があるので、各自が設定して確認してほしい。背景色を変える「BackColor」やフォームの枠線の色を変える「BorderColor」など、いろいろ用意されている。今回は「BackColor」に［パレット］から「Blue」を選択してみた（図3）。

図3　フォームの［プロパティ］を設定した

「テキストボックス」「オプションボタン」などのコントロールを配置

　次に［ツールボックス］からコントロールを配置してみよう。今回作るフォームには表のような項目とコントロールを配置する。

　［ツールボックス］でどのコントロールが何に該当するかは、コントロールの上にマウスをかざすとツールチップヒントが出てコントロールの名前が表示されるので、それで確認してほしい。

　表に従ってコントロールを配置すると図4のようになる。

図4　各コントロールを配置した

　各コントロール、この場合は項目名となる「ラベル」を配置したとき、「ラベル」の［Caption］に項目名を指定し、［ForeColor]に「白」を指定している。［ForeColor］値は、その［プロパティ］を選択すると右に表示される［▼］アイコンをタップすると、［パレット］タブと［システム］タブが表示されるので、［パレット］タブ内から「白」を選択するといい。

　「氏名」と「住所」に配置した「テキストボックス」には［プロパティ］の［IMEMode］に「fmIMEModeOn」を選択して、IMEモードをオンにしている。「電話番号」では「fmIMEModeOff」にして、IMEモードはオフにしている。

　「性別」に使用している「オプションボタン」も、［プロパティ］から［Caption］［ForeColor］を設定している。

　配置した各コントロールには、［プロパティ］の［（オブジェクト名）］に任意の名前を指定できる。各配置したコントロールの［（オブジェクト名）］は表を参照してほしい。

コントロールの「イベント」に対応したマクロを書き、動かす

　ここからはマクロを書いてみよう。

コントロールをダブルクリックしてSubプロシージャを自動作成

　まず「登録」ボタンをダブルクリックすると、リスト1のようなSubプロシージャが自動的に作成される。

Private Sub 登録ボタン_Click()
  
End Sub

「登録」ボタンをClickしたときのマクロ

　このリスト1の中にコードを書いていく。コードはリスト2のようになる。

Option Explicit
 
Private sex As String
Private lastRow As Long
 
Private Sub 登録ボタン_Click()
  If 氏名テキストボックス.Text = "" Then
    MsgBox "氏名を入力してください。"
    Exit Sub
  End If
 
  If 住所テキストボックス.Text = "" Then
    MsgBox "住所を入力してください。!"
    Exit Sub
  End If
 
  If 電話テキストボックス.Text = "" Then
    MsgBox "電話番号を入力してください。"
    Exit Sub
  End If
 
  If sex = "" Then
    MsgBox "性別を選択してください。"
    Exit Sub
  End If
 
  With Worksheets("Sheet1")
    lastRow = .Cells(Rows.Count, 1).End(xlUp).Row + 1
    .Cells(lastRow, 1).Value = 氏名テキストボックス.Text
    .Cells(lastRow, 2).Value = 住所テキストボックス.Text
    .Cells(lastRow, 3).Value = 電話テキストボックス.Text
    .Cells(lastRow, 4).Value = sex
  End With
 
  氏名テキストボックス.Text = ""
  住所テキストボックス.Text = ""
  電話テキストボックス.Text = ""
  男性オプションボタン.Value = False
  女性オプションボタン.Value = False
End Sub
 
Private Sub 男性オプションボタン_Change()
  If 男性オプションボタン.Value = True Then
    sex = "男性"
  End If
End Sub
 
Private Sub 女性オプションボタン_Change()
  If 女性オプションボタン.Value = True Then
    sex = "女性"
  End If
End Sub

　マクロの中身を解説しよう。まず、3〜4行目では、「性別」から選択された値を格納しておく文字列型変数「sex」とデータの入っている行の個数を格納するLong型変数「lastRow」を宣言している

　「登録ボタン_Click()」では、まず「氏名」「住所」「電話番号」にデータが入っていなかったら警告メッセージを表示する（7〜20行目）。

　文字列型変数sexに値が入ってない場合も、警告メッセージを表示してる（22〜25行目）。sexに入る値は、次に記述しているコード内で「男性」または「女性」の「オプションボタン」の値が変更されたときに取得するようにしている（42〜52行目）。

　27〜33行目では、全ての項目に無事値が入ると、Sheet1のワークシートに対してセルの上方向（xlUp）に向かってデータが入っているセルを検索し、そのセルの1つ下のセルにデータを追加していく処理を記述している。この場合、「氏名」の項目名の下のセルにデータを追加する。

　最初の行の1列目に「氏名テキストボックス」の値を入力する。lastRowにはデータの入っている行の個数が格納されているため、データの入っている行の1つ下の行から順次データを追加していく。

　次に、2列目の「住所」に「住所テキストボックス」の値を入力、3列目の「電話番号」に「電話番号テキストボックス」の値を入力する。最後に、4列目の「性別」に変数sexが格納している値を入力する。

　データを追加していくと、追加されたデータの位置まで上方向に向かって検索し、その1つ下にデータを追加していくので、順次データが追加されていくことになる。

入力フォームは実行後にクリアすることが大事

　Excelにデータを追加した後は、それぞれの入力ボックスを空にし、オプションボタンの選択も解除する。

　このように、入力フォームを持つアプリケーションの開発では、一度入力して実行した後は、次の入力のために入力内容をクリアしておくことが大事だ。忘れずに行おう。

実行結果

　このマクロを実行してフォームにデータを入力したのが図5、「登録」ボタンでExcelに追加したのが図6となる。データが追加される「A3:D:100」の範囲には、Excel 2016のメニューから「フォントのサイズ」を「14」に手動で設定している。

図5　フォームにデータを入力した

図6　「登録」ボタンでExcelにデータを追加した

　このマクロを実行してデータを追加していくには、あらかじめExcelの表に「氏名」「住所」「電話番号」「性別」といった項目名だけの表を作成しておく必要がある。

Excel上からフォームを起動させるShowメソッド

　このフォームをExcel上から起動させるには、次の手順を踏む。

　VBEの画面で、プロジェクト内の「VBAプロジェクト（Form1.xlsm）」を選択し、VBEメニューの［挿入］→［標準モジュール］と選択して、Module1を追加する。その中に、リスト3のマクロを追加する。Showメソッドでフォームを表示するだけの簡単なものだ。

Option Explicit
Sub 入力フォーム起動()
  UserForm1.Show
End Sub

　次に、Excel上にボタンを配置して、名前に「入力フォーム起動」とでもして、「入力フォーム起動」マクロを関連付けると、図7のようになる。

図7　 「入力フォーム起動」ボタンにマクロを関連付けた

　以上で、「テキストボックス」と「オプションボタン」を使用したフォームの例は終わりだ。

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

　いろいろなコントロールがあるので、全てを紹介することはできないが、最後に、使用頻度が高いと思われる、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

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

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

図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

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

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

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

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

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

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

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

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

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

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

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

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

参考書籍

• 『Excel VBA 逆引き辞典パーフェクト 2013/2010/2007/2003対応』翔泳社
• 『わかるハンディ 仕事がはかどるマクロ＆VBA Excel』学研パブリッシング

著者プロフィール

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年に薬師寺聖と結成したコラボレーション・ユニット