【ExcelVBA】ユーザーフォーム活用の基礎【業務改善の強力な武器になります】

2019年6月20日
2019年9月13日
ユーザーフォーム

ユーザーフォーム

みなさん、こんにちはケンケンです。

今回は、ユーザーフォームの作り方をご紹介します。

実務でよく耳にするのは「管理データを作っているけど、横に長い表にたくさんの項目を入力するのが面倒！」といった声です。

みなさんも経験があるのではないでしょうか。

こんな状態を解消するのに役立つのがユーザーフォームです。

基本をおさえてしまえばけっこう簡単に作れて大変便利なので、これを機にマスターしましょう。

1 ユーザフォームとは
2 作成手順
3 フォームからエクセルシートへ転記してみよう
4 変数を使って転記してみよう
5 まとめ

ユーザフォームとは

横に長いシートのデータを更新するのは手間とストレスがかかります。

それを解消してくれるのがユーザーフォームです。

フォームを使うと入力できる書式を限定することもでき、データをきれいに作ることができます。

フォームを自分で作れるとソフトウェア会社へ依頼する金額を大幅に削減できます。

以前、経費精算についてはシステムを導入することをおすすめしましたが、今回作るフォームは経費精算システムとは違って、各社各部署で作りたいフォームが異なるはずなので、それをいちいち外注すると多額のコストがかかるため、フォームは自前で作れるようになった方が良いと考えます。

具体的にどういったものか見てみましょう。

ご覧のとおり、フォームを作ると情報を見やすく入力しやすくすることも可能です。

入力したい項目を埋めて最後に入力ボタンをクリックすると、その内容がエクセルシートに反映される仕組みです。

作成手順

それでは、先ほどお見せしたフォームを手順に沿って作成していきましょう。

フォームの基礎を作る

最初は、フォームの元となる基礎の部分を作って行きます。

Alt+F11でVBEを起動させ「挿入」→「ユーザーフォーム」の順でクリックします。

すると上記のようにフォームが作られ、UserForm１というモジュールもできます。

ツールボックスが表示されていなかったら「表示」から「ツールボックス」を選択し、表示させておいてください。

これで、フォームの基礎工事が終わりました。

フォームを表示させてみよう

フォームを表示されるためには、表示させるためのコードつまり、プロシージャが必要です。

標準モジュールにフォーム起動用のコードを記述します。

Sub start_form()
    Userform1.Show
End Sub

プロシージャ名は「start_form」としました。

UserForm1は先ほど作成した、フォームのオブジェクト名です。

このようにフォームを作成する際は、部品ごとにオブジェクト名があるのでその名前を使ってメソッドを実行できます。

上の図はプロパティウィンドウと言って、VBEの「表示」→「プロパティウィンドウ」で表示できます。

ボタンやテキストボックスなどの部品の様々な状態が記載されています。

コードに戻りますが、UserForm1に対してフォームを表示するshowメソッドを実行しています。

次にエクセルシート上にボタンを配置します。

エクセルシート上で「開発」＋「挿入」からボタンを選択して、先ほど作った「start_form」を選び、OKをクリックしてください。

ボタンができたらクリックしてみましょう。

フォームが起動しました。

まだフォームに何も配置していないので右上の「×」で閉じてください。

フォーム上にボタンを配置してみよう

フォームに入力用と閉じる用のボタンを配置していきます。

その前に、デフォルトではフォームが小さいので少し大きくします。

大きくできたら、フォーム上にボタンを配置します。

ツールボックスからコマンドボタン（上図赤枠）を選択し、フォーム上でクリックするとボタンが配置されます。

ボタンを２つ作り以下のように配置場所を整えます。ボタンはドラッグで位置を変更できます。

オブジェクト名とCaptionを変更しよう

フォームを作成する際に、重要になるのが、オブジェクト名とCaptionです。

Captionはフォーム上の表記の仕方を表しています。ボタンのCaptionを「閉じる」にするとボタンの表記が閉じるに変更されます

オブジェクト名はボタンなど部品個々の名前のことです。Captionとは違ってフォーム上に表示されません。

しかし、オブジェクト名をきちんと付けておくことは重要で、コードを書くのが楽になります。

