エクセルVBAで住所録入力フォームを作る方法

VBA

スポンサーリンク


住所録をエクセルVBAのユーザーフォームを使って、オリジナルの「住所録入力フォーム」を作ろうと思った2017年12月。

波乗りアヒル
波乗りアヒル

まず取り組んだのが名前を入力すると「通し番号」が連番でセルに入る、自動連番付与の作成。

自動連番付与の完成後「住所録入力フォーム」作成に取り組んだが、知識不足で試行錯誤の毎日。

4か月もの時間を擁して2018年4月やっと作ることができたのが、このページに書いてあります。

波乗りアヒル
波乗りアヒル

もちろん今も使っています!

以前自作したカレンダーを改良して「和暦西暦対応カレンダー」にしたので、住所録フォームに組み込みたくなった2018年10月。出来上がったらこのページもリメイクします。

スポンサーリンク


スポンサーリンク

ユーザーフォームを使って入力も検索も簡単に

年賀状送付の為だけに住所録を必要としている場合は、市販の年賀状ソフトを購入して住所を入力すれば困ることは無いのですが、仕事などではそういう訳にはいかないですよね。

一般的にはエクセルを使って、名前や住所に担当者や顧客区分などオリジナルの項目を追加して住所録を作ることが多いと思います。

そんな住所録をエクセルで作って自分自身が使うだけならば問題はないのですが、他の人が使うとなると時々問題が発生した経験ってありませんか?

それは作成者が意図しない入力方法や、書式設定や数式を入れてあるセルを消されてしまって正しく表示されなくなってしまうことなど。

このようなことを防ぐ方法として、通常のワークシートのセルへの直接入力をさせないで、別枠の入力専用フォームをエクセルのワークシート上に表示させて、ここで入力をしてもらうようにします。

また、住所録なので探すことも必要なので、同じようにオリジナルの検索フォームも作ってしまえば、とても便利で作ったエクセル住所録を壊されないように守れますね。

ユーザーフォームを作る前に準備

住所録の必要項目をワークシートにつくろう

住所録を入力するフォームを作りますが、その前にエクセルの住所録で必要な項目を先に作りましょう。

なぜなら、オリジナルの入力フォームは入力した内容をエクセルの住所録に自動的に書き加える為の単なる画面みないなものだからです。

住所録なので最低限必要な項目と言えば、名前・住所・生年月日ですね。それとは別に追加しなくてはいけない必要項目がありますね。

では、使う人にとって必要な項目とは何でしょうか?それは、検索することが多い項目が必要項目ってことですね。

例えば性別や年齢などは、年賀状や暑中見舞いなどの為だけならば必要ないですが、業務内容によっては性別や年齢でサービス内容が変わる場合は必要になりますよね。

エクセル住所録1

この画像のように、ここでは基本的な住所録に必要な「顧客番号」「名前」「フリガナ」「郵便番号」「住所」「生年月日」「性別」「電話番号」「メードアドレス」の9項目を作って利用しています。

住所録入力フォームをつくろう

エクセルのワークシートに直接入力をすることも当然可能ですが、顧客数が少なければ新規入力する時に画面をスクロールして入力済みの最終行を探して入力すればいいのですが、100人、200人・・・と増えていった場合に、いちいちスクロールして入力するのはとても面倒ですし時間の無駄になります。

その為に専用の住所録入力フォームをつくってしまえば、無駄な時間が無くなり仕事の効率化につながります。
ユーザーフォームの作り方が分からない方はこちらも参考にしてくださいね。

ここでつくる住所録入力フォームは、必要項目を入力して図のような新規登録ボタンを押すと、自動的に入力済み最終行を見つけてその下の行に入力した項目を転記してくれるフォームなのです。

探す手間やスクロールする手間もないので時間短縮・仕事効率化になりますよ。

エクセル住所録入力フォーム

エクセルでユーザーフォーム(UserForm1)を作り、入力に必要な項目が分かる為のラベル(Label)と実際に入力をする為のテキストボックス(TextBox)と性別を選んでもらう為のオプションボタン(OptionButton)とワークシートに転記する為のコマンドボタン(CommandButton)を配置します。

見た目は気にせず、おおよその場所で構いません。全体が固まってからバランスを取りながら配置場所を整えます。

上記画像のようにラベルとテキストボックスとコマンドボタンを配置し、見栄えの為にフレーム(Frame)も配置しました。

そしてユーザーフォーム・ラベル・フレーム・オプションボタンコマンドボタンそれぞれの名前(キャプション)も変更しました。

名前(キャプション)の変更って?方は、次に行く前にコントロールプロパティ設定方法を参考にして下さいね。

キャプションを変更したらテキストボックスの入力規制も設定しておきましょう。これをやっておくと、名前は全角かな・郵便番号は半角英数に常になっているので便利です。

スポンサーリンク


見た目はできましたね。

住所録入力フォームを使ってみよう

