本ページはプロモーションが含まれています。

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

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

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

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

数文字打つことで検索候補の絞り込みが可能に

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

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

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

目次

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

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

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

予測検索シート

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

「郵便番号」シート

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

「検索候補」シート

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

=IFERROR(VLOOKUP(A2,郵便番号,2,FALSE),"")
今回追加の「Excelサジェスト」シート

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

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

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

こちらnoteで、関連記事で利用しているシートを公開中です。

もし時間がない方はご利用をおすすめします(ただいまタイムセール中、5個の限定価格300円→10個限定価格500円、あと9個)。

1 2

関連コンテンツ

よかったらシェアしてね!
  • URLをコピーしました!
  • URLをコピーしました!

コメント

コメント一覧 (36件)

  • はじめまして。
    古寺と申します。

    今回こちらの記事と、VBAを利用せずリストを予測候補表示の2つを参考にしています。
    しかし、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を利用させて頂こうと思い再挑戦し使えるようになったのですが、使っているとエラーが出ます。

    内容は、町名リストに無い文字を入力して検索してしまうとエラーが出ます。
    もう一つ、印刷し終わって新たに作り替えようとして全体の入力(他にも5列ほど入力項目がある)を削除するとエラーが出ます。
    VBA無しの方では、どちらの条件でも正常に作動しています。
    .Add Type:=xlValidateList, Operator:=xlEqual, Formula1:=”=町名”
    どちらのエラーもこの部分が黄色になっています。

    対処方法があるようでしたら、ご教示いただけますでしょうか。
    よろしくお願いいたします。

    • HORI 様
      コメントありがとうございます。

      エラーの内容を拝見すると、別のセルに移動した際に定義した名前(町名)がクリアされていないことが原因のように思います。

      ただいま外出中ですので詳しく試すことはできませんが、一つ前の山田様のコメントが参考になるのではないかと思います。

      一度試していただけたら幸いです。
      もしそれでもうまくいかないようであれば、再度コメントいただきたく存じます。

      何卒よろしくお願いします。

      • 早速のご返答ありがとうございます。

        山田様のコメントは事前に拝見していて、エラー回避の為にそちらのVBAを利用させて頂いていました。
        二つ目のエラーに関しては、入力の削除をボタンでやるようにしていたので、一括消去ではなくA列のみ削除後B列以降を削除するように設定し直して対応しました。
        一つ目のエラーですが、エラーメッセージを表示する設定と関係がありますか?
        VBA無しの方では、入力規制のエラーメッセージを表示しない設定にしてあります。

        よろしくお願いいたします。

          • 山田 様
            コメントありがとうございます。

            ヒントをいただき、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

          以上、お試しいただければ幸いです。

          • リーダーの独り言様、山田様
            ありがとうございます。
            快適に予測検索出来るようになりました。

            作っていただいた内容でやってみると、『入力した値は正しくありません。』とメッセージが出ます。ここまでは正しくない文字なので良いのですが、その後そのセルに正しい文字を入れても入力制限のメッセージが出て、何も入力出来なくなってしまいました。違うセルで検索をすると正常に戻りました。

            行数が約3倍になってしまいましたが、今は入力規制のエラーメッセージを表示しないようにして使っています。以下のようにしました。

            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」のように作成されてみてはいかがでしょうか?
      サジェスト候補で二層は考えてなかったので、うまく動作しないようであればまたコメントいただけたら検討させていただきます。
      何卒よろしくお願いいたします。

  • はじめまして
    鈴木ともうします。
    いったん文字を入力したセルに再びサジェスト機能を使うためには一度セルをクリアする必要がありますが、これを回避する方法はないでしょうか?
    宜しくお願いします。

    • 鈴木様
      コメントありがとうございます。

      残念ながら、一旦入力したセルに再びサジェスト機能を使うためには、セルをクリアする必要があります。
      そのような仕様となっております。大変申し訳ありません。

      • 注意点は、いったん文字を入力したセルに再びサジェスト機能を使うためには一度セルをクリアする必要があります。
        とのことですが、セルをクリアしてもエラーになります。
        どのような方法でクリアにされていますでしょうか。

        • 山田様
          コメントありがとうございます。

          当方で確認いたしましたところ、普通に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」
    というエラーメッセージが表示されて、サジェストが表示されません。

    「郵便番号」シートにある名前を正式に入力したらサジェストは出ますが、名前の一部だけ入力したらサジェストが出るようにはなっていないです。

    何が原因でしょうか?

コメントする

CAPTCHA


このサイトはスパムを低減するために Akismet を使っています。コメントデータの処理方法の詳細はこちらをご覧ください

目次