MENU
ゼロから始めるExcel-VBA
  1. ホーム
  2. 作成事例
  3. エクセルVBAで住所録検索してリストを修正するユーザーフォームを作る

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

作成事例
住所録入力フォーム
波乗りアヒル
波乗りアヒル

このページは「エクセルVBAで住所録入力フォームを作る」ページの続きとなり、入力データの修正に関するページとなります。

あわせて読みたい
エクセルVBAで住所録入力フォームを作る方法 数百人規模の中規模の住所録や顧客データをエクセルで管理している事業所って意外と多いですが、データを検索して修正するのってすごく面倒じゃないですか？ 面倒だなあ...

住所録の修正が一番面倒で時間がかかる作業ですよね。

いちいち画面スクロールして目的の顧客を探して、見つけたら修正してまた戻るって作業は時間の無駄なので、ユーザーフォームを使って一瞬で終わらせてしまいましょう。

前ページでも見ていただいた住所録フォーム、以前は新規登録フォームと修正フォームを別々にしていたのですが、多少経験値が上がったのかな？まとめることが出来たので、新しくこのページも修正していきます。

目次

住所録を一瞬で修正するオリジナルフォーム

それでは続きを書いていきます。

【修正登録】ボタンにVBAコードを登録しよう

基本的に新規登録と修正登録の操作方法を同じにして、未登録番号ならば新規登録ができるようになり、登録済み番号ならば既存データを探して見つけたらフォーム上に自動表示されて確認・修正をして修正登録ボタンを押せば、データが元の場所に上書きされるようになっています。

【修正登録】ボタンにVBAコードを登録する
  1. 誤って顧客番号確定ボタンが押された時のエラー回避メッセージ表示
  2. 修正登録するかしないかを「はい」「いいえ」で選択させる
  3. 「はい」を選んだらデータを上書きする
  4. 「いいえ」を選んだらフォームを閉じてメインメニューを表示
  5. 上記1～4の作業を行う時に画面が作業中チラつくので、ちらつき防止処理をする

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

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

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

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

If UserForm1.TextBox1 = “” Then
MsgBox (“会員番号が未入力です”)

あわせて読みたい
複数のテキストボックスが空欄の時にメッセージを出す方法 エクセルでユーザーフォームを使ったオリジナルの入力画面はとても便利で作業時間を大幅に削減してくれるので重宝していると思いますが、自分以外の人に使ってもらうと...

２、修正登録するかしないかを「はい」「いいえ」で選択させる

顧客番号がTextBox1に入力されて顧客番号確定ボタンを押したら、登録済み番号の時は自動的に番号に該当する名前などの住所録データが自動的に各テキストボックスに表示されます。

修正を加えたら修正登録ボタンを押すとメッセージで「修正しますか？」と表示されるので「はい」「いいえ」を選択できるようにして、確認できるようにします。

「はい」「いいえ」を選択させるメッセージ

msg = MsgBox(“修正登録しますか？”, Buttons:=vbYesNo + vbExclamation)

あわせて読みたい
メッセージボックスではいといいえを選択させる方法 〇○しますか？と聞いて「はい」「いいえ」で処理を振り分けたいことありませんか？ 通常のVBAのメッセージ（MsgBox）ではOKしかないのですが、はいといいえを表示させて...

３、「はい」を選んだらデータを上書きする

エクセル住所録で一番面倒で時間と手間のかかる引っ越しなどで住所が変わった時に、修正しますが住所録リストの中から該当者を探して書き換える作業です。

この作業を一瞬で終わらせることが作業効率アップの方法なのです。

実は、顧客番号確定ボタンを押した時にカーソルが見えないところで入力した顧客番号の位置に移動しています。

そして現在のカーソルの位置を”ActiveCell“とVBAコードでは表し、ActiveCellを基準にセルの位置と入力データのやり取りを行い、ActiveCell.Offset(,1)で1列右のセルを意味しています。

「はい」を選んだらデータを上書きする