ユーザーフォームでつくった住所録入力フォームを使って実際にワークシートに自動的に転記して使うのですが、一番肝心のコマンドボタンの設定をしていませんのでまだ使えません。

このコマンドボタンに様々な指示を記憶させなくてはいけないのです。

記憶させれば1回押すだけで入力終わっちゃうぞ

コマンドボタンにはこんな指示が記録されています。

  1. コマンドボタンを押したら下記の作業を行う
  2. 名前が入力されると顧客番号を自動的に連番を付与する
  3. 名前などのテキストボックスの内容を入力済み最終行を探して一つ下のセルに入力する
  4. 指示された列にそれぞれ入力する

では、実際にはこのように記録しています。

Private Sub CommandButton1_Click()
If Range(“B1”).Offset(1).Value = “” Then
Range(“B1”).Offset(1).Value = TextBox1.Value
Range(“B1”).Offset(1, 1).Value = TextBox2.Value
Range(“B1”).Offset(1, 3).Value = TextBox3.Value
Range(“B1”).Offset(1, 4).Value = TextBox4.Value
Range(“B1”).Offset(1, 5).Value = TextBox5.Value
Range(“B1”).Offset(1, 6).Value = TextBox6.Value
Range(“B1”).Offset(1, 7).Value = TextBox7.Value
If OptionButton1.Value = True Then
Range(“B1”).Offset(1, 2).Value = OptionButton1.Caption
ElseIf OptionButton2.Value = True Then
Range(“B1”).Offset(1, 2).Value = opttionbutton2.Caption
End If
Else
Range(“B1”).End(xlDown).Offset(1).Value = TextBox1.Value
Range(“C1”).End(xlDown).Offset(1).Value = TextBox2.Value
Range(“E1”).End(xlDown).Offset(1).Value = TextBox3.Value
Range(“F1”).End(xlDown).Offset(1).Value = TextBox4.Value
Range(“G1”).End(xlDown).Offset(1).Value = TextBox5.Value
Range(“H1”).End(xlDown).Offset(1).Value = TextBox6.Value
Range(“I1”).End(xlDown).Offset(1).Value = TextBox7.Value
If OptionButton1.Value = True Then
Range(“D1”).End(xlDown).Offset(1).Value = OptionButton1.Caption
ElseIf OptionButton2.Value = True Then
Range(“D1”).End(xlDown).Offset(1).Value = OptionButton2.Caption
End If
End If
Dim i As Integer
i = 1
Do While Cells(i + 1, “B”).Value <> “”
Cells(i + 1, “A”).Value = i
i = i + 1
Loop
End Sub
スポンサーリンク

  1. コマンドボタン(CommandButton1)がクリックされたら
  2. もし、セルB1の1つ下のセルが空白の場合 ※If開始
  3. セルB1の1つ下のセルに名前(TextBox1)を入力する
  4. セルB1の1つ下で1つ右のセルにフリガナ(TextBox2)を入力する
  5. セルB1の1つ下で3つ右のセルに郵便番号(TextBox3)を入力する
  6. セルB1の1つ下で4つ右のセルに住所(TextBox4)を入力する
  7. セルB1の1つ下で5つ右のセルに生年月日(TextBox5)を入力する
  8. セルB1の1つ下で6つ右のセルに電話番号(TextBox6)を入力する
  9. セルB1の1つ下で7つ右のセルにメールアドレス(TextBox7)を入力する
  10. もし性別が男性(OptionButton1)ならば ※If開始
  11. セルB1の1つ下で2つ右のセルに男性(OptionButton1)を入力する
  12. それ以外で性別が女性(OptionButton2)ならば
  13. セルB1の1つ下で2つ右のセルに女性(OptionButton2)を入力する
  14. セルB1が空白以外の場合
  15. セルB1セルを基準とした最終行を探して一つ下のセルに名前(TextBox1)を入力
  16. セルC1セルを基準とした最終行を探して一つ下のセルにフリガナ(TextBox2)を入力
  17. セルE1セルを基準とした最終行を探して一つ下のセルに郵便番号(TextBox3)を入力
  18. セルF1セルを基準とした最終行を探して一つ下のセルに住所(TextBox4)を入力
  19. セルG1セルを基準とした最終行を探して一つ下のセルに生年月日(TextBox5)を入力
  20. セルH1セルを基準とした最終行を探して一つ下のセルに電話番号(TextBox6)を入力
  21. セルI1セルを基準とした最終行を探して一つ下のセルにメールアドレス(TextBox7)を入力
  22. もし性別が男性(OptionButton1)ならば
  23. セルD1セルを基準とした最終行を探して一つ下のセルに男性(OptionButton1)を入力
  24. それ以外で性別が女性(OptionButton2)ならば
  25. セルD1セルを基準とした最終行を探して一つ下のセルに女性(OptionButton2)を入力
  26. Ifステートメントを終了
  27. Ifステートメントを終了
  28. 回数を数える為の整数の変数「i」を宣言
  29. 変数「i」に連番初期値として「1」を入れる
  30. 名前入力の行(B列)i+1行目のセルが空白でない時
  31. 顧客番号(A列)i+1行目のセルの値を入力する
  32. iに1を加える
  33. 「8」~「10」の作業を繰り返す
  34. 終了

