サジェスト機能を実現するコードの作成
「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
最後に、変更されたセルを選択して「End If」としています。
Range(myRange).Select
End If
これのおかげでちょっとだけ挙動が変になりますが、まあご愛敬ということで許してください。
注意点は、いったん文字を入力したセルに再びサジェスト機能を使うためには一度セルをクリアする必要があります。
いくつかVBAを利用したエクセルのサジェスト機能の紹介のサイトはあるが
「エクセル サジェスト」等で検索をすると、VBAのコードだけでサジェスト機能を実現しているサイトもありますが、初心者にとっては応用が難しいですよね。コードの意味を理解するのに時間がかかります。
今回の「VBAのコード9行」でエクセルのサジェスト機能を実現する方法は、VBAを少し知っている方にとって理解がしやすいと思います。
コメント
コメント一覧 （36件）
はじめまして。
古寺と申します。
今回こちらの記事と、VBAを利用せずリストを予測候補表示の２つを参考にしています。
しかし、vbaがうまく機能せず、アドバイスいただければと思いご連絡しております。
私なりに考えたところ問題はMy Rangeの箇所だと思うのですが
こちらをもう少し詳しくご教示いただけないでしょうか。
MyRangeは既に別の場所で作っている形なのでしょうか。
そのままコピペだと反映しないですよね？
質問の仕方もわからず、あいまいで申し訳ございません。
よろしくお願い致します。
古寺様、コメントありがとうございます。
myRangeは変数を格納するだけの文字列ですからコピペのままで特に変数の宣言をしなくても利用できると思います。
改めてコードを見直したのですが、コピペでうまくいかない可能性があるのは、
If ActiveCell.Column = 1 Then
の部分ではないかと推測します。
もしサジェスト変換をする列がA列なら1、B列なら2、となります。
この部分を確認していただけたら幸いです。
的外れであれば申し訳ないです。
返信はや 私の聞きたいことすべて解決した
ありがとうございました
とよしま様
コメントありがとうございます。
一つ上のコメントが役に立ちましたでしょうか。
もしそうなら嬉しい限りです。
ありがとうございました。
初めまして、山田と申します。
こちらと前回の記事を参考にExcelファイルを作らせていただいております。
サジェスト機能が便利で作業効率もアップし、大変助かっております。
その際エラーがでてしまい、直せないかと試行錯誤したのですが
デバックできなかった為質問させていただきます。
ExcelサジェストシートのA列に入力、サジェスト機能にて選択後、Tabキーにて隣のB列へ
文字または数字を入力した際Enterで確定をするとバグが出てきます。
コードの7行目
.Add Type:=xlValidateList, Operator:=xlEqual, Formula1:=”=町名”
が黄色になっています。
その際に、予測検索シートA2へ入力されているのが先ほど入力したB列のものになっていました。
A列へ入力したときに、予測検索シートA2はA列のものになっているのは確認済みですので、その後B列へ入力したものに変わってしまったものと思われます。
こちらの対処の仕方をご教授願えれば大変助かります。
拙い文章で申し訳ございませんが、よろしくお願いいたします。
山田 様
コメントありがとうございます。
今外出中で、実際に操作をできない状態ですが、文面から思いついたことは下記のステートメントが問題なのかもしれません。
If ActiveCell.Column = 1 Then
これは、現在選択されている列がA列ならばmyRangeという範囲を設定するステートメントですが、B列が選択されたときにもmyRangeという範囲名が残っているためにエラーが発生している可能性があります。
それを回避するために、Private Sub Worksheet_Changeに条件分岐を設定し、Else構文でA列以外がアクティブなときはmyRangeをクリアする設定が必要かもしれません。
エスパーな答えで申し訳ありません。
条件分岐をしてもうまく作動しないようであればまた後日、実際に操作して確認後修正版をこちらのコメント欄にてご返信させていただきます。
何卒よろしくお願い致します。
返信ありがとうございます。遅くなり申し訳ございません。
返信いただいた内容をもとに、テスト環境で下記のように実行してみましたら思うような結果となりましたことをご報告致します。
このたびはお忙しい中、ご教授いただきありがとうございました。
Private Sub Worksheet_Change(ByVal Target As Range)
If ActiveCell.Column = 1 Then
Range(“a:a”).Validation.Delete
myRange = Target.Address
If Range(myRange).Column = 1 Then
Worksheets(“予測検索”).Range(“a2″).Value = Target
With Range(myRange).Validation
.Add Type:=xlValidateList, Operator:=xlEqual, Formula1:=”=町名”
End With
Range(myRange).Select
End If
End If
End Sub
山田 様
ご返信ありがとうございます。
おお、素晴らしい！
なるほど、さらに条件を追加して回避されたということですね。
大変参考になりました。
また、お役に立てて嬉しく思います。
ありがとうございました。
はじめまして。
『VBAを利用せずリストを絞り込む方法』を便利に使わせて頂いてます。
ありがとうございます。
Excelは先月まで、作ってあるものを使うだけで計算式も使ったことが無い初心者なので、理解度がかなり低いですがご容赦ください。
現状、（エラーが出てVBAは断念して）VBAを利用せずサジェスト機能を使っています。
少し様式の違うブックを作ることになって、同じものを幾つも作り多くの入力セルに対応させている今の方法では作るのに手間がかかるため、今回はVBAを利用させて頂こうと思い再挑戦し使えるようになったのですが、使っているとエラーが出ます。
内容は、町名リストに無い文字を入力して検索してしまうとエラーが出ます。
もう一つ、印刷し終わって新たに作り替えようとして全体の入力（他にも５列ほど入力項目がある）を削除するとエラーが出ます。
VBA無しの方では、どちらの条件でも正常に作動しています。
.Add Type:=xlValidateList, Operator:=xlEqual, Formula1:=”=町名”
どちらのエラーもこの部分が黄色になっています。
対処方法があるようでしたら、ご教示いただけますでしょうか。
よろしくお願いいたします。
HORI 様
コメントありがとうございます。
エラーの内容を拝見すると、別のセルに移動した際に定義した名前（町名）がクリアされていないことが原因のように思います。
ただいま外出中ですので詳しく試すことはできませんが、一つ前の山田様のコメントが参考になるのではないかと思います。
一度試していただけたら幸いです。
もしそれでもうまくいかないようであれば、再度コメントいただきたく存じます。
何卒よろしくお願いします。
早速のご返答ありがとうございます。
山田様のコメントは事前に拝見していて、エラー回避の為にそちらのVBAを利用させて頂いていました。
二つ目のエラーに関しては、入力の削除をボタンでやるようにしていたので、一括消去ではなくＡ列のみ削除後Ｂ列以降を削除するように設定し直して対応しました。
一つ目のエラーですが、エラーメッセージを表示する設定と関係がありますか？
VBA無しの方では、入力規制のエラーメッセージを表示しない設定にしてあります。
よろしくお願いいたします。
横から失礼いたします。
HORI様初めまして、山田と申します。
私も似たようなエラーになったときに、以下のサイトを参考にさせていただきました。
参考になれば幸いです。
http://officetanaka.net/excel/vba/tips/tips104.htm
山田 様
コメントありがとうございます。
ヒントをいただき、HORI様のエラー回避解決できました。
返事が遅くなり申し訳ありません。
久々に当該ファイルを触ることができました。
一つ目のエラーの件ですが、名前の定義「町名」が該当文字列がない場合には名前「町名」が存在しないことが原因と思われます。
山田様のコメントでヒントをいただきました。
コードを下記のように追加するとうまくいきました。
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
On Error GoTo myError
With Range(myRange).Validation
.Add Type:=xlValidateList, Operator:=xlEqual, Formula1:=”=町名”
End With
Range(myRange).Select
End If
myError:
End Sub
以上、お試しいただければ幸いです。
リーダーの独り言様、山田様
ありがとうございます。
快適に予測検索出来るようになりました。
作っていただいた内容でやってみると、『入力した値は正しくありません。』とメッセージが出ます。ここまでは正しくない文字なので良いのですが、その後そのセルに正しい文字を入れても入力制限のメッセージが出て、何も入力出来なくなってしまいました。違うセルで検索をすると正常に戻りました。
行数が約３倍になってしまいましたが、今は入力規制のエラーメッセージを表示しないようにして使っています。以下のようにしました。
Private Sub Worksheet_Change(ByVal Target As Range)
If ActiveCell.Column = 1 Then
Range(“a:a”).Validation.Delete
myRange = Target.Address
If Range(myRange).Column = 1 Then
Worksheets(“予測検索”).Range(“a2″).Value = Target
With Range(myRange).Validation
On Error Resume Next ’エラー無視開始
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=”=町名”
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = “”
.ErrorTitle = “”
.InputMessage = “”
.ErrorMessage = “”
.IMEMode = xlIMEModeNoControl
.ShowInput = True
.ShowError = False
On Error GoTo 0 ‘エラー無視終了
End With
Range(myRange).Select
End If
End If
End Sub
私には、これを簡略化する知識がないのでこのまま使おうと思います。
いろいろとご教示いただきありがとうございまた。
HORI 様
コメントありがとうございます。
素晴らしい！
よく研究されましたね。勉強になります。自分も参考にさせていただきます。
快適に予測変換利用されているとのことでよかったです。
本来ならもっと簡単にエクエルの標準機能として追加してほしいところですよね。
今後とも何卒よろしくお願いいたします。
有益なVBAの例示、誠にありがとうございます。
さて、サジェスト機能のシート例はA列のみですが、B列もサジェスト機能を
使用したい場合はどのような記述になるのでしょうか。
考えているのは、出発地、目的地でそれぞれ入力項目があるような表への入力です。
A列 B列
出発地 目的地
東京 新宿
他の関数のシートはメンテ済でVBAの記述だけがよくわかりません。
ご教示頂けないでしょうか。
Thomasさま、コメントありがとうございます。
ただ今外出中のスマホからの投稿なのですが、
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
の部分の
ActiveCell.Column = 1
を2に変更し、ワークシートも改めて「予測変換2」を作成し、名前の定義も例えばですが「町名2」のように作成されてみてはいかがでしょうか？
サジェスト候補で二層は考えてなかったので、うまく動作しないようであればまたコメントいただけたら検討させていただきます。
何卒よろしくお願いいたします。
あと、Range関係の変更も必要かと思います。
はじめまして
鈴木ともうします。
いったん文字を入力したセルに再びサジェスト機能を使うためには一度セルをクリアする必要がありますが、これを回避する方法はないでしょうか？
宜しくお願いします。
鈴木様
コメントありがとうございます。
残念ながら、一旦入力したセルに再びサジェスト機能を使うためには、セルをクリアする必要があります。
そのような仕様となっております。大変申し訳ありません。
注意点は、いったん文字を入力したセルに再びサジェスト機能を使うためには一度セルをクリアする必要があります。
とのことですが、セルをクリアしてもエラーになります。
どのような方法でクリアにされていますでしょうか。
山田様
コメントありがとうございます。
当方で確認いたしましたところ、普通にDELキー等で削除しても問題は発生していません。
どのようなエラーが表示されるか、詳細教えていただけたら解決できるかもしれません。
ご返信ありがとうございます。
1度目は問題なく入力できるし表示されますが、再度delで削除して新たな文字を入力ところ下記のエラーメッセージが表示されます。
すべてクリア等も試しましたが同じ結果でした。
「✕この値は、このセルに定義されているデータ入力規則の制限を満たしていません。」
文字の下に再試行・キャンセル・ヘルプのボタンがあります。
よろしくお願いいたします。
山田様
コメントありがとうございます。
なるほど、上記の症状確認致しました。
回避方法ですが、お手数ですが一旦別のセルを選択後、再入力したいセルを再度選択し入力してみてください。
何卒よろしくお願いいたします。
「たった9行のVBAコードでエクセルのサジェスト機能が可能！」を参考にさせてもらっています。
今まで何度かVBA挑戦して挫折してしまっています。
今回は頑張ろうと思って勉強していますが、行き詰まってしまいました。
・A列すべてにドロップダウンのサジェスト機能は付けられますか？
ご教授おねがいします。
maki.ym様
コメントありがとうございます。
VBAの記述にあります以下の部分
If ActiveCell.Column = 1 Then
という部分でA列が選択された場合にのみ、サジェスト変換がされるようにしています。
ですからA列であれば何行目であっても動作するかと思われます。
ご確認いただけたら幸いです。
何卒よろしくお願い致します。
ご返信ありがとうございます。
A列のどれか一つのセルにはつきますが、すべてのセル(A1～A30まで)を
同じサジェスト機能付きのドロップダウンにしたいのですが可能でしょうか？
納品書を作るためにこの機能を使いたいです。
すいません！できました！
あとは希望する形に応用できれば大丈夫だと思うので頑張ってみます！
お手数をおかけしました。
またわからなくなったら質問させていただきます。
よろしくお願いいたします。
maki.ym様
ご返信ありがとうございます。
うまく稼働したようでよかったです。
ぜひ応用して活用されることを願っています。
また何かありましたらコメントいただけたら幸いです。
何卒よろしくお願い致します。
度々申し訳ございません。教えてください。
C列にサジェスト機能を付けたい場合は
Private Sub Worksheet_Change(ByVal Target As Range)
If ActiveCell.Column = 3 Then
Range(“c:c”).Validation.Delete
myRange = Target.Address
Worksheets(“予測検索”).Range(“a2:a30″).Value = Target
With Range(myRange).Validation
.Add Type:=xlValidateList, Operator:=xlEqual, Formula1:=”=町名”
End With
Range(myRange).Select
End If
End Sub
で合ってますか？
できればC6～C30にだけサジェスト機能つけられないかと思っていますが可能でしょうか？
もし可能ならそちらも合わせてご教授お願いします。
maki.ym様
ご返信が遅くなり申し訳ありません。
上記の通りで大丈夫だと思います。
ただ、A列もサジェスト機能を利用するのであればIf End IfをA列用の記述の下に追加する必要があります。
また行を制限するにはthenの後にもう一度If EndIf構文を記述することで可能です。
別件ですが、メールでお問い合わせいた方だと思うのですが、何故か返信がうまく届かなようです。
こちらにてお詫び申し上げます。
今後ともよろしくお願いいたします。
お疲れ様です。
仕事でサジェスト機能を使わせていただこうかと思っているのですが、
うまくいかないところがあります。ご教授お願いします。
「Excelサジェスト」のシートで名前の一部を入力してプルダウンをクリックすると、
「This Value Doesnt Match The Data Validation Restrictions Defined For This Cell」
というエラーメッセージが表示されて、サジェストが表示されません。
「郵便番号」シートにある名前を正式に入力したらサジェストは出ますが、名前の一部だけ入力したらサジェストが出るようにはなっていないです。
何が原因でしょうか？
IS様
コメントありがとうございます。
まず質問ですが、下記のページのサジェスト自体は稼働しているのでしょうか？
VBAを利用せずリストを予測候補表示（サジェスト機能）で絞り込む方法！エクセルのドロップダウンリスト（プルダウンメニュー）は便利なようで結構めんどい。。。 365・2019・2016・2013・2010・2007対応
ご確認いただけたら幸いです。
何卒よろしくお願い致します。
はい、「予測検索」シートでのサジェストは正常にできています。
「予測検索」シートのA3のセル(サジェストが表示されるセル)も、名前の一部を入力してエンターを押すと同様のエラーメッセージが表示されるのですが、その点は問題はないのでしょうか？
データの入力規則があるセルで問題が起こっているので、もしかしたら何か関係あるかもしれないと思うのですが。。。
ご連絡ありがとうございます。
なるとほど、データの入力規則を適用しておられるのですね。
それではデータの入力規則を外してみてください。
データの入力規則が設定されているとうまく稼働しないはずですので。
何卒よろしくお願いいたします。