If msg = vbYes Then
ActiveCell.Offset(, 1).Value = UserForm1.TextBox2.Value
ActiveCell.Offset(, 2).Value = UserForm1.TextBox3.Value
ActiveCell.Offset(, 3).Value = UserForm1.TextBox4.Value
ActiveCell.Offset(, 4).Value = UserForm1.TextBox5.Value
ActiveCell.Offset(, 5).Value = UserForm1.TextBox6.Value
ActiveCell.Offset(, 6).Value = UserForm1.TextBox7.Value
ActiveCell.Offset(, 7).Value = UserForm1.TextBox8.Value
If OptionButton4.Value = True Then
ActiveCell.Offset(, 9).Value = “男”
ElseIf OptionButton5.Value = True Then
ActiveCell.Offset(, 9).Value = “女”
End If
End If

４、「いいえ」を選んだらフォームを閉じてメインメニューを表示

データの確認だけで修正箇所が無い場合などは「いいえ」を選んだら住所録フォームを閉じて、メインメニューに必要なデータを読み込んでメインメニューを開きます。

「いいえ」を選んで閉じる

Unload UserForm1
UserForm4.Label1.Caption = Range(“P2”).Value
UserForm4.TextBox1.Value = Range(“S2”).Value
UserForm4.TextBox2.Value = Range(“T2”).Value
UserForm4.Show

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

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

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

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

あわせて読みたい
エクセルが重くなるVBAの処理を高速化して画面ちらつきを防ぐ方法 コマンドボタンをクリックして作業を自動化するとVBAコードを読み取りながら処理をしているので、画面がチョコチョコ移動したり切り替わったり目障りだと思ったことあり...
波乗りアヒル
波乗りアヒル

これで顧客番号確定ボタンのVBAコードの設定が終わりました。 下記が実際のVBAコードで、コピペして使っていただいても構いませんよ

修正登録ボタンに登録のVBAコード

Private Sub CommandButton3_Click()
Application.ScreenUpdating = False
If UserForm1.TextBox1 = “” Then
MsgBox (“会員番号が未入力です”)
Else
msg = MsgBox(“修正登録しますか？”, Buttons:=vbYesNo + vbExclamation)
If msg = vbYes Then
ActiveCell.Offset(, 1).Value = UserForm1.TextBox2.Value
ActiveCell.Offset(, 2).Value = UserForm1.TextBox3.Value
ActiveCell.Offset(, 3).Value = UserForm1.TextBox4.Value
ActiveCell.Offset(, 4).Value = UserForm1.TextBox5.Value
ActiveCell.Offset(, 5).Value = UserForm1.TextBox6.Value
ActiveCell.Offset(, 6).Value = UserForm1.TextBox7.Value
ActiveCell.Offset(, 7).Value = UserForm1.TextBox8.Value
If OptionButton4.Value = True Then
ActiveCell.Offset(, 9).Value = “男”
ElseIf OptionButton5.Value = True Then
ActiveCell.Offset(, 9).Value = “女”
End If
End If
End If
Unload UserForm1
UserForm4.Label1.Caption = Range(“P2”).Value
UserForm4.TextBox1.Value = Range(“S2”).Value
UserForm4.TextBox2.Value = Range(“T2”).Value
UserForm4.Show
Application.ScreenUpdating = True
End Sub

顧客番号を検索する機能

エクセルの検索機能と言えば、ワークシート上でctrl+Fでこのような画面が表示されます。

エクセル検索と置換
エクセル検索と置換

この機能を使えば、わざわざExcel-VBAを使って検索をしなくてもいいのですが、住所録のデータが入っているセルを直接触るようになるので、計算式が入っているセルを誤って消してしまったりするリスクがあります。

そのリスクを避けるためにExcel-VBAを使った検索機能と登録修正を組み合わせた方が安全です

電話番号検索ボタンにVBAコードを登録

顧客番号検索フレーム内のテキストボックス（TextBox12）に電話番号を入力して電話番号検索ボタンを押したら、住所録リストを検索して見つけたら検索結果表示をするようにしましょう。