注意事項
このVBAコードで、テキストボックスに1つでも空欄があると各項目の最終行が異なってしまう為に、次に新規登録ボタンを押したときに当然ながら最終行がずれてしまいます。

エラー表示は出ていませんが・・・

VBAコードで対処法があると思いますが、まだそこまで私自身が対応できていないので、何か文字を入れれば解決できるので、数字で「0」を入力して最終行のズレを防いでいます。

対処法が見つかったら追記します。

これで、住所録入力フォームが出来上がりました。

このままでも住所録入力フォームとして使えるのですが、エラーの防止と続けて新規入力を便利にする為の追加コードを加えましょう。

住所録フォームを快適に使う為に

エクセルVBAで住所録新規入力フォームを作って、自分自身で入力をしていた時には全く問題なかったのですが、職場の同僚に作業を引き継いだら「エラーが出た」って言われました。

なぜ?

理由がわからない。自分で入力作業を行っていた時には何の問題もなく快適にできていたのに・・・

調べても原因が分からなかったのですが作業を行っていたスタッフに聞いたところ、名前を未入力のまま誤って【新規登録】ボタンを押してしまった為に、名前と住所などの行がずれてしまったようでした。名前以外の8項目のテキストボックスには、初期値として「0」が自動表示されるようになっているので、入力漏れでもセルには「0」が転記されるので行のずれを防ぐようにしていたのですが、

まさか・・・

名前を入力忘れるなんて考えていませんでした。よって名前が空欄になるけど、他の8項目は入力されるので行がずれてしまったという事でした。

エラーを防ぐために追加したコード

前のページでお話しした、コードの先頭に「名前が空欄の時は処理を中止してメッセージを表示する」というコードを付け加えます。

If UserForm1.TextBox1 = “” Then
MsgBox (“名前が入力されていません”)
Else

上記コードは、Private Sub CommandButton1_Click()のすぐ下に挿入します。そして、最後のEnd Subのすぐ上の行にEnd Ifを追加します。

これで、名前入力漏れでも行がずれるエラーは防げるようになりました。次に名前など入力して、新規登録ボタンを押すと名前だけ空欄にして、残りの項目は「0」が入っている状態に戻します。こうするとわずかな事ですが名前を消す手間が省けます。

挿入する場所は、先ほど追加したEnd Ifのすぐ上に【TextBox1.Value = “”】を追加するだけです。

残りのテキストボックス2には【TextBox2.Value = “0”】をあとは繰り返すだけです。

入力ができるようになったら、検索して修正もしたくなりますよね。
検索や修正ができないと、実用的でないので検索・修正フォームを次に作りましょう。

各コントロールまとめて転記シリーズ

ユーザーフォームを使うと配置したコントロール(テキストボックスやラベルなど)とセルとの間で値のやり取りが多くなります。

よく使うのでまとめてみました。

値の場所転記したい場所説明ページ
セルの値ラベル名VBAコード
セルの値テキストボックスの値VBAコード
セルの値コマンドボタンの表示名VBAコード
セルの値セルの値VBAコード
テキストボックス入力値セルの値VBAコード
波乗りアヒル
波乗りアヒル

成績表作成には知っておきたいExcel-VBAもくじ

ボウリングハンデキャップのつけ方
HDCPの種類はリーグのルールにより複雑に ボウリングの成績表(リーグ戦)で必ず必要になるのが、ハンデキャップ(HDCP)です。 ボウリングご予約の時に一番多く使われているのが、女性にだけハンデキャップをつけてあげることが多いですね。 だいだい何点ぐらいのハンデキャップが多いの?
ボウリングリーグ対戦表の数字を名前に自動変換する方法
リーグ戦の対戦表をエクセルで作る時、数字で表されている番号を名前に自動的に変換できると作業時間が短くなり効率的ですね。たった2つのエクセル関数を使うだけで、簡単にできるんです。今回の回戦数を入力するだけで一発変換完了。もちろんお客様にスケジュール対戦表として渡すことも簡単ですよ。
人数に応じてリーグ対戦表を自動選択する方法
複数の表があり、条件に合った表を選んで、さらにセルの値を選んで数字を名前に自動変換できると、とても便利なエクセルができますよね。ボウリングのリーグ対戦表も人数により表が異なるので、参加人数が決まったら、自動的に表を選んで対戦番号がこれも自動的に参加者名に変換されると便利で作業効率がアップして時間短縮になりますよ。
波乗りアヒル
波乗りアヒル

エクセルの勉強お疲れ様です。ちょっとひと休みしませんか?

タイトルとURLをコピーしました