リストボックス・コンボボックスのRouSorceプロパティに他ブックのシートを指定するには?
Question 5313 拙著「Excel VBA そのまま使える実用マクロ 500連発」 Previous Next
詳細は こちら サンプルNo.416 ◆ 環境・・ Excel 2003 & Windows XP

構文のrowsourceに、表示していないブックのシートのセル範囲を指定したいのですが、activate関数でブックを指定して、ブックを表示したままだと、リストボックスにセル範囲が表示されるんですが、ブックを非表示にするとエラーが出ます。 ブックを非表示にしてリストボックスにデータを表示させたいのですが、なにかいい方法はありませんか?。
Answer   Copyright (C) 2009.10.5 永井善王
拙著 「Excel VBA そのまま使える実用マクロ 500連発」 を お求めいただき、ありがとうございます。
No.416は 「コンボボックス・リストボックスで複数列のリストを制御する」と題するマクロで、下記2つの機能があります。

1.コンボボックスとリストボックス
 に複数列のリストをセットする
2.コンボボックスとリストボックス
 の複数列のリストの幅を設定する

リストのソースデータとして、ユーザーフォームと同一ブックにある「名簿」シート(下図)を指定しています。


リストボックスのリストを設定するコードは、下記のとおりになっています。
    With ListBox1
        .ColumnCount = 2
        .BoundColumn = 2
        .RowSource = "名簿!A2:B10"
    End With

ご質問は、リストボックスのリストに、前もって開いてあるがアクティブでない他のブックのシートを指定するには、上記コードの4行目をどのように書けばよいかということですね。
結論から申し上げます。
他のブックの名前を 「500_3_416u.xls」、シートの名前を「名簿」とすると、コードを下記のように修正しましょう。
    With ListBox1
        .ColumnCount = 2
        .BoundColumn = 2
        .RowSource = "[500_3_416u.xls]名簿!A2:B10"
    End With
なお、丁寧に記述したければ下記のようにしても構いません。
        .RowSource = Workbooks("500_3_416u.xls"). _
            Worksheets("名簿").Range("A2:B10").Address(External:=True)
さっそく試してみて、上手くいったらご連絡ください。

サンプルブックのダウンロード
下記2つのブックを、ダウンロードして一旦ハードディスクに保存し、後で改めて開いてから実行してください。
 ・「名簿」シート 500_3_416u.xls(17KB)
ここをクリック
 ・ マクロブック 500_3_416r.xls(68KB)
ここをクリック
ありがとうございました
うまく行きました。早速のご回答ありがとうございました。
エクセル4.0マクロで多くのプログラムを作り業務の助けとしてまいりましたが、イヨイヨ動きに信頼性がなくなり、老骨に鞭打ってVBAで作り直しています。
他の2冊も購入します。 よろしくお願いします。

 

Excel VBA Macro