電話番号検索ボタンにVBAコードを登録する
  1. 誤って電話番号検索ボタンが押された時のエラー回避メッセージ表示
  2. テキストボックスに入力された電話番号をセルR12へ転記
  3. 顧客番号入力している列から入力した電話番号を探しリストアップする
  4. リストアップした顧客番号と名前を検索結果用フォームに書き出す
  5. ユーザーフォーム切替える
  6. 該当者がいない時はメッセージで知らせる
  7. 上記1～6の作業を行う時に画面が作業中チラつくので、ちらつき防止処理をする

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

１、誤って電話番号検索ボタンが押された時のエラー回避メッセージ表示

修正登録ボタン同様に、電話番号を入力するテキストボックス（TextBox12）に電話番号を入力して電話番号検索ボタン（CommandButton6）を押すのが当たり前なのですが、うっかりテキストボックスに何も入力しないで押してしまった時に、メッセージを表示してあげると親切ですし、誤った操作という事をお知らせできるので設定することをおススメします。

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

If UserForm1.TextBox12 = “” Then
MsgBox (“電話番号が入力されていません”)

あわせて読みたい
複数のテキストボックスが空欄の時にメッセージを出す方法 エクセルでユーザーフォームを使ったオリジナルの入力画面はとても便利で作業時間を大幅に削減してくれるので重宝していると思いますが、自分以外の人に使ってもらうと...

２、テキストボックスに入力された電話番号をセルR12へ転記

エクセル住所録
画像クリックで拡大します

TextBox12に入力された電話番号をセルR12へ転記することで、R12に記載された電話番号をFindメソッドを使って、探し出せるようにします。

セルR12に電話番号、セルQ12にフリガナ
TextBoxの入力値をセルへ転記

Range(“R12”).Value = UserForm1.TextBox12.Value

あわせて読みたい
ユーザーフォームの複数のテキストボックスの値をまとめてセルへ入力 ユーザーフォームに10個のテキストボックスに入力した値をまとめて一括でセルに書き写すことありませんか？ 1個1個VBAコードを書いてもいいのですが、10個や20個と増え...

３、顧客番号を入力している列から入力した電話番号を探しリストアップする

住所録で電話番号が入力されているのはAH列なので、ここでは2行目から1000行目までを検索範囲とし、同じ条件でデータ続けて検索するにはFindNextメソッドを使います。

本来、電話番号なので同一番号がないと思ってFindメソッドを使っていましたが、ご家族などで固定電話番号で複数人登録されて、検索できなかったので繰り返し処理を行うFindNextメソッドを使う方が間違えないです。

検索して該当した電話番号から、顧客番号と名前をセルBG列・BH列にリストアップする。

同じ条件で続けて検索するFindNextメソッド

Dim myRange As Range, meRange As Range, myAddress As String, i As Integer
Set meRange = Range(“AH2:AH1000”)
Set myRange = meRange.Find(What:=Range(“R12”).Value, LookIn:=xlValues)
If Not myRange Is Nothing Then
myAddress = myRange.Address
i = 2
Do
Cells(i, “BG”).Value = myRange.Offset(, -6).Value
Cells(i, “BH”).Value = myRange.Offset(, -5).Value
Set myRange = meRange.FindNext(After:=myRange)
i = i + 1
Loop Until myRange.Address = myAddress

見つけた電話番号のセルの位置にカーソルが移動し（myRange)-6なので左へ6列目のセルの値（顧客番号）をBG列の2行目から順に、-5なので左へ5列目のセルの値（名前）をBH列の2行目から順にリストアップします。

あわせて読みたい
表の中から同じ検索条件で条件に合うデータだけを順に取り出す方法 成績表や住所録などの表の中から条件に合うデータだけ取り出して、リストアップした一覧表を作りたいって事ありませんか？ 点数順や科目別に並び替えたりするには、エク...

４、リストアップした顧客番号と名前を検索結果用フォームに書き出す

リストアップして書き出された顧客番号と名前を検索結果フォームに表示します。

表示方法はユーザーフォームに配置したLabelの名前（Caption）を書き換える方法です。

ユーザーフォームのラベルを繰り返し書き換える

For j = 1 To 20
With UserForm2.Controls(“Label” & j)
.Caption = Cells(j + 1, 59).Value
End With
With UserForm2.Controls(“Label” & j + 20)
.Caption = Cells(j + 1, 60).Value
End With
Next j

