エクセルVBAコンボボックスについて

質問

図の左のように、商品リスト欄のセルA3から下方向に大分類（A16まで14種類）
セルB3～O3（Bから数えて14個目のO）列から下方向に小分類があります。
P2～P11には1から10までの数字が入っています。

図の右側がユーザフォームで、コンボボックスの番号を入れています。
コンボボックス「1.4.・・・28.」までは大分類を選べるようにして、
コンボボックス「2.5.・・・29.」までは左の大分類に応じた小分類の値を表示させたいと思っています。

大分類「111」→小分類「あ～そ」　大分類「222」→小分類「た～と」といった具合です。
そのコンボボックスの値を指定したセルに入力しようと思っています。

下のように記述した結果、問題が発生しました。
(1)大分類で14個ほどあるリストの3つほどしかでてこない。
(2)Select Caseの構文を使用していて、大分類が14個だから「Case 13」までと
しているが、「大分類の数-1」までの変えられる数までにしたい。
アドバイスをいただけると助かります。

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

Private Sub UserForm_Initialize()

Dim MyVar1 As Variant
MyVar1 = Sheets("商品リスト").Range("A3:A" & Range("A3").End(xlDown).Row)

Dim MyVar20 As Variant
MyVar20 = Sheets("商品リスト").Range("P2:P" & Range("P2").End(xlDown).Row)

'種類欄を指定
With ComboBox1
.List() = MyVar1
End With

※・・・（省略）・・・　コンボボックス4から25まで※

With ComboBox28
.List() = MyVar1
End With

'数量欄を指定
With ComboBox3
.List() = MyVar20
End With

※・・・（省略）・・・　コンボボックス6から27まで※

With ComboBox30
.List() = MyVar20
End With
End Sub

'1番目
Private Sub ComboBox1_Change()

Dim MyVar1 As Variant

※・・・（省略）・・・ MyVar2～13 As Variant※

Dim MyVar14 As Variant
Dim MyVar20 As Variant

MyVar1 = Sheets("商品リスト").Range("A3:A" & Range("A3").End(xlDown).Row)

※・・・（省略）・・・ B列からＮ列まで※

MyVar15 = Sheets("商品リスト").Range("O3:O" & Range("O3").End(xlDown).Row)
MyVar20 = Sheets("商品リスト").Range("P2:P11")

Select Case ComboBox1.ListIndex
Case 0
With ComboBox2
.List() = MyVar2
End With

※・・・（省略）・・・ Case1～ Ｃａｓｅ12 ※

Case 13
With ComboBox2
.List() = MyVar15
End With

End Select

End Sub

回答No.1

keithin · ベストアンサー

＞14個ほどあるリストの3つほどしかでてこない。

表示だけの問題でなく確かに３つしか載ってない事を確認済みなのでしたら，あと間違ってる可能性があるのは

次の行：
MyVar1 = Sheets("商品リスト").Range("A3:A" & Range("A3").End(xlDown).Row)

で，実行中に間違ったシートが開かれているという状況でしょうか。

正しい書き方：
MyVar1 = Sheets("商品リスト").Range("A3:A" & Sheets("商品リスト").Range("A3").End(xlDown).Row)

実際には，書いたマクロをただ漫然と実行して「うごいたー　とまったー」と一喜一憂してるんじゃなく，アヤシいところでデバッグを行い，あなたの書いた１行１行が確かに意図通りに動作しているのか，その一行一行で変数の値や実際に取得している値の中身や範囲は具体的にどうなっているのか調べ，マクロの動作をトレースして間違いを探してください。
具体的には「３行しか入ってない」その仕込みをしたまさにその瞬間，myVar1の実際の中身はどうなっていたのか，何故そうだったのか，といったことですね。

参考：上から下までよく目を通してください。
http://members.jcom.home.ne.jp/rex-uchida/vba110.htm

＞可変にしたい

Variant配列を宣言して利用してみましょう。
最大個数(20)は「デザイン時にはフィックスしている」ものとします。

Private Sub ComboBox1_Change()

Dim MyVar(20) As Variant
dim i
dim A_max

A_max = Sheets("商品リスト").range("A65536").end(xlup).row - 2
for i = 2 to A_max
MyVar(i) = Sheets("商品リスト").Range(Sheets("商品リスト").cells(3, i) , Sheets("商品リスト").cells(65536,i).End(xlup))
next i
combobox2.list() = myvar(ComboBox1.ListIndex+2)
End Sub

＃実際には，Initialize時点でパブリックなmyVar(20)にデータを取得しておき，各実行時に毎回毎回シートを調べに行ったりしないで済ませるような，仕込みをすると良いんじゃないかな？と思います。

お礼

＞正しい書き方：
＞MyVar1 = Sheets("商品リスト").Range("A3:A" & Sheets("商品リスト").Range("A3").End
＞(xlDown).Row)
ここを直したらうまく行きました！

ありがとうございました。

回答No.2

Azzz___

こんにちは。

直接の回答ではないので恐縮ですが

>(2)Select Caseの構文を使用していて、大分類が14個だから「Case 13」までと
こちらの方はクラスモジュールを使う方法があると思います。
(というか、それしか私には分かりませんでしたが、、)

複数のコンボボックスのイベントプロシージャで、基本的に同じ処理をしているという前提ですが、
その場合、クラスモジュールを使えば一つのプロシージャで代替できると思います。
(もっともこの場合、Case13までの記述は必要になりますが1回の記述で済むものと思われます)
そもそも質問者さんの説明が理解できているか不明なのと、クラスモジュールの
説明は「雛型」、「インスタンス」の概念の理解レベルなどで説明方法変わりが難しいですので
細かい点は遠慮させて頂きます点ご容赦です。

他の回答者が言われているように配列にする方法もありますが、この方法でも可能だと思います。
なお、この場合は、

Private Sub ComboBoxXXX_Change()

の部分は省略できず、コンボボックスの数だけChangeイベントを書く必要が出てきます。
(ただ、コンボボックスの名前を工夫すれば共通のプロシージャを1個用意し、
ある程度は楽にできるかもしれません。更に私は分かりませんが、呼び出し元のプロシージャ名を取得できる方法があれば尚良しの感じにできそうですが)

下記で似たようなこと語っています。
http://okwave.jp/qa/q6539523.html

なお、
>(1)大分類で14個ほどあるリストの3つほどしかでてこない。
こちらは別の回答者の方が言われているように、コードのミスか何かではないでしょうか？
該当部分を絞って質問された方が的確な回答が得られるかと思います。