エクセルＶＢＡのコンボボックスについて教えてください

質問

こんにちは。
エクセル2007のコンボボックス（ユーザーフォーム）
の質問です。

　　　Ａ　　　　Ｂ　　　　　Ｃ
１　さんま　　塩焼き　　さしみ
２　まぐろ　　さしみ　　ステーキ
３　さば　　　味噌　　　ばってら

上記のようなデータがあるとします。
名前の定義にて
Ａ１：Ａ３に”魚”という名前を付けます。

Ｂ１：Ｃ１に”サンマ”
Ｂ２：Ｃ２に”マグロ”
Ｂ３：Ｃ３に”サバ”
という名前をそれぞれ付けます。

それぞれの名前を
プロパティのＲｏｗＳｏｕｒｃｅ
へ入力しコンボボックスの項目を表示したのですが、
”魚”と入力した時は”さんま””まぐろ””さば”と表示されたのですが
”サンマ”と入力した時は”塩焼き”のみ表示されました。
”マグロ””サバ”も同様にＢ列しか表示されません。

どのようにしたら”塩焼き””さしみ”と表示
出来るのでしょうか？

どなたかよろしくお願いします。

回答No.2

Wendy02 · ベストアンサー

基礎的なことを抜きにして、単に、テクニック上の問題を質問しているようですが、ワークシートの入力規則のリストと、VBAは、まるっきり違います。本来、VBAのプログラマでは、このようなComboBox の使い回しなどいうことはしません。これは、実験的な意味しかありません。コードがややこしくなるだけだと思います。

以下では、名前定義は、「魚」というものしか使っていません。
同じように、「野菜」「果物」というリストを作れば、同じように出てきます。

本来、VBAでは、名前定義を読むというのは、一旦、インターフェースで、プロパティを読んで、Rangeオブジェクトに入れ替えているのですから、あまりホメられた方法ではありません。使えなくなるトラブルが発生することがありますから、あまり数を増やさないことです。

以下は、解説やサポートはしません。もし、コードを読んで分かるなら、以下のようにしたら可能です。もともと、RowSource は不要です。以下のコードを生かすも殺すも、ご質問者自身です。

'=========================================
'
Private myVal As String '第一階層の項目
Private iStep As Integer '階層を取る
Private Sub UserForm_Initialize()
'不要なRowSource は削除する
　ComboBox1.RowSource = "" 'RowSource が入れていなければ、不要行
　'第0階層を入れるなら、以下のように入れる
　ComboBox1.List = Array("魚", "野菜", "果物")　'初期メニュー
End Sub
Private Sub ComboBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
'値が入っている状態で、ダブルクリックすれば、初期メニューに戻る
　If ComboBox1.Value <> "" Then
　　iStep = 0
　　ComboBox1.List = Array("魚", "野菜", "果物")
　End If
End Sub

Private Sub ComboBox1_Change()
　　If ComboBox1.Value = "" Then Exit Sub
　　If iStep = 0 Then
　　　　'第一階層
　　　　If ComboBox1.ListIndex > -1 Then
　　　　　　myVal = ComboBox1.Value '現在は、野菜、果物の選択肢はない
　　　　　　iStep = 1
　　　　　　Call ComboBoxListing1
　　　　　　ComboBox1.ListIndex = -1
　　　　End If
　　ElseIf iStep = 1 Then
　　　　'第二階層
　　　　iStep = 2
　　　　Call ComboBoxListing2(ComboBox1.ListIndex)
　　　　ComboBox1.ListIndex = -1
　　Else
　　　　iStep = 0
　　End If
End Sub
Sub ComboBoxListing1()
　　'第一階層
　　Dim myList
　　On Error Resume Next
　　myList = Application.Transpose(Range(myVal))
　　ComboBox1.List = myList
　　On Error GoTo 0
End Sub
Sub ComboBoxListing2(Lindx As Integer)
　　'第二階層
　　Dim myList
　　Const 列 As Integer = 2
　　On Error Resume Next
　　myList = Range(myVal).Cells(Lindx + 1).Offset(, 1).Resize(, 列).Value
　　ComboBox1.List = Application.Transpose(myList)
　　On Error GoTo 0
End Sub

'-------------------------------------------

お礼

Wendy02さん、丁寧なご回答ありがとうございます。非常に勉強になります。教えていただいた回答により納得することが出来ました。
ありがとうございました。

回答No.1

keithin

横に並んでいるモノを「Row」Sourceには出来ません。
＃コンボボックスのColumnCountを２にしてみると，１行×２列の横並びで「塩焼き　　さしみ」と登録されているのを確認できます。

まぁ一番手っ取り早いのは，UserFormのInitializeイベント辺りを使ってAddItemしてしまう手かもしれませんが，折角ですから。
UserFormをＷクリックして現れたシートに

Private Sub UserForm_Initialize()
　Me.ComboBox2.List = Application.Transpose(Range("サンマ"))
　Me.ComboBox3.List = Application.Transpose(Range("マグロ"))
　Me.ComboBox4.List = Application.Transpose(Range("サバ"))
End Sub

のようにして「実行時に」登録してみます。事前にプロパティウィンドウで「デザイン時」の設定はしないでおきます。

お礼

keithinさん、早速のご回答ありがとうございます。
＞横に並んでいるモノを「Row」Sourceには出来ません。
入力規則にて名前を使用した時のように、行も列も関係なく表示されるものだと思っていましたが、出来ないのですね・・。
最悪はデータを縦にするしかないですよね。
上記の方法ためさせていただきます。