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

住所録入力フォーム作成事例

数百人規模の中規模の住所録や顧客データをエクセルで管理している事業所って意外と多いですが、データを検索して修正するのってすごく面倒じゃないですか?

面倒だなあって思っているエクセルの住所録を自分だけが使うだけならば問題はないのですが、みんなで入力や修正を行うと、全角や半角など他の人が使うとなると時々問題が発生した経験ってありませんか?

入力も正確に、検索も修正も簡単にして作業効率アップしたいですよね。

ユーザーフォームで入力も検索も修正も瞬間技

エクセルデータでのトラブル問題は、作成者が意図しない入力方法や、書式設定や数式を入れてあるセルを消されてしまって正しく表示されなくなってしまうことなど。

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

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

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

ところでユーザーフォームって何?って思った方、まずはこちらを見ていただくとユーザーフォームを使ってオリジナル入力フォーム作りたくなりますよ!

スポンサーリンク

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

まず、今使っている住所録をそのまま使うのが一番いいのですが、ユーザーフォームを使うという事はEXCEL-VBAを使うという事なので、入力やデータ検索の為に実際にやってみて思ったことを書き出してみました。

今使っている住所録を確認してみよう

作成済み住所録を修正しよう
  1. 結合セルは使わない
  2. フリガナは苗字と名前を別々のセルに分ける
  3. 電話番号はハイフンを入れない
  4. 誕生日などの日付は和暦を使わず西暦を使う

1、結合セルは使わない

見た目などを優先するとセルを結合して表示してしまえば便利で簡単なのですが、データの検索や書き換え(修正)、追記などを行う住所録などではエラーの原因になるので使わない方がいいです。

データの検索や修正などをExcel-VBAで行うと、セルの番地が不明確になったり検索する基準から右に5個目のセルの値を修正なんて事が結合されているとできません。

また、並べ替えもできなくなるので、一旦結合セルを解除してデータ修正などしてから再度結合してなんて大変なので、無駄なExcel-VBAコードは使わない方が処理も早くなるので結合セルは使わないことをおススメします。

それでもどうしも使わなくてはいけない場面ってあるので、このようにVBAコードを組み合わせれば問題解決しますよ。

2、フリガナは苗字と名前を別々のセルに分ける

住所録では名前の検索ができなければ、わざわざExcel-VBAを覚えて ユーザーフォームを使って やる必要はありません。

検索する時に名前のフルネームを漢字で検索せずに、カタカナの苗字で探しませんか?

そうするとフリガナで名字だけ入力すれば、該当リストが表示されて選択すれば検索完了しますので、フリガナの苗字と名前を分けておくと検索に便利になったのでおススメですよ。

3、電話番号はハイフンを入れずに入力

これはフリガナ検索でうまく検索できない時、例えば「中田さん」はナカタ?ナカダ?どちらで登録したか分からない時ありますよね。

そうすると、電話番号ならば間違えないので電話番号検索を付け加えたのですが、入力するスタッフによりハイフン付ける人と付けに人がいると検索ができなくなりますので、数字だけにした方がいいですよ。

4、誕生日などの日付は和暦ではなく西暦を使う

生年月日や入会日・更新日などの日付入力は「昭和」「平成」「令和」などの和暦を使っていますか?それとも西暦を使っていますか?

もちろんどちらが正しいなんてことないのですが、誕生日の計算などを数式で自動計算させるならば、西暦の方がシンプルに簡単で、今日の日付から誕生日を引けば年齢が計算できますからね。

その為に入会用紙などには誕生日を西暦で書くように促しているのですが、和暦で書く人意外といますよね。

西暦に直すのが結構面倒なので、エクセル関数とVBAを組み合わせてユーザーフォームに和暦を西暦に変換表示させる機能を付け加えておくと便利ですよ。

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

もし今使っている住所録が上記に合致していたらそのまま使えそうなので、ユーザーフォームで簡単・便利に改良しましょう。

違っていてもこの4つぐらいならば修正できそうじゃないですか?

住所録の項目をワークシートに作ろう

せっかくユーザーフォームを使って住所録を簡単・便利に作り変えるのだから、既存の住所録を改良して使おうって思いますよね。

最初から作るつもりで見てもらえると、分かりやすいかな?また、修正してユーザーフォームだけ追加するならば、ここはクリアしているって思えば次へ進んでくださいね。

エクセルで作る住所録ワークシート
画像クリックで拡大します

エクセルで作る住所録のワークシートは全部で15項目ありますが、人それぞれによって不必要な項目や差し替えなければならない項目などがあると思いますので、ご自由に設定して下さいね。

ここまでの作業は全く難しくないですが、簡単便利に入力・検索・修正するためにユーザーフォームを使うのですが、全てをユーザーフォームで行う必要はありません。

エクセル関数とExcel-VBAを上手に組み合わせた方が簡単で、作る時間も短縮できました。

エクセル関数で行った方が簡単な項目
  1. 誕生月
  2. 更新状況
  3. 年齢

1、誕生月

お誕生月にクーポンを郵送しているので「誕生月」という項目を使っていて、生年月日(セルAI列)から「月」を取り出して表記するエクセル関数「MONTH」を使っています。

生年月日が空欄だとなぜか1と表示されてしまうので、IF関数で空欄処理を付け加えるとこのようなエクセル数式になります。

誕生月で使うエクセル数式

=IF(AI2=””,””,MONTH(AI2))

2、更新状況

住所の確認などの為に毎年1回更新手続きを行っているので、更新手続きを行っていただいたお客様と未更新のお客様を識別するために「更新状況」という項目を使っていて、今年度の基準日よりも更新日の方が新しい(数値が大きい)時は「更新済み」、古い(数値が小さい)時は「未更新」とそれぞれ表示します。

更新日が空欄だと”未更新”と表示されてしまうので、IF関数で空欄処理を付け加えるとこのようなエクセル数式になります。

更新状況で使うエクセル数式

=IF(AC2=””,””,IF(AJ2>=$S$2,”更新済”,”未更新”))

3、年齢

実際のところ年齢が分かったからと言って何かに使っているわけではないのですが、一般的に年齢という項目ってあるので用意して、「日付の差」を計算するエクセル関数「DATEIF」を使っています。

誕生日が空欄だと1900年を基準にされてしまうので、2019年だと119歳って表示されてしまうので、ここでもIF関数で空欄処理を付け加えるとこのようなエクセル数式になります。

年齢算出で使うエクセル数式

=IF(AC2=””,””,DATEDIF(AI2,$P$2,”Y”))

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

これ以外の12項目は全てユーザーフォームで入力した値が自動的にそれぞれの項目に自動転記されるようになっています。

日付の基本項目を作っておく

誕生日・更新状況・年齢をエクセル関数で設定するうえで、今日の日付や更新基準日などが無いと計算できないので準備しましょう。

住所録データはAA列から使っていてその左側P列~S列に設定

セルP2が「2019/8/27」と表示していますが、エクセル関数TODAYで現在日時を自動表示させて、セルQ2で「年」を取り出して表示、セルR2で月を取り出しています。

またセルS2は更新基準日を入れて更新状況を比較できるようにしています。

さあ、いよいよ本番です。入力フォーム作りましょう!

スポンサーリンク

なぜ必要?住所録入力フォーム

そもそも、ワークシートに直接顧客情報を入力するのがダメなの?って思う方は、このページにたどり着いていないと思います。

新規入力や検索・修正などで探すにが面倒だとか大変だと思い、エクセルの作業時間短縮して仕事効率化したいからじゃないでしょうか?

私自身最初に作った入力フォームはこんな感じだったのですが・・・

住所録入力フォーム
最初に作った住所録入力フォーム

新規入力や検索など使っているスタッフからのリクエストなどを次から次へと取り入れて行ったら、こんな感じになっちゃいました。

住所録入力フォーム
改良した住所録入力フォーム 備考欄はメールアドレスに改良します

この入力フォームで新規登録ボタンを押せば、住所録リストの最終行に新規追加登録され、修正登録ボタンを押せば、該当者を探して見つけたらそれぞれの項目を上書きするようになります。

更に顧客番号が分からない時などは「検索」で電話番号検索フリガナ検索ができるようになり、生年月日が和暦で記載されている時は西暦を調べることもできるようになりました。

スポンサーリンク

【顧客番号確定】ボタンにVBAコードを登録しよう

ユーザーフォームの基本でもあるプログラムを実行させるためにコマンドボタンに作業を登録しなくてはいけません。

ここでは、顧客番号を入力して顧客番号確定ボタンを押すと、未登録番号ならば新規登録へ、登録済み番号ならば修正へ処理を振り分ける作業をVBAコードを使って登録しておきます。

【顧客番号確定】ボタンで実行するVBA作業
  1. 誤って顧客番号確定ボタンが押された時のエラー回避メッセージ表示
  2. 未登録番号か登録済み番号かを判別させる
  3. 未登録番号時の新規登録の確認メッセージ表示
  4. 登録済みの時はデータを探してフォームに表示して確認メッセージを表示
  5. 上記1~4の作業を行う時に画面が作業中チラつくので、ちらつき防止処理をする

ボタンを1回押すだけでこれだけの作業を一瞬で行ってくれるので、探す手間もなくなりますので仕事作業の効率が大幅にアップしますよ!

1、誤って顧客番号確定ボタンが押された時のエラー回避メッセージ表示

顧客番号を入力するテキストボックス(TextBox1)に顧客番号を入力して顧客番号確定ボタン(CommandButton1)を押すのが当たり前なのですが、うっかりテキストボックスに何も入力しないで押してしまった時に、メッセージを表示してあげると親切ですし、誤った操作という事をお知らせできるので設定することをおススメします。

誤クリックしたらメッセージを表示する

If UserForm1.TextBox1.Value = “” Then
MsgBox (“会員番号が入力されていません”)
End If

2、未登録番号か登録済み番号かを判別させる

顧客番号検索用に準備します

【顧客番号確定】ボタンを押すと、 TextBox1に入力されている顧客番号がセルP11に転記され、VLOOKUP関数により住所録リストより探してセルP12に表示させます。

登録時は「名前」未登録時は「0」を表示で判別

=IFERROR(VLOOKUP(P11,AB2:AD1000,2,0),0)

セルP12の検索結果により未登録番号か登録済み番号化を判別させます。

未登録・登録済みを自動判別

Range(“P11”).Value = UserForm1.TextBox1.Value
If Range(“P12”).Value = 0 Then

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

ここでは数式をセルに入力していますが、1つの検索条件で複数のセルの値を探すときは、Findメソッドを使う方法もありますので覚えておくと非常に便利ですよ。

3、未登録番号時の新規登録の確認メッセージ表示

未登録番号か登録済み番号かを判別させた結果、未登録番号だった時の処理ですが、上の続きになりますが、( If Range(“P12”).Value = 0 Then)検索結果が名前でなく”0”だった時はメッセージを出すという事ですね。

メッセージを出す

MsgBox (“未登録番号です。新規登録します”)

4、登録済みの時はデータを探してフォームに表示して確認メッセージを表示

顧客番号が登録済みの場合は、名前・フリガナなどの登録済みデータリストから探し出して各テキストボックスに表示させます。

そして、メッセージで「登録済番号です。修正実行しますか?」と表示して「はい」「いいえ」で選択させて、「はい」を選べば、各項目を修正可能になり「いいえ」を選ぶと入力フォームを閉じてメインメニューフォームに表示が切り替わります。

データ読み込み

Dim myRange As Range
Set myRange = Range(“AB2:AB1001”).Find(What:=Range(“P11”).Value, LookAt:=xlWhole)
If Not myRange Is Nothing Then
myRange.Select
UserForm1.TextBox2.Value = myRange.Offset(, i + 1).Value
UserForm1.TextBox3.Value = myRange.Offset(, i + 2).Value
UserForm1.TextBox4.Value = myRange.Offset(, i + 3).Value
UserForm1.TextBox5.Value = myRange.Offset(, i + 4).Value
UserForm1.TextBox6.Value = myRange.Offset(, i + 5).Value
UserForm1.TextBox7.Value = myRange.Offset(, i + 6).Value
UserForm1.TextBox8.Value = myRange.Offset(, i + 7).Value
UserForm1.Label28.Caption = myRange.Offset(, i + 11).Value
msg = MsgBox(“登録済番号です。修正実行しますか?”, Buttons:=vbYesNo + vbExclamation)
If msg = vbYes Then
Else
Unload UserForm1
End If
End If

上記1~4の作業を行う時に画面が作業中チラつくので、ちらつき防止処理をする

【顧客番号確定】ボタンを押すと、ここまで記載した処理を順番に一瞬で行ってくれるのですが、その処理の度にカーソルが処理をしているセルの位置に実際は移動しています。

このままだと、画面が移動に合わせてあちこち移動するので画面がチラつき、見苦しい!

そこでちらつき防止処理するVBAコード” Application.ScreenUpdating ”の前後に入れましょう。

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

これで【顧客番号確定】ボタンのVBAコードの設定が終わりました。

下記が実際のVBAコードで、コピペして使っていただいても構いませんよ

顧客番号確定ボタン登録のVBAコード

Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
If UserForm1.TextBox1.Value = “” Then
MsgBox (“会員番号が入力されていません”)
Else
Range(“P11”).Value = UserForm1.TextBox1.Value
If Range(“P12”).Value = 0 Then
MsgBox (“未登録番号です。新規登録します”)
Else
Dim myRange As Range
Set myRange = Range(“AB2:AB1001”).Find(What:=Range(“P11”).Value, LookAt:=xlWhole)
If Not myRange Is Nothing Then
myRange.Select
UserForm1.TextBox2.Value = myRange.Offset(, i + 1).Value
UserForm1.TextBox3.Value = myRange.Offset(, i + 2).Value
UserForm1.TextBox4.Value = myRange.Offset(, i + 3).Value
UserForm1.TextBox5.Value = myRange.Offset(, i + 4).Value
UserForm1.TextBox6.Value = myRange.Offset(, i + 5).Value
UserForm1.TextBox7.Value = myRange.Offset(, i + 6).Value
UserForm1.TextBox8.Value = myRange.Offset(, i + 7).Value
UserForm1.Label28.Caption = myRange.Offset(, i + 11).Value
msg = MsgBox(“登録済番号です。修正実行しますか?”, Buttons:=vbYesNo + vbExclamation)
If msg = vbYes Then
Else
Unload UserForm1
UserForm4.Show
End If
End If
End If
End If
Application.ScreenUpdating = Ture
End Sub

スポンサーリンク

【郵便番号検索】ボタンにURLを登録しよう

先ほどお話しした通り、郵便番号を入力すると住所が表示されるようにするにはとても大変なので、手っ取り早く郵便番号が分からない時の為に【郵便番号検索】ボタンを押すと郵便局のホームページを開いて郵便番号が検索できるようにしましょう。

ここでは、 https://tripbowl.com/excel-vba/link/ が開くように登録します

指定したホームページを開く

Private Sub CommandButton2_Click()
ActiveWorkbook.FollowHyperlink Address:=”https://www.post.japanpost.jp/smt-zipcode/”
End Sub

【新規登録】ボタンのVBAコード

住所録リストに新規登録をするのにはリストの最終行を探してから追加で入力作業を行いますので、マウスで画面スクロールさせて探す手間を省くには必須で、オリジナル入力フォームでこのような無駄な作業時間をカットできますので、ぜひ作りましょう。

【新規登録】ボタンで実行するVBA作業
  1. 誤って顧客番号確定ボタンが押された時のエラー回避メッセージ表示
  2. 顧客番号が重複していないかのチェックと重複時のエラーメッセージ表示
  3. 新規登録のデータを1行目に登録
  4. 1行目にデータが入力済みの場合は入力最終行を探してデータ登録
  5. 登録数を自動連番する

最初に作ったエクセル住所録では”2番”の重複番号チェックが無かった為に、同じ番号が2つリストにできてしまって、データの修正する時に検索エラーになったことがあったので、この重複チェックは必須だと思い知らされました。

また、”5番の”登録数の自動連番は今のところ(2年ぐらい経ちますが)使うことが無いので無くてもいいと最近は思っています。

1、誤って【顧客番号確定】ボタンが押された時のエラー回避メッセージ表示

【顧客番号確定】ボタンの時と同じで、入力するテキストボックス(TextBox1)に顧客番号を入力して【顧客番号確定】ボタン(CommandButton1)を押すのが当たり前なのですが、うっかりテキストボックスに何も入力しないで押してしまった時に、メッセージを表示してあげると親切ですし、誤った操作という事をお知らせできるので設定することをおススメします。

誤クリックしたらメッセージを表示する

If UserForm1.TextBox1 = “” Then
MsgBox (“会員番号が入力されていません”)

2、顧客番号が重複していないかのチェックと重複時のエラーメッセージ表示

先ほどもお話しした追加で作った項目で、うっかり同じ番号を追加で登録してしまうのを防ぐために【新規登録】ボタンを押したら、重複番号でないかをチェックして重複していたらエラーメッセージを表示させます。

まずはワークシート上に上の図のように重複チェック項目を作りエクセル数式を入れておいて、重複しなければ「0」を重複したら「1」を表示させます。

重複チェック用のエクセル数式

=IF(IFERROR(VLOOKUP(P11,AB2:AB999,1,0),0)=0,0,1)

次に【新規登録】ボタンに重複時のエラーメッセージのVBAコードを書き込みます。

重複時のエラーメッセージ

If Range(“S11”).Value = 1 Then
MsgBox (“会員番号が重複しています”)

3、新規登録のデータを1行目に登録

名前など住所録に必要な項目を各テキストボックスに入力して【新規登録】ボタンを押して所定のセルへ転記させますが、住所録リスト最初の1人目の時と2人目以降では処理が異なりますので、ここでは最初の1人目について書きます。

1人目は一般的に2行目になります。(1行目は項目名が記載されています)

左画像は最初に見てもらった画像と同じです

住所録リストで会員番号の列を基準にすると、セルAB1が基準という事になります。

そして最初の1人目なので入力すべきセルはセルAB2になりますが、言い方を変えると、基準のセルAB1の1行下と言い換えることができますよね。

そして氏名はセルAC2に入らなくてはいけないのですが、これも言い方を変えると、基準セルAB1の1列右・1行下と言い換えられますね。

この言い換えを繰り返して、生年月日はセルAI2なのでセルAB1の7列右・1行下ですね。

と、いう事で、このようなVBAコードで1人目を表します。

見出し

If Range(“AB1”).Offset(1) = “” Then
Range(“AB1”).Offset(1).Value = TextBox1.Value
Range(“AB1”).Offset(1, 1).Value = TextBox2.Value
Range(“AB1”).Offset(1, 2).Value = TextBox3.Value
Range(“AB1”).Offset(1, 3).Value = TextBox4.Value
Range(“AB1”).Offset(1, 4).Value = TextBox5.Value
Range(“AB1”).Offset(1, 5).Value = TextBox6.Value
Range(“AB1”).Offset(1, 6).Value = TextBox7.Value
Range(“AB1”).Offset(1, 7).Value = TextBox8.Value
Range(“AB1”).Offset(1, 11).Value = Label28.Caption
Range(“AB1”).Offset(1, 8).Value = Cells(2, 16).Value
If OptionButton4.Value = True Then
Range(“AB1”).Offset(1, 9).Value = (“男”)
ElseIf OptionButton5.Value = True Then
Range(“AB1”).Offset(1, 9).Value = (“女”)
End If

4、1行目にデータが入力済みの場合は入力最終行を探してデータ登録

これも簡単に言うと2人目以降の事で、先ほどは基準セルに対して何列目・1行下としましたが、2日目以降は何列目かは分かりません

そこで入力済みの最終行の1行下に追加登録をするようにVBAコードもおかないと、いつまでたっても上書きされてリストは増えていきません。

2人目以降の入力済み最終行の下に追加する

Range(“AB1”).End(xlDown).Offset(1).Value = TextBox1.Value
Range(“AC1”).End(xlDown).Offset(1).Value = TextBox2.Value
Range(“AD1”).End(xlDown).Offset(1).Value = TextBox3.Value
Range(“AE1”).End(xlDown).Offset(1).Value = TextBox4.Value
Range(“AF1”).End(xlDown).Offset(1).Value = TextBox5.Value
Range(“AG1”).End(xlDown).Offset(1).Value = TextBox6.Value
Range(“AH1”).End(xlDown).Offset(1).Value = TextBox7.Value
Range(“AI1”).End(xlDown).Offset(1).Value = TextBox8.Value
Range(“AM1”).End(xlDown).Offset(1).Value = Label28.Caption
Range(“AJ1”).End(xlDown).Offset(1).Value = Cells(2, 16).Value
If OptionButton4.Value = True Then
Range(“AK1”).End(xlDown).Offset(1).Value = (“男”)
ElseIf OptionButton5.Value = True Then
Range(“AK1”).End(xlDown).Offset(1).Value = (“女”)
End If
End If

5、登録数を自動連番する

実用性があるかどうかわかりませんが、使ってはいますがあまり使っていない気がするのでいらないかもしれませんね。

自動連番は、会員番号が登録されると自動的に登録数をカウントアップして記載するコードです。

見出し

Dim i As Integer
i = 1
Do While Cells(i + 1, “AB”).Value <> “”
Cells(i + 1, “AA”).Value = i
i = i + 1
Loop

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