2018.06.20みなさまこんにちは、ノグチです。前回は、エクセルの入力規則のひとつ、ドロップダウンリストをVBAで設定する方法をご紹介しました。【エクセルVBA】Validation.Addメソッドで入力規則のドロップダウンを作る方法ValidationオブジェクトのAddメソッドを使って、セルに入力規則のドロップダウンリストを作成する方法をご紹介しています。シートやブックに入力された値によって入力規則の種類を変えたり、ドロップダウンリストに表示する値を変えたい、という時に便利に使えますよ。tonari-it.com2018.06.13Changeイベントを使った部分一致検索は前々回の記事をご覧ください。【エクセルVBA】ChangeイベントとEnableEventsプロパティで部分一致検索をする方法WorksheetオブジェクトのChangeイベントと、ApplicationオブジェクトのEnableEventsプロパティを使って、セルに得意先名の一部を入力したら、マスタシートから正式名称を取得して入力セルに返す、というコードをご紹介しています。面倒かつ間違い易い入力作業は、出来るだけ簡略化したいですね。最後に連載目次:エクセルVBAのイベントを使ってもっと便利なツールにしよう!Changeイベントプロシージャに入力規則作成を差し込む前回コードのおさらいでは、早速コードを書いていきましょう。前々回記事でご紹介したコードがこちら。Subそして入力シートと、マスタシートがこちらです。差し込むここに前回ご紹介した、セル範囲にドロップダウンリストを作成するコードを差し込みます。SubFindメソッドを使ってB5セルの入力値で、スタシートの得意先名称を検索した時の処理に、検索にヒットした場合にValidation.AddメソッドでB5セルにドロップダウンリストを作成する、というコードを差し込んでいます。これでFindメソッドにマッチした得意先の正式名称がマスタシートから取得されて、このようにドロップダウンリストに表示されるようになります。複数の検索結果をドロップダウンリストに表示させるしかし、マスタシートを見てみると、検索に使った文字列『会社』を含む得意先は全部で4つあります。上の画像では1件しか取得できていません。ヒットした値はすべてドロップダウンリストに表示させたいですよね。検索に使う文字列を含む得意先をすべてドロップダウンリストに表示できるように、もう少しコードに手を加えましょう。Findメソッドでマスタシートを検索して、マッチしたセル以降のセルはFindNextメソッドを使って値を検索します。マッチする値があったら、Validation.AddメソッドのFomula1パラメータセット用の変数varListに格納していく、という処理を加えます。IfShowErrorプロパティでエラーメッセージを表示させないようにする1度セルに検索したい値を入力して、Changeイベントが実行された後にもう一度違う値で検索をしたい!という場合もありますよね。その場合、上のコードのValidation.Addメソッドのパラメータ指定では、B5セルにドロップダウンリストに表示されていない値を入力すると、毎回メッセージが表示されてしまいます。少々煩わしいので、このメッセージ表示をOFFにしてしまいましょう。これには、ValidationオブジェクトのShowErrorプロパティを使います。ShowErrorプロパティは、このように記述します。(False)入力規則に指定された値以外を入力した時に表示されるメッセージを表示させたい場合をTrueを、表示させたくない場合はFalseを指定します。これも、上のコードに差し込んでおきましょう。複数の検索結果をドロップダウンリストに表示させるコードすると、こんなコードになります。objCustom.Addressで、マッチした値のセルを取得していますね。これは、次のDo~Loopにある、FindNextメソッドの終了条件のために取得しています。上のコードの場合、FindNextメソッドはマスタシートの最後のセルまで検索したら、もう一度得意先リストの先頭から検索を始めようとします。1件目の得意先名はDo~Loopの前のFindメソッドで検索されていて、すでにFormula1パラメータ用の変数に格納されているので、それ以上検索を続ける必要はありません。strAdrこのように記述しておく必要があります。動作確認では、さっそくこちらを動かしてみましょう!B5セルに『会社』と入力してエンターキーを押すと...この通り、ドロップダウンリストに検索にヒットした得意先名がすべて表示されていますね!最後に今回は、Changeイベントと入力規則のドロップダウンリストを組み合わせて、検索値にヒットする文字列をすべてドロップダウンリストに表示する方法をご紹介しました。Changeイベントをうまく使えば、もしかしたら普段使っているエクセルツールをもっと便利なツールにできるかもしれませんね。次回記事では、Findメソッドの使い方をご紹介していますので、今回の記事と併せてご覧ください。【エクセルVBA】指定した範囲内から値を検索するFindメソッドとその使い方エクセルシートに入力した値から、VBAのFindメソッドを使ってシート内を検索し、結果を返す方法をご紹介しています。Findメソッドは部分一致or全体一致、列方向or行方向など検索の条件を色々指定できて使い勝手が良いのでオススメですよ。tonari-it.com2018.06.27それでは、最後までお読みいただきありがとうございました!連載目次:エクセルVBAのイベントを使ってもっと便利なツールにしよう!エクセルVBAでリストの重複を排除する方法として、Dictionaryオブジェクトを使った重複排除の方法をご紹介しています。投稿者プロフィールノグチフリーランスPG・SE元製造子会社SE。業務システム保守・導入両方やった経験をお伝えすることで、少しでもシステムや手作業に悩まされる人が減ればいいなぁと日々夢見ております。「コミュニティ+講座」形式で継続性&教え合える環境も提供。!ノンプログラマーがプログラミングスキルを身に着ける支援ををするコミュニティ。セミナー・もくもく会・Facebookグループのサポートで「自らで学び続ける力をつける」支援、「教え合うことで学びの価値を上げる」場の提供をしています。関連記事Excel・VBA活用術エクセルVBAでテーブル操作~そのメリットと変換方法&ListObjectの取得エクセルVBAでテーブルを操作する方法について丁寧に解説していきます。今回は、テーブルとは、エクセルのデータリストをテーブルに変換する方法、VBAでテーブルをオブジェクトとして取得する方法をお伝えします。【エクセルVBA入門】マクロでVLookupメソッドを使ってデータを検索する方法エクセルVBAを使ってバラバラの経費精算書データを集約するシリーズの7回目です。今回は、業務で有効なテクニックとしてもう一つ、マスタシートからVLookupメソッドでデータを取得してくる方法についてお伝えしていきます。VBAで、メール送信時のイベントApplication_ItemSendの引数CancelにTrueを指定することで、メール送信をキャンセルする方法をご紹介しています。この方法を使えば、メール送信ボタンを押したときに、任意のメッセージを表示させたりすることができますよ!2019.03.08Excel・VBA活用術エクセルのゴールシークを使って目標から逆算して毎月の必要なアクションを知る過去データから回帰分析で未来の予測をすることができます。今回はエクセルのゴールシークという機能を使うことで、ゴールである目標値から逆算をして前提となる数値を求める方法についてお伝えします。VBAにキーボード入力をさせる方法エクセルVBAで、キーボードのキーを押したときと同じ動きをさせるSendKeysステートメントを、プルダウンメニューのオープンを例にしてご紹介しています。このステートメントを使えば、プルダウンメニューの手動オープンのように、大した手間ではないれど、何度もやるとなると面倒...という場合の解決策になるかもしれません。【エクセルVBA】FileSystemオブジェクトの基本と簡単にフルパスを生成・分解する方法FileSystemオブジェクトを使ってエクセルVBAでファイルやフォルダを取り合う使う方法。今回は、FileSystemオブジェクトの基本と、ファイルのフルパスを簡単に生成・分解する方法ついてお伝えします。「コミュニティ+講座」形式で継続性&教え合える環境も提供。詳細・お申込はこちら隣ITを応援する当ブログを「応援したい!」「役に立ったよ!」というお気持ちを、コチラからお支払いただくことができます。日々更新の活力倍増いたします。※PayPalアカウントが必要です。※金額は変更可能です。Platform23HTML・CSS32iPhone・アプリ29Monaca・スマホアプリ開発7PC全般・Windows86PowerPoint・VBA28Python87RPA1VBScript15Vue.js・JavaScript13WEBサービス・クラウド68WordPress・ブログ88Word・VBA42コミュニティ・イベント108セミナーのお知らせ11チャットワーク72データ分析・アクセス解析40仕事・生産性・働き方73動画配信8商業出版21起業・フリーランス72電子書籍・Kindle3運営会社プランノーツは"IT×働き方改革"をお手伝いする会社です。「コミュニティ+講座」形式で継続性&教え合える環境も提供。詳細・お申込はこちら隣ITを応援する当ブログを「応援したい!」「役に立ったよ!」というお気持ちを、コチラからお支払いただくことができます。日々更新の活力倍増いたします。※PayPalアカウントが必要です。※金額は変更可能です。Platform23HTML・CSS32iPhone・アプリ29Monaca・スマホアプリ開発7PC全般・Windows86PowerPoint・VBA28Python87RPA1VBScript15Vue.js・JavaScript13WEBサービス・クラウド68WordPress・ブログ88Word・VBA42コミュニティ・イベント108セミナーのお知らせ11チャットワーク72データ分析・アクセス解析40仕事・生産性・働き方73動画配信8商業出版21起業・フリーランス72電子書籍・Kindle3運営会社プランノーツは"IT×働き方改革"をお手伝いする会社です。RowSourceプロパティとAddItemメソッド2020.01.21GASでGoogleスライドのSlideオブジェクトの概要とその取得方法2020.01.162020.01.24Facebookページいつも隣にitのお仕事タイトルとURLをコピーしました。