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

コメント一覧 （21件）

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

  今回こちらの記事と、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関係の変更も必要かと思います。

    返信

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

  返信

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

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

    返信

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