コンボボックスのListFillRangeの変更について

質問

皆様、こんにちは。
いつもお世話になっております。
条件によってコンボボックスのListFillRangeを変更させたいですが、可能でしょうか？
次のように書いてみましたが、動きません。。
If Range("D2") = 1 Then
ActiveSheet.Shapes("DropDown63").ListFillRange = "$C$44:$C$54"
ElseIf Range("D2") = 2 Then
ActiveSheet.Shapes("DropDown63").ListFillRange = "$C$55:$C$73"
End If
「指定した名前のアイテムが見つかりませんでした」というメッセージが出てしまうんですが、コンボボックスの名前の書き方が間違っているでしょうか？
ご存知でしたら何方か教えていただければ非常に助かります！
どうぞよろしくお願いいたします。

回答No.3

Wendy02 · ベストアンサー

こんばんは。

その辺りは、きちんと、人に教えてもらわないと難しいですね。
今は、もうここら辺りの説明は本には出ていません。Ver.5 のマニュアルに書かれていますが、書籍は、手に入らないと思います。

"DropDown63"　この名前は記録マクロからだと思いますが、確かですか？

補足情報から書くと、#1 さんの使っている、DropDowns は、隠しオブジェクトと言われています。これは、Ver.5 の書き方です。いろんな書き方がありますが、私は、この　DropDowns にIndex で書く方法が間違いが少ないと思います。

"DropDown63"　名前の、63ですが、このドロップダウンが63個もあるでしょうか。

最初に、分かりやすい書き方としては、別途、

ActiveSheet.DropDowns("ドロップ 1").Select

という書き方があります。ただ、名前ボックス（ワークシートの左上端)に出ている名前を正確に入れます。

ドロップ 1　（半角の数字の前は半角のスペース)
ドロップ 63　ということになります。

「Shapes コレクション」にすると、

ActiveSheet.Shapes("Drop Down 63").DrawingObject.ListFillRange = "C44:C54"　（絶対参照でなくても良い)
自動的に入れたものは、こんなスタイルになるはずです。

この方法でも可能なはずです。（半角空白を守ってください)
ActiveSheet.Shapes("ドロップ 63").DrawingObject.ListFillRange = "C44:C54"

これは、Office 2000からの書き方ともいえるのですが、もともと、ドロップダウンなどは、フォームツール、Ver.5 のものですから、その方法で書いた方法がよいです。Office 2000以降は、コントロールツールを使うのが推奨されています。

しかし、私は、こんな方法で書いています。

順序良く並んでいるものとすると、名前は何であれ、作られた順に、
1番目は、Index が　1になります。
ActiveSheet.DropDowns(1).Select

「1」から順番にIndex が付きますから、途中で削除しても、順位が繰り上がるだけです。
ActiveSheet.DropDowns(1).ListFillRange = "C44:C54"

いろいろ研究してみてください。フォームツールは、マクロで設定するときに便利です。

補足

Wendy02様、ご親切に教えていただき本当にありがとうございました。

ドロップダウンの名前が確認でき（ドロップ 63でした）、
ActiveSheet.Shapes("ドロップ 63").DrawingObject.ListFillRange = "C44:C54"
の方法で書いたら確かに動きました！！

もう一つ教えていただいたIndexの方法なんですが、

>作られた順に、1番目は、Index が　1になります。

と書いてありますが、ドロップダウンが作られた順番のことでしょうか？この場合は、Indexは63になるでしょうか？

"DropDown63"　名前の、63については、63個もありません。前に作ったり消したりしたものから順番に数えられているでしょうか？もしそうであれば、これを中止させる方法がありますでしょうか？

それと、もとの質問と関係がありませんが、初心者向けのVBAの分かりやすい教科書等をご存知でしたら、教えていただけないでしょうか？

色々聞いてしまって申し訳ありませんが、
どうぞよろしくお願いいたします。

回答No.4

Wendy02

こんにちは。