ここでは最大20名分をリストアップ可能にしたので変数jは1～20となり、ラベルの数は顧客番号が1～20で名前が21～40を使用するのでj+20となります。

また取得するセル番地はj+1で2行目の59列目（BG列）、60列目（BH列）となります。

５、ユーザーフォーム切替える

UserForm2
Label1～20が顧客番号・21～40が名前に変わります

検索によりリストアップされた顧客番号と名前が読み込まれてからこのフォームは表示され、住所録入力フォームは閉じられてフォームの切り替えが行われます。

あわせて読みたい
エクセルを開くと自動的にユーザーフォームが数秒後に開く方法 エクセル住所録などでオリジナル入力画面をユーザーフォームで作っても、そのままではエクセルワークシート上にはありません。 最初の頃は「あれ？どこに行っちゃたのか...

このフォームに表示された顧客番号をクリックすると顧客番号が入力された状態で住所録入力フォームに切り替わります。

リストアップが終わり、UserForm2にデータは引き継がれたのでセルを空欄に戻しておくことも忘れずに行います。

６、該当者がいない時はメッセージで知らせる

検索した結果該当者がいない場合はメッセージで知らせます。

メッセージで該当者なしを知らせる

Else
MsgBox “該当者がいません”

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

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

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

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

あわせて読みたい
エクセルが重くなるVBAの処理を高速化して画面ちらつきを防ぐ方法 コマンドボタンをクリックして作業を自動化するとVBAコードを読み取りながら処理をしているので、画面がチョコチョコ移動したり切り替わったり目障りだと思ったことあり...
電話番号検索ボタンに登録のVBAコード

Private Sub CommandButton6_Click()
Application.ScreenUpdating = False
If UserForm1.TextBox12 = “” Then
MsgBox (“電話番号が入力されていません”)
Else
Range(“R12”).Value = UserForm1.TextBox12.Value
Dim myRange As Range, meRange As Range, myAddress As String, i As Integer
Set meRange = Range(“AH2:AH1000”)
Set myRange = meRange.Find(What:=Range(“R12”).Value, LookIn:=xlValues)
If Not myRange Is Nothing Then
myAddress = myRange.Address
i = 2
Do
Cells(i, “BG”).Value = myRange.Offset(, -6).Value
Cells(i, “BH”).Value = myRange.Offset(, -5).Value
Set myRange = meRange.FindNext(After:=myRange)
i = i + 1
Loop Until myRange.Address = myAddress
For j = 1 To 20
With UserForm2.Controls(“Label” & j)
.Caption = Cells(j + 1, 59).Value
End With
With UserForm2.Controls(“Label” & j + 20)
.Caption = Cells(j + 1, 60).Value
End With
Next j
Unload UserForm1
UserForm2.Show
Range(“BG2:BH1000”).Value = “”
Else
MsgBox “該当者がいません”
End If
End If
Application.ScreenUpdating = True
End Sub

スポンサーリンク

【フリガナ検索】ボタンにVBAコードを登録しよう

今度は電話番号の代わりにフリガナ（苗字）で顧客番号を検索できるようにします。

住所録検索用
セルR12に電話番号、セルQ12にフリガナ

使用するVBAコードもほぼ電話番号検索ボタンと一緒で、違いはテキストボックスに入力されたフリガナの転記先が画像のようにQ12に変わり、FindNextメソッドのmyRange.Offsetの位置がフリガナを基準になります。

電話番号検索との違うVBAコード①

Cells(i, “BG”).Value = myRange.Offset(, -2).Value
Cells(i, “BH”).Value = myRange.Offset(, -1).Value

あとは同じVBAコードが使用できますから難しくはありません。

昭和・平成・令和から西暦にする

生年月日を記入する欄に「西暦記入」と書いてあってもなぜか、わざわざ平成○○年って書く人っていますよね。

その度に西暦に置き換えるのが意外と面倒という声があり、住所録フォームに追加したところ好評です。

作るのも簡単でエクセル関数VLOOKUPを使いワークシートに表を用意して、ユーザーフォーム上ではテキストボックスとオプションボタンで完成です。

