エクセルVBAで住所録検索してリストを修正するユーザーフォームを作る

VBA

スポンサーリンク


スポンサーリンク


スポンサーリンク

名前検索フォームをつくろう

住所録入力ができるようになり実際に使う時には、住所録の中から名前を探したり、住所の変更などの修正をすることがあります。

エクセル検索と置換
マウスでスクロールして目的の名前を探すこともできますし、エクセルの標準機能の「ctrl」+「F」を押して検索と置き換えを使うこともできますが、「エクセルできます!」って言う従業員でもこのエクセルの検索機能を知らないなんてことがあります。

その為、説明するのも面倒なので、ユーザーフォームを使ってオリジナル検索・修正フォームをつくっておけばそのような手間もなく仕事がスムーズに効率よくできるので便利です。

検索項目を決めよう

このページでご紹介している住所録は全部で9項目「顧客番号」「名前」「生年月日」「性別」「郵便番号」「住所1」「住所2」「電話番号」「メードアドレス」を作成していますが、実際検索で使うのは、どれでしょうか?
前のページ(住所録入力フォームを作ろう)からの続きのページとなってます。

人により、または、使用用途により多少異なると思いますが、顧客番号・名前・電話番号の3つぐらいが一番多いと思います。

特に今の時代は、ポイントカードを扱っているお店も多く顧客を番号管理していますよね。大企業やある程度の店舗数・規模を持ったお店ならばPOSシステムや顧客管理ソフトを使って管理されていると思いますが、小規模の店舗などでは、エクセルで管理しているところも多いと思います。

と、言う事で、まずは顧客番号から名前などを検索できるようにできる検索フォームを作りましょう。

今回作成したのは、顧客番号を入力すると検索する個人住所録データが表示されて、同時に修正できるフォームを作成してみました。

スポンサーリンク



イメージは下記のような感じです。

  1. 顧客番号を入力して検索ボタンを押す
  2. 未登録の場合は新規登録フォームへ
  3. 入力済みの住所録リストから該当する番号を探し出す
  4. 番号を見つけたら、名前・フリガナ・性別・郵便番号・住所・生年月日・電話番号・メールアドレスの全てを表示
  5. 変更したい部分を上書きして住所録リストへ戻す

ここまでできるようになるまでずいぶん時間かかりました・・・エクセル初心者でも根気と時間を掛ければ、何とかなるものですね。

顧客番号から住所録を検索・修正するユーザーフォーム

検索・修正ユーザーフォーム

ユーザーフォームに必要な項目を配置します。当然なのですが、新規登録フォームと項目は一緒です。違いは、新規入会フォームでは自動連番を付与するようになっているので必要なかったのですが、検索・修正フォームでは必要になるので追加しています。

また、性別の項目がオプションボタンからテキストボックスに変わっています。

シンプルに考えると新規入会住所録フォームでセルに転記した内容を検索修正フォームで、そのセルの値を表示させるだけなのですもんね。

それでは、顧客番号を入力して検索ボタンを押したらユーザーフォーム上に表示されるVBAコードをご紹介します。

検索ボタンを押したら探したい人の住所などが表示

実際に使っている【検索ボタン】って勝手に読んでますが・・・このようなVBAコードを書いています。

Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
Range(“AA2”).Value = TextBox1.Value
Dim mycell As Range
Set mycell = Range(“A2:A103”).Find(What:=Range(“AA2”).Value, LookAt:=xlWhole)
If Not mycell Is Nothing Then
mycell.Select
With UserForm2
For i = 2 To 9
UserForm2.Controls(“TextBox” & i).Value = mycell.Offset(, i – 1).Value
Next i
End With
Else
MsgBox “未登録番号です。新規登録フォームへ移動します”
Unload UserForm2
UserForm1.Show
Exit Sub
End If
Application.ScreenUpdating = True
End Sub
スポンサーリンク