オブジェクト名とCaptionは部品を作るごとに変更するようクセをつけると良いです。

それでは、先ほど作ったボタンのオブジェクト名とCaptionを変更してみましょう。

プロパティウィンドウから直接変更できます。

２つのボタンを上図のように変更してください。

クリックイベントを実行してみよう

先ほど名前を変更した「閉じる」ボタンをダブルクリックします。

すると、btnClose_clickプロシージャが生成されるので以下のようにコードを書きます。

Private Sub btnClose_Click()
    Unload Me
End Sub

コードの意味としては、自ら閉じるといった感じでしょうか。フォーム自体を閉じるコードです。

これで、エクセルシートからフォームを開いてから「閉じる」ボタンをクリックするとフォームを閉じることが出来るようになります。

ラベルとテキストボックスを配置しよう

これからラベルとテキストボックスを配置していきます。

ラベルは各項目の見出しのようなもので、テキストボックスは文字情報を格納・転記できる機能です。

ツールボックスからラベルを選択してフォームに配置します。

ラベルのCaptionを変更して「氏名」と表示させます。

ラベルの場合は、オブジェクトをクリックして直接Captionを修正できます。

今度は、テキストボックスを選択し上図のように配置します。

これでひとつずつラベルとテキストボックスの配置が出来たので、必要な要素すべてを配置します。

このように配置します。

そして、コードを書きやすくするためにテキストボックスのオブジェクト名を以下のように変更します。

オブジェクト名を付ける時のコツというか暗黙のルールとして、オブジェクトをアルファベット３文字で表現します。

テキスト（text）なら「txt」
ボタン（button）なら「btn」

などが代表例です。

今回はテキストボックスなので「txt」を頭に付けて、氏名を入力するので「txtName」としました。

その他のテキストボックスも同様の法則で名前を付けます。

英語と日本語が混在していますが、ご愛嬌と言うことでお願いします。

意味が通ってコードが書きやすくなっていれば問題ありません。

フォームからエクセルシートへ転記してみよう

準備は整ったのでいよいよフォームからデータを転記してみます。

具体的には、先ほど作ったテキストボックスに入力されたデータを「入力」ボタンをクリックしたときにエクセルシートに書き出します。

なので、「入力」ボタンをダブルクリックし、生成されたbtnInput_Clickプロシージャに以下のようにコードを記述します。

Private Sub btnInput_Click()
    Range("B2").Value = Userform1.txtName.Text
    Range("C2").Value = Userform1.txtFurigana.Text
    Range("D2").Value = Userform1.txtAge.Text
    Range("E2").Value = Userform1.txtJyusyo.Text
    Range("F2").Value = Userform1.txtTel.Text
    Range("G2").Value = Userform1.txtMail.Text
    Range("H2").Value = Userform1.txtTel2.Text
End Sub

さあ準備は整いました。

エクセルシートからフォーム起動用のボタンをクリックし、データを打ち込んで入力ボタンをクリックしてください。

シートに入力することができました。

変数を使って転記してみよう

とうとうフォームから入力することができましたが、このままでは不完全で問題があります。

A列にデータナンバーをとって採番するべき
フォームからの入力は常に2行目に実行されてしまう

この２つの問題を解決したコードが下記になります。

Private Sub btnInput_Click()
    Dim Lrow
    Lrow = Range("A" & Rows.Count).End(xlUp).Row '①
    If Lrow = 1 Then '②
        Range("A2").Value = 1
    Else
        Range("A" & Lrow + 1).Value = WorksheetFunction.Max(Range("A2"), Range("A" & Lrow)) + 1 '③
    End If
    Range("B" & Lrow + 1).Value = Userform1.txtName.Text '④
    Range("C" & Lrow + 1).Value = Userform1.txtFurigana.Text
    Range("D" & Lrow + 1).Value = Userform1.txtAge.Text
    Range("E" & Lrow + 1).Value = Userform1.txtJyusyo.Text
    Range("F" & Lrow + 1).Value = Userform1.txtTel.Text
    Range("G" & Lrow + 1).Value = Userform1.txtMail.Text
    Range("H" & Lrow + 1).Value = Userform1.txtTel2.Text
End Sub

①A列の最終行を取得しています。