たった9行のVBAコードでエクセルのサジェスト機能が可能！　数文字入力するだけでリストを予測変換 これはすごい！ 土日でマスター エクセルVBA講座③ ワークシートイベントの利用

今回タイトルで煽っていますが、実際すごいと思います。

文字の一部を入力するとドロップダウンリスト（プルダウンメニュー）の候補が絞られて選択が容易になるコードを9行で作成しました。

例えば「南」と入力すると「南」を含んだ候補だけが下図のように表示されます。

ただ、エクセルのブックで準備が必要です。下記の記事を最初にご覧ください。

こちらの記事をしっかり理解できる方にとっては非常に簡単なマクロとなります。

VBAを利用せずリストを予測候補表示（サジェスト機能）で絞り込む方法！エクセルのドロップダウンリスト（プルダウンメニュー）は便利なようで結構めんどい。。。 365・2019・2016・2013・2010・2007対応

エクセルのドロップダウンリスト（プルダウンリスト）って、便利なようですがリストが増えると選択するのが大変なんですよね。入力候補が多数あると、入力したい値を探すのに一苦労です。入力規則があるとデータが多いときに選択が大変...

long-valley-river.com

2020.02.22

3つのシートを事前に作成しておく必要がありますが、これさえ完成すればたったの9行のコードでGoogleチックなサジェスト機能（あいまい検索）が可能になります。

必要なエクセルのシートは4つ

上記の記事でも紹介していますが、用意するシートは3つ＋今回追加するシート1つです。

まずは、今回ほぼ利用しないのですが、検索する文字列を格納する「予測検索」シートです。A2セルに文字を入れて、部分一致の検索を行うためのシートです。

続いて絞り込み候補のデータベースとなる、地名と郵便番号のデータを格納した「郵便番号」シートです。

そしてサジェスト機能の肝である、部分一致した検索候補を表示する「検索候補」シートです。

そして今回追加する、「Excelサジェスト」シートです。B列には「VLOOKUP関数」で郵便番号を表示する数式を挿入しています。

=IFERROR(VLOOKUP(A2,郵便番号,2,FALSE),"")

ワークシートイベントを利用してサジェスト機能を実現

今回のVBA講座は、「ワークシートイベント」を利用したコードの作成です。

ここからは、前述の関連記事で各シートを作成済ということで進めていきます。

「Visual Basic」の画面で、今回サジェスト機能を利用するシートをダブルクリックします。

左側「（General)」をクリックして、「Worksheet」を選択します。

「Worksheet」を選択すると、「Private Sub Worksheet_SelectionChange」が自動的に表示されますが、右側のプルダウンメニューから「Change」を選択します。

特に必要はないのですが、「Private Sub Worksheet_SelectionChange」を消去しておきます。コードを見やすくするためです。

「Private Sub Worksheet_Change」と「End Sub」の間に今回の「9行のコード」を入力するだけでエクセルの「サジェスト機能（あいまい検索）」が完成します。

サジェスト機能のコード（マクロ）内容の解説

下記が「サジェスト機能」をエクセルで実現させるコードの完成版です。

「Worksheet_Change」は、セルの値が変更された際に実行されるマクロです。「Target」という引数（変更されたセルの範囲）を持っています。

Private Sub Worksheet_Change(ByVal Target As Range)
 If ActiveCell.Column = 1 Then
 Range("a:a").Validation.Delete
 myRange = Target.Address
 Worksheets("予測検索").Range("a2").Value = Target
 With Range(myRange).Validation
         .Add Type:=xlValidateList, Operator:=xlEqual, Formula1:="=町名"
 End With
 Range(myRange).Select
 End If
End Sub

下記の一文は、変更されたセルの列が1（A列）ならば、その後のマクロを実行するという意味です。ですからB列以降を変更してもマクロは実行されません。

If ActiveCell.Column = 1 Then

下記の一文では、すでに存在するA列の入力規則をクリアしています。「Validation」は、セルの入力規則を設定する「オブジェクト（操作をする対象）」です。

 Range("a:a").Validation.Delete

下記は自分で作成した変数「myRange」に「Target」（値が変更されたセル）の範囲を設定する一文です。作成した変数は分かりやすいように「my～」という変数を利用することが自分は多いです。分かりやすければ何でも構いません。

 myRange = Target.Address

次のコードでは、先ほど紹介したワークシート「予測検索」のA2セルに今回入力された値（Targetという引数）を入力する命令です。

 Worksheets("予測検索").Range("a2").Value = Target

それに連動して「検索候補」シートに候補が表示されます。

そして、下記のコードが、文字を入力したセルの入力規則に、検索候補リストであるすでに定義している範囲の名前「町名」を設定するコードとなります。

 With Range(myRange).Validation
         .Add Type:=xlValidateList, Operator:=xlEqual, Formula1:="=町名"
 End With

Excel（エクセル）プルダウンリスト（ドロップダウンリスト）のリストを可変式リストにすればリストの項目が増えても再設定不要！ リストを名前の定義とテーブルにする2つの方法 365・2019・2016・2013・2010・2007対応

データの入力規則で、「元の値」をリストにして、プルダウンリスト（ドロップダウンリスト）を利用している方は多いと思います。「元の値」のリストは、Excel2007以前では、セル範囲での別シートを参照することができません。ですから参照...

long-valley-river.com

2019.11.20

最後に、変更されたセルを選択して「End If」としています。

 Range(myRange).Select
 End If

これのおかげでちょっとだけ挙動が変になりますが、まあご愛敬ということで許してください。

注意点は、いったん文字を入力したセルに再びサジェスト機能を使うためには一度セルをクリアする必要があります。

いくつかVBAを利用したエクセルのサジェスト機能の紹介のサイトはあるが

「エクセル サジェスト」等で検索をすると、VBAのコードだけでサジェスト機能を実現しているサイトもありますが、初心者にとっては応用が難しいですよね。コードの意味を理解するのに時間がかかります。

今回の「VBAのコード9行」でエクセルのサジェスト機能を実現する方法は、VBAを少し知っている方にとって理解がしやすいと思います。

ぜひ、ご活用ください。不明な点はコメントいただきましたら対応致します。