VBAでの列参照によるリストボックス（重複排除）の作成方法について

エクセルのVBAの機能を利用し
以下のような機能を実装したいと考えております。

イメージ図のような生徒の成績表があったとして、
C列に生徒の名前が記入されているものとします。

そして、成績表の名前の列を参照し
重複を排除した状態でG4のセルにてリストボックスを
作成したいと考えています。

また、リストボックスよりユーザー名を指定すると
人数と書かれたセルの横に、重複しているユーザの人数を表示したいと
思っております。
＃今回のヤマダの場合、「２」と表示したい。

画像でのイメージ図としては以下になります。
![]

恐れ入りますが、ご教授いただけると幸いです。
宜しくおねがいします。

行動規範の内容に同意します

回答2件

ベストアンサー

G4セルの入力規則でリスト表示する項目をVBAで作成し、選択した項目の件数をG9セルに表示したい、ということで大丈夫でしょうか？

G9セル：一致する候補のカウント

まずG9セルの方ですが（ここは問題とされているかわかりませんが）、COUNTIFというExcel関数を利用すればVBAを使わなくてもシート側で実現できると思います。

G4セル：重複しないリスト作成(VBAでの入力規則作成)

本題の重複しないリスト作成のほうですが、VBAを利用するのであれば

＝＝＝
①ディクショナリを作成する

②対象データ範囲(C5～9)をループ処理する

～ループここから～
③1行ずつC列のセルの値を取得し、ディクショナリに未登録(Dictionary.ExistsがFalse)ならディクショナリに追加する

④ディクショナリ追加した場合、同時にリスト作成用の文字列にも追加していく
（カンマ区切りで"ヤマダ,タナカ,サトウ"のように文字列連結）
～ループここまで～

⑤G4セルの入力規則をクリアする(``Range("G4").Validation.Delete`)

⑥ループで作成したリスト文字列をG4セルの入力規則でリスト候補として設定する
⇒Range("G4").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:=作成したリスト文字列
＝＝＝
といった流れで実現できると思います。

簡単にポイントだけ解説すると
・重複チェックを簡単にするためにディクショナリオブジェクトを使用しています。
・１行ずつ名前を取得し、未登録ならそれをディクショナリに登録していきます。
・入力規則の設定はRange.Validation.Addで行いますが、この時カンマ区切りの文字列でリスト候補を指定します。
⇒参考サイト

といった感じです。

参考になれば幸いです。

投稿2018/12/11 07:06