和暦を西暦に変換
  1. ワークシートに和暦と西暦の表を作成
  2. オプションボタンとテキストボックスの値を転記するセルと数式を準備
  3. VLOOKUP関数で検索した西暦をフォームに表示

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

１、ワークシートに和暦と西暦の表を作成

和暦西暦ワークシート

和暦西暦ワークシート（一部抜粋）

セルW5~X229に左の図のように和暦と西暦の対照表を作っておきます。

オプションボタンで選択された和暦がセルW2

テキストボックスの年がセルX2

W2とX2とX1をくっつけてY2

  • 和暦が昭和1年～昭和64年
  • 平成1年～平成31年
  • 令和1年～令和82年

これで西暦1926年～2100年まで対応します。

Y2の値をVLOKUP関数で見つけた西暦をY4

Y4の値をユーザーフォームに表示させる

それでは、ワークシートの準備です。

２、オプションボタンとテキストボックスの値を転記するセルと数式を準備

セルY2の結合する数式

=W2&X2&X1

セルの値同士をくっつけるには「&」でできるようになります。

セルY4の検索するエクセル関数

=VLOOKUP(Y2,W5:X229,2,0)

Y2が検査値、W5:X229が和暦と西暦の表の範囲、2が列番号（表の2列目「西暦」）、0が完全一致となります。

次に変換ボタンにVBAコードを書き込みます。

３、VLOOKUP関数で検索した西暦をフォームに表示

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

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

If UserForm1.TextBox14.Value = “” Then
MsgBox (“年が入力されていません”)

あわせて読みたい
複数のテキストボックスが空欄の時にメッセージを出す方法 エクセルでユーザーフォームを使ったオリジナルの入力画面はとても便利で作業時間を大幅に削減してくれるので重宝していると思いますが、自分以外の人に使ってもらうと...

次にオプションボタンの設定で、選んだボタンの和暦がセルW2に転記させます。

オプションボタンの設定

If OptionButton1.Value = True Then
Range(“W2”).Value = “昭和”
ElseIf OptionButton2.Value = True Then
Range(“W2”).Value = “平成”
ElseIf OptionButton3.Value = True Then
Range(“W2”).Value = “令和”
ElseIf OptionButton6.Value = True Then
Range(“W2”).Value = “予備”
End If

年を入力するテキストボックス（TextBox14）の値をセルX2に転記させます。

テキストボックスの設定

Range(“X2”).Value = TextBox14.Text

最後に和暦西暦対照表から探し出した西暦をフォーム（Label35）に表示する。

フォームに表示させるラベルの設定

UserForm1.Label35.Caption = Range(“Y4”).Value

これで変換ボタンで西暦が表示されるようになりました。

変換ボタン登録のVBAコード

Private Sub CommandButton8_Click()
If UserForm1.TextBox14.Value = “” Then
MsgBox (“年が入力されていません”)
Else
If OptionButton1.Value = True Then
Range(“W2”).Value = “昭和”
ElseIf OptionButton2.Value = True Then
Range(“W2”).Value = “平成”
ElseIf OptionButton3.Value = True Then
Range(“W2”).Value = “令和”
ElseIf OptionButton6.Value = True Then
Range(“W2”).Value = “予備”
End If
Range(“X2”).Value = TextBox14.Text
UserForm1.Label35.Caption = Range(“Y4”).Value
End If
End Sub

とりっぷぼうる
とりっぷぼうる

ここまで書いた和暦を西暦にする方法、2018年当時の私自身のエクセルの知識ではこんな感じでしたが、現在ではかなりシンプルに作れるようになりました。

ちょっとだけ成長した和暦西暦変換を見ていてコピペして使ってもらえると嬉しいな！！

あわせて読みたい
エクセルで和暦（昭和・平成・令和）を西暦に一発変換する 和暦から西暦または西暦から和暦に変換する事なんてソフトやアプリケーションでできるのに、なんでわざわざエクセルでやる必要があるの？って思いませんでしたか？ ある...
作成事例
Excel-VBA作成例 ユーザーフォーム
目次