第11回 リストボックスのリストを動的に作る

診断(11)
リストボックスのリストを動的に作る

「ゲール君，どうしたんじゃ？元気がないぞ」
「藪医者にふられました」
「おや，まぁ。何があった？」
「他の女性に乗り換えらました。あの人，○○フェチだったんです」
「なに～！」
「私のようないい女をふるなんて…」
「ゲール君，人間の半分は男じゃ，そう落胆するな。君のかたわらにも…」
「でも，気を取り直して，また合コンに励みます。というわけで，さっさと仕事を片付けましょう！」
「ああ，うん。なんだかなぁ…」

「ふむふむ，五里霧中のご様子じゃな」
「えっ，ゴリラが夢中？」
「君の耳はどうなっとるんじゃ，まったく…。さて，ゲール君。一般にVBA（Visual Basic for Applications）は，BASICに基づく言語仕様なので簡単だと宣伝されているな？」
「はい！ 私もそう思います」
「そう，確かに，条件分岐やループの構造などはBASICであるから，BASIC系の言語を学んだ人にはわかりやすい。しかし，Excelのオブジェクト構成やプロパティ，メソッドなどは，BASICには直接関係のない独自なものなので，一朝一夕にはマスターできないんじゃ」
「オブジェクト，メソッド，プロパティ，イベント，ステートメント，演算子，関数…。いっぱいありますからねぇ」
「一応，リファレンスマニュアルというものがあって，それを見ればわかることになっておる。しかし，あの内容は，初心者には古文書みたいなもんじゃ。“さっぱり意味がわからん”と言う人は多い」
「じゃあ，皆さんどうしているんですか？」
「このクリニックが繁盛しとる理由がそこにある」
「え？ どういうことでしょう？」
「先人の知識を拝借するということだよ。リファレンスマニュアルでは，プロパティやメソッドなどのキーワードを知らなければ，調べようがないんじゃ。そういうときは，知っている人に聞くのが一番の近道」
「なるほど“年をとったら亀のよう”ってやつですね」
「それを言うなら“亀の甲より年の劫”じゃ」
「そうそう，それです。で，この方への処方は？」

「リストボックスのリストの作成方法は三つある。が，ダイナミックが条件となると二つじゃな」
「ダイナミックというのは，大胆にということですか？」
「カタカナ英語では，そういう意味に使われることが多いが，この場合のDYNAMICは『動的』という意味じゃ」
「動的？」
「そう，反対語には『静的』という言葉がある。英語ではSTATIC」
「言語明瞭，意味不明」
「つまりじゃ，リストボックスのリストを，固定の状態ではなくて，その時々の状況に応じて変更したいということなんじゃ」
「状況に応じて？」
「そう，状況に応じて。どういうことかは，サンプルで理解したまえ。まずは，基本から始めよう。セルに並べた文字列や数値をリストにする方法じゃ」
「お願いします」

　最初に，セルにリストにしたい文字列や数値などを並べます。ここでは，セルA1からA8に記述しました（図1）。次に，VBEを開いてユーザーフォームを挿入し，フォーム上にリストボックスを配置します。プロパティウィンドウのRowSourceプロパティに「SHEET1!A1:A8」と代入します（図2）。

図1●リストにする文字列をセルに記述して並べる

図2●リストボックスのRowSourceプロパティに，セルのシート名とアドレスを代入 [画像のクリックで拡大表示]

　このユーザーフォームを実行すると，指定したセルの文字列がリストとなって，リストボックスが表示されます（図3）。

図3●リストボックスにセルの内容が表示される

「先生，早くも一件落着ですか？」
「これこれ，これはあくまでも基本じゃ。まだ1行もコードを書いておらんではないか」
「そうでした。あれ？『SHEET1』は全部大文字ですか」
「いや，RowSourceプロパティは大文字と小文字を区別しないようなので，どちらでもよい」
「シート名とセルのアドレスの間にあるビックリマーク（！）は何ですか？」
「ビックリマークではない。エクスクラメーションマークと呼ぶ」
「どっちでもいいじゃないですか。失恋したての女をいじめると泣いちゃうぞ！」
「わかった！ わかったから泣くな。このエクスクラメーションマークは，シート名とセルのアドレスを区切る記号じゃ。数式で他のシートのセルを参照するときに使うのと同じじゃよ」

「シート名は絶対必要なんですか？」
「難しい質問じゃな。ワークシートが1枚の場合と，2枚以上の場合で答が違う」
「1枚の場合は？」
「ワークシートが1枚の場合は，必要ない」
「2枚以上の場合は？」
「おおむね必要」
「あいまいな答ですねぇ。シート名を省略すると，どうなるんですか？」
「正確に言うならば，ユーザーフォームを表示したときに，アクティブなシートのセルにある文字列がリストになる。例えば，ワークシートが2枚あるブックで，RowSourceプロパティの値を“A1:A3”にしたとする。Sheet1がアクティブなときにユーザーフォームを表示すると，Sheet1のセル“A1:A3”にある文字列がリストになる。一方，Sheet2がアクティブなときにユーザーフォームを表示すると，Sheet2のセル“A1:A3”にある文字列がリストになるということじゃ」
「それって問題なんですか？」
「必ず，リストにする文字列があるシートがアクティブな状態でユーザーフォームが表示されるのであれば，問題はない。しかし，そうでない場合は問題が起きるじゃろうな」
「う～ん，要するに，シート名を必ず付けておけば，問題は起きないってことですね」
「そういうことじゃ」

「で，次は？」
「次は，この操作をマクロにする。前回，ワークシートのActivateイベントは学習したな」
「はい」
「ユーザーフォームにも，Activateイベントがある。ユーザーフォームがアクティブになった際に実行されるUserForm_Activateプロシジャに，リストボックスのRowSourceプロパティを設定するステートメントを記述するんじゃ」
「プロパティウィンドウの設定をコードにしただけじゃないですか。あまり意味がないような気がしますが」
「ステップの一つじゃよ」
「わかりました」

　前述のリストボックスのRowSourceプロパティの値を削除し，ユーザーフォームをダブルクリックして，ユーザーフォームのモジュールを表示してください。コードウィンドウ右上にあるプロシジャボックスでActivateをクリックして，UserForm_Activateプロシジャを作成します（図4）。

図4●UserForm_Activateプロシジャのひな型を作る [画像のクリックで拡大表示]

　勝手に作成されたUserForm_Clickプロシジャは必要ないので削除し（図5），UserForm_Activateプロシジャにリスト1のコードを記述します。

図5●UserForm_Clickプロシジャは不要なので削除する

Private Sub UserForm_Activate()
  Me.ListBox1.RowSource = "SHEET1!A1:A8"
End Sub

　このユーザーフォームを実行すると，図3と同じリストボックスが表示されます。