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

スポンサーリンク

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

スポンサーリンク

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

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

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

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

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

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

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

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

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

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

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

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

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

スポンサーリンク

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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