前提条件として
まず、顧客番号が【A列】に自動連番で付与されていますのでこの番号を検索するとして、顧客数が最大100人以内にします。200人の人数がいる場合はA103をA203にすればいいのですよ

検索したい番号は住所録リストに影響が出ないセルに表示させる(ここではセルAA2)

それでは、1行ずつお話しします。

  1. コマンドボタン(Commandbutton1)を押したら
  2. 画面表示の更新を止めてちらつき防止と処理速度を上げる
  3. セル(AA2)にテキストボックス(TextBox1)の値を転記する
  4. 変数「mycell」を宣言
  5. mycellは「A2~A103」の範囲の中にありセル「AA2」に記載された値を探す
  6. もしmycellを見つけたら
  7. 見つけたmycellのセルを選ぶ(セルがクリックされて文字入力できる状態)
  8. ユーザーフォーム(UserForm2)を指定(Withステートメント開始)
  9. 指定した回数だけ処理を繰り返すForを使い(変数はi)で2から9を指定
  10. UserForm2に配置したコントロールのTextBox2の値は、見つけたmycellの2つ右のセル-1(1つ右になる)を選んで転記する
  11. TextBox9まで繰り返す
  12. Withステートメント終了
  13. もし該当する番号が無かったら
  14. メッセージで「未登録番号です。新規登録フォームへ移動します」を表示する
  15. UserForm2の表示を消す
  16. UserForm1を表示する
  17. 処理終了
  18. Ifステートメント終了
  19. 画面表示の更新を止めるのをやめて元に戻す
  20. 終了

これで検索ボタンを押すと、見つけたい番号から顧客情報が表示されます。

顧客番号検索・修正2

ただこの状態では、番号検索をして表示しただけです。ここで修正したい項目を修正入力して元データを書き換えましょう。

次に画像の右側にある修正実行ボタンの設定を次にしましょう。

データを書き換える修正ボタンの設定

前のページで番号検索して見つけ出した住所録の各項目。

このデータで修正したい項目を入力修正して元データに戻してあげるのに【修正ボタン】を設定しましょう。

【修正ボタン】には下記のようなVBAコードを書いています。

Private Sub CommandButton2_Click()
With UserForm2
For i = 2 To 9
ActiveCell.Offset(, i – 1).Value = UserForm2.Controls(“TextBox” & i).Value
Next i
End With
Dim j As Long
With UserForm2
For j = 1 To 9
UserForm2.Controls(“TextBox” & j).Value = “”
Next j
End With
End Sub

それでは、1行ずつお話しします。

  1. コマンドボタン(CommandButton2)を押したら
  2. ユーザーフォーム(UserForm2)の処理をします(Withステートメント開始)
  3. 繰り返し処理します。変数「i」は2から9
  4. 選択されているセル(見つけた番号のセル)の1つ右のセルに変数「i」-1のセルにUserForm2のコントロールのテキストボックス「変数i」の値に書き換える
  5. 2~9まで繰り返す
  6. Withステートメント終了
  7. 整数の変数を宣言
  8. ユーザーフォーム(UserForm2)の処理をします(Withステートメント開始)
  9. 繰り返し処理をします。変数「j」は1から9
  10. UserForm2のコントロールのテキストボックス「変数j」の値を空欄にする
  11. 1~9まで繰り返す
  12. Withステートメント終了
  13. 終了

これで、修正ボタンが機能するようになります。

⑦以下に関してはおまけですね。修正が終わればテキストボックスに文字などは必要ないので消しちゃいましょうっていうコードですもんね。

最後に、【MENU】ボタンには、このエクセルを開いた時に新規入力フォームと修正フォームのどちらを選ぶか可能になるユーザーフォームが開くようになっています。

また、まだ知識不足なので、郵便番号を入力すると自動的に都道府県と市町村と地区名がテキストボックスに表示されるようにしたいのですが、かなりレベルが高くてまだできない状態です。その為に郵便番号が検索できるホームページへリンクしているだけなのです。

いつかはチャレンジしたいですね。

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

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

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

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

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

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

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