正直な話、私は、最初のころ、ここらは、さっぱり分からなかったです。誰かに教われば、覚えるのは早かったのですが、あまり詳しい人は少ないようです。フォームツールは、最終的には、DialogSheet を扱うためのツールです。DialogSheet は、シートの挿入で出てくるシートのひとつです。

>ドロップダウンが作られた順番のことでしょうか？
>これを中止させる方法がありますでしょうか？

これは、止める方法がないので、シートを新しくするしかないそうです。名前自体は、変えれば分かりませんが、"ドロップ 63"とかの63というのは、作っていった順番の数でも、そのオブジェクトを削除しても、その（内部的)数は、どんどん増えていくのです。この63の上限は、論理的には、100万を越える数まで可能のようですが、実際、1,000を越える頃から、ワークシートが変な状態になるような気がします。ワークシートの中に、このカウントの記録が残っています。（確認はOffice 2003 までです)

Index 側は、削除すると、繰り上げになります。

名前とインデックスの関係は、このマクロで分かります。

Sub FormCheckTest()
　　Dim o As Object
　　For Each o In ActiveSheet.DropDowns
　　　　MsgBox "Name : " & o.Name & vbCrLf & _
　　　　　　　 "Index： " & o.Index
　　Next o
End Sub

------------------------------------------
私は、Ver.5 マクロのMSのマニュアルは持っていますが、市販の本の方がわかりやすかったです。古本屋さんで見つけました。

Ver.5 のヘルプより

構文
object.DropDowns(index)

ドロップダウン リスト ボックス コントロールの名前またはインデックス番号を指定します (複数のコントロールを指定するには、配列を使います)。

構文
object.ListFillRange

Microsoft Excel では、指定したセル範囲に含まれる各セルの内容を読み込み、読み込んだ値をリスト ボックスに入力します。指定したセル範囲内のセルに加えられた変更は、リスト ボックスにも反映されます。
リスト ボックス内のリストの作成に AddItem メソッドが使われているとき、このプロパティは空の文字列 ("") を返します。

---------------------------------------------

初心者向けのVBAの分かりやすい教科書等

多くの人は、これで始めているようです。

『かんたんプログラミング Excel2003 VBA 基礎編』技術評論社 (2004/02)
大村 あつし (著)
http://www.amazon.co.jp/exec/obidos/ASIN/4774119660

または、

『かんたんプログラミング Excel 2007 VBA 基礎編』技術評論社
(2008/1/8)大村 あつし (著)
http://www.amazon.co.jp/exec/obidos/ASIN/477413340X

これは、三部作です。しかし、このシリーズの後に続く教科書が見当たらなくなってしまいました。

それから、Office のバージョンにによって、Windows Common Controls 6.0が使えるようになっていますが、この使い方を書いてあるマニュアルがありません。(VB6のマニュアルにはありますが、もはや手に入れにくいです。)

お礼

Wendy02様、ありがとうございました！
IndexやObjectのことなどを含めて、大変勉強になりました。
教えていただいた本を購入して積極的に勉強しようと思っております。
今後ともどうぞよろしくお願いいたします。

回答No.2

masa_019

コンボボックスを選択した状態で、
イミディエイトに、
? selection.name
と記述してEnterで名前がわかります。

お礼

masa_019様、ありがとうございました。
名前はドロップ63でした^^;
これからもどうぞよろしくお願いいたします。

回答No.1

masa_019

こんにちは。

名前が"DropDown63"で間違いないのなら、

ActiveSheet.DropDowns("DropDown63").ListFillRange = "$C$44:$C$54"

としてみてはどうでしょう。

補足

masa_019様、返事をどうもありがとうございました。教えていただいたようにやってみましたが、「WorksheetクラスのDropDownsプロパティを取得できません」というメッセージがでます。
それと、コンボボックスの名前を確認する方法を教えていただけないでしょうか？私はフォームのメニューにある「コードの編集」というところをクリックし、出てきたSubの名前を使っただけですので、間違っているような気がします。。