先月の記事「プロのヒント：Excelでのデータ入力を容易にするユーザーフォームの追加」では、VBAを使ってExcelユーザーフォームを作成し、リストコントロールにデータを入力する基本的な方法について説明しました。リストを更新するには、VBAプロシージャに項目を追加または削除します。今月は、シートレベルで入力されたデータを使ってコンボコントロールとリストボックスコントロールにデータを入力するテクニックを紹介します。
始める前に、シート レベルのリストに関する 2 つのアドバイスがあります。
- リストが1つしかない場合でも、専用のシートを使用してください。操作に慣れれば、リストを追加する機会が増える可能性が高いため、専用のシートは実用的で効率的、そして安全です。この例のリスト専用シートは、まさに「リスト」という名前が付けられています。
- 可能であれば、データとユーザーの使用方法に適した順序でリスト項目を入力してください。例えば、アルファベット順や数字順などです。リスト項目を常に順序通りに入力することは必ずしも現実的ではありませんが、可能な場合はそうしてください。事後的にリストの順序を付けると、作業が複雑になります。
範囲名を使用する
Excelのリストコントロールにデータを入力する最も簡単な方法は、シートレベルでリストを作成し、リストに範囲名を付けることです。そして、その範囲をコントロールの「値集合ソース」プロパティで参照します。これは最も簡単な方法ですが、柔軟性は最も低くなります。まず、図Aに示すようなリストが必要です。任意のリストで作業することも、サンプルの.xlsまたは.xlsmファイルをダウンロードすることもできます。
図A
専用シートにアイテムのリストを入力します。
リストを入力したら、名前の範囲を適用します。
- ヘッダー (A1:A6) を含むリストを選択します。
- [数式]タブをクリックします。
- [定義された名前] グループで、[選択範囲から作成] をクリックします。
- Excelは、表示されるダイアログ（図B）でデフォルトで「先頭行」オプションを選択します。「OK」をクリックします。
図B
これで、UserForm を追加する準備が整いました。
- [Alt]+[F11] キーを押して Visual Basic Editor (VBE) を起動します。
- [挿入]メニューから[ユーザーフォーム]を選択します。
- ツールボックスのコンボボックスコントロールをクリックし、ユーザーフォームをクリックして新しいコントロールを挿入します（図C）。
図C
- プロパティウィンドウが開いている場合は、新しいコントロール（選択したオブジェクト）のプロパティが表示されます。このウィンドウが表示されていない場合は、[F4]キーを押すか、[表示]メニューから[プロパティウィンドウ]を選択してください。
- コンボボックスが選択されたままの状態で、「Row Source」プロパティに「ClassRange」と入力します（図D）。これは、先ほど作成した範囲（リスト）の名前です（図Bを参照）。
図D
これで完了です！これで、[F5]キーを押すか、VBEの標準ツールバーにある「マクロ実行」ボタンをクリックして、新しいユーザーフォームをテストする準備が整いました。図Eは、コントロールを開いてリストを表示しているところです。これは簡単で、VBAの知識もほとんど必要ありませんでした。
図E
Excel は、ClassRange 名前付き範囲に格納されているデータを使用してコントロールにデータを入力します。
先ほど、この方法はおそらく最も簡単ですが、柔軟性が最も低いと述べました。その理由は次のとおりです。リストの末尾に項目を追加した場合、範囲名を更新して新しいセルを含めることを忘れなければ、その新しい項目はコントロールのリストに表示されません。行を挿入して項目を追加すると、Excel によって範囲名が更新され、新しい行が含まれるようになりますが、それでもこの操作を忘れずに行う必要があります。さらに、リストがアルファベット順に並んでいる場合は、並べ替えることも忘れずに行う必要があります。これは決して悪い解決策ではありませんが、必要なメンテナンスの量を減らすことは良い目標であり、次の方法、つまり Table オブジェクトを使用してリストを保存する方法への素晴らしいつながりとなります。
先に進む前に、なぜ動的な範囲の数式（「Excelのユーザーフォームのコンボボックスに動的なリストを入力する」）を推奨しないのかと疑問に思う方もいるでしょう。ほとんどの場合、このような複雑なソリューションは必要ありません。そして、これは私にとって最も苦手な方法です。なぜなら、分散ワークブックでは動的な範囲を維持するのが難しい場合があるからです。
テーブルオブジェクトを使用する
リストによっては、静的なものではなく、項目を追加したり、場合によっては削除したりする必要があるでしょう。ExcelのTableオブジェクトは、まさにこの用途に最適です。（TableオブジェクトはExcel 2007で新しく追加されたため、それ以前のバージョンでは使用できません。）手順は基本的に名前付き範囲と同じですが、範囲の代わりにTableオブジェクトを使用し、コントロールのRowSourceプロパティでそのTableオブジェクトを参照します。先ほどと同様に、図Fに示すようなリストが必要です。
図F
リスト項目を入力します。
最初のリストが完成したら、それを Table オブジェクトに変換する準備が整います。
- リスト内の任意の場所をクリックします。
- [挿入]タブをクリックします。
- 「表」グループで、「表」（図G）をクリックします。
図G
- デフォルト設定は正しいので、「OK」をクリックしてください。リストにヘッダーがない場合は、必ずそのオプションのチェックを外してください。Excelはテーブルオブジェクトを作成し、デフォルトのテーブル形式を適用します。
Excelは各テーブルにデフォルトの名前を割り当てます。この名前を知っておく必要があるので、テーブルを選択した状態で「デザイン」コンテキストタブをクリックします。左側の「プロパティ」グループに、テーブルのデフォルトの名前が表示されます。デフォルトの名前を使用することも、以下の手順でカスタム名を割り当てることもできます。
- テーブル名コントロール内をクリックして名前を選択します。
- 新しい名前を入力します（図H）。
図H
それでおしまい！
VBEに戻り、ユーザーフォームに2つ目のコンボボックスコントロールを追加します。そのコントロールを選択した状態で、図Iに示すように、新しいコントロールの「値集合ソース」プロパティに「ClassTable」と入力します。
図I
コントロールの行ソース プロパティを設定します。
[F5]キーを押してユーザーフォームを実行します。図Jは2番目のコントロールのリストを示しています。
図J
新しいコントロールには同じリストが表示されますが、ソースは異なります。
ここまでの手順は最初の名前付き範囲の方法とほぼ同じですが、ここで2つの方法が異なります。ユーザーフォームを閉じて「リスト」シートに戻ります。そこで、テーブルリストの一番下に新しい項目を追加します。C6を選択し、「Reptile」と入力します。Excelがテーブルを拡張して新しい項目を追加しない場合は、その項目を削除します。次に、セルC5を選択し、Tabキーを押してテーブルを拡張し、C6を含めて「Reptile」と入力します（図Kを参照）。
図K
テーブルに新しい項目を追加します。
VBEに戻り、[F5]キーを押してユーザーフォームを実行します。図Lに示すように、新しいリストにはReptileが含まれています。範囲名を更新したり、リストを並べ替えたりする必要はありませんでした。そのまま動作します。
図L
新しいコントロールは自動的に更新され、新しいアイテムである Reptile が含まれます。
どちらの方法もVBAコードはほとんど必要ありませんが、テーブル方式は範囲名方式よりも柔軟性に優れています。リストを作成する際は、メンテナンス性と使いやすさを念頭に置いてください。
Officeに関するご質問をお送りください
読者からの質問には可能な限りお答えしますが、必ずお答えできるとは限りません。ご連絡の際は、できるだけ具体的にご記入ください。例えば、「ワークブックのトラブルシューティングをして、問題点を修正してください」といった質問ではおそらく回答が得られないでしょうが、「この数式が期待どおりの結果にならない理由を教えていただけますか？」といった質問であれば、回答が得られるかもしれません。私はTechRepublicから時間や専門知識に対する報酬を受け取っておらず、読者から料金を請求することもありません。ご連絡は[email protected]までお願いいたします。
