コンボボックス３つを連動させたい

VBA初心者なのですが、参考書やインターネットを勉強しながら取り組んでいます。行き詰っているので助け舟を頂戴できればと思います。
やりたいのは1800アイテムほどある食品リストからコードと食品名を検索するプログラムです。

もとになるデータは、B食品群、C食品コード、D食品名、あとは成分値が右にずっと続いています。
コンボボックスを３つ作り、食品群、食品コード、食品名が表示されるようにしました。
これらを連動させ、
１食品群ー食品名で探す
２コードー食品名で探す
３食品名を直接入力して探す
いずれにも対応できるようなもの、最終的には任意のセルに選択した食品名及びその食品のコードを表示できるようにしたいのです。

リスト範囲は
Private Sub UserForm_Initialize()
'食品群のリスト範囲’(食品群は18種)
ComboBox1.RowSource = "処理!B3：B20"←リスト選択用に見出しの部分だけ別シートに作りました
'食品名のリスト範囲’
ComboBox2.RowSource = "食品成分表!D4：D1885"
'食品コードのリスト範囲’
ComboBox3.RowSource = "食品成分表!C4：C1885"
End Sub
とし、
Private Sub comboBox1_Change()
'食品群リスト、食品名リストを連動させる’
Dim selIndex As Integer
selIndex = Me.ComboBox1.ListIndex
Select Case selIndex
Case 0
Me.ComboBox2.RowSource = "食品成分表!D4:D146"
＜以下Case 18まで続く＞
End Select
End Sub

と、食品群と食品は連動できたのですが、食品群とコード、コードと食品名がつなげられません。
Addltemでひとつづつ書いてもみましたが、1800ともなると量が多すぎるようでエラーになってしまいます。
よろしければアドバイスをお願いします。


　　

No.4 ベストアンサー 回答者： papayuka 回答日時： 2004/10/05 00:26

結果を別に持つ方がやりやすいかと思います。

UserForm に ComboBox を3つ、ListBox を1つ、CommandButton を1つ置いて
試してます。
AddItem でやってますのでソートされてなくても大丈夫ですが、ソート
されているなら #3さんのような工夫をされた方が速いでしょう。

読み取ったイメージだけで作ってますので、的外れかも知れませんし、
そのままでは動かないかも知れません。

試すなら元ブックのコピーを用意するなどテスト環境で。

'----------------------------------------------------------------
Private Sub UserForm_Initialize()
　　ComboBox1.RowSource = "処理!B3：B20"
　　ComboBox2.RowSource = "食品成分表!D4：D1885"
　　ComboBox3.RowSource = "食品成分表!C4：C1885"
　　ListBox1.ColumnCount = 3
　　CommandButton1.Enabled = False
End Sub
'----------------------------------------------------------------
Private Sub ComboBox1_Change()
Dim c, first As String
ListBox1.Clear
CommandButton1.Enabled = False
With Worksheets("食品成分表").Range("B3:B1885")
　　Set c = .Find(Me.ComboBox1.Text, LookIn:=xlValues)
　　If Not c Is Nothing Then
　　　　first = c.Address
　　　　Do
　　　　　With Me.ListBox1
　　　　　　.AddItem c.Text
　　　　　　.List(.ListCount - 1, 1) = c.Offset(0, 1).Text
　　　　　　.List(.ListCount - 1, 2) = c.Offset(0, 2).Text
　　　　　End With
　　　　　　Set c = .FindNext(c)
　　　　Loop While Not c Is Nothing And c.Address <> first
　　End If
End With
End Sub
'----------------------------------------------------------------
Private Sub ComboBox2_Change()
Dim c, first As String
ListBox1.Clear
CommandButton1.Enabled = False
With Worksheets("食品成分表").Range("D3:D1885")
　　Set c = .Find(Me.ComboBox2.Text, LookIn:=xlValues)
　　If Not c Is Nothing Then
　　　　first = c.Address
　　　　Do
　　　　　With Me.ListBox1
　　　　　　.AddItem c.Offset(0, -2).Text
　　　　　　.List(.ListCount - 1, 1) = c.Offset(0, -1).Text
　　　　　　.List(.ListCount - 1, 2) = c.Text
　　　　　End With
　　　　　　Set c = .FindNext(c)
　　　　Loop While Not c Is Nothing And c.Address <> first
　　End If
End With
End Sub
'----------------------------------------------------------------
Private Sub ComboBox3_Change()
Dim c, first As String
ListBox1.Clear
CommandButton1.Enabled = False
With Worksheets("食品成分表").Range("C3:C1885")
　　Set c = .Find(Me.ComboBox3.Text, LookIn:=xlValues)
　　If Not c Is Nothing Then
　　　　first = c.Address
　　　　Do
　　　　　With Me.ListBox1
　　　　　　.AddItem c.Offset(0, -1).Text
　　　　　　.List(.ListCount - 1, 1) = c.Text
　　　　　　.List(.ListCount - 1, 2) = c.Offset(0, 1).Text
　　　　　End With
　　　　　　Set c = .FindNext(c)
　　　　Loop While Not c Is Nothing And c.Address <> first
　　End If
End With
End Sub
'----------------------------------------------------------------
Private Sub CommandButton1_Click()
　With Me.ListBox1
　MsgBox .List(.ListIndex, 0) & vbCrLf & _
　　　　 .List(.ListIndex, 1) & vbCrLf & _
　　　　 .List(.ListIndex, 2)
　End With
End Sub
'----------------------------------------------------------------
Private Sub ListBox1_Click()
　CommandButton1.Enabled = ListBox1.ListCount <> -1
End Sub

この回答へのお礼

私の質問文を元に、あっという間にすらすら組めるなんて本当に凄い！びっくりしました。
ほぼ、私のイメージしていたとおりです。

今まで作ったものに追加し、最終的な結果は、セルに表示したかったので、MsgBox のあたりを、
With Me.ListBox1
Range("D10").Value = .List(.ListIndex, 1) & " " & .List(.ListIndex, 2)
End With
と置き換えたらセル表示することができました！
記述していただいたVBAの中にはわからないものもあるので、本を引き引き解読,勉強しています。
本当にありがとうございました。

お礼日時：2004/10/06 13:35

No.3 回答者： imogasi 回答日時： 2004/10/04 23:20

VBとなっているが、エクセルＶＢＡですね。

それなら
AddItemなど使わずに、ListFillRangeを活用しましょう。
１８食品群があるということなので
Ａ列にＡ１：Ａ１８まで
a
b
c
・
・
p
q
r
と食品群名リストを作る。
Ｂ列に
食品群に属する食品をまとめた食品名を固める
それを
c1:c3　　　a群の塊
c4:c8　　　b群の塊
c9:c13 c群の塊
c14:c17 d群の塊
・・・
のように入れる。文字列で入れる。
本当は各群が１００近くあるからa群はC1:C102、b群は
C103:C208とかのようになる。
c1:c3セルにはa群の商品名
c4:c8セルにはb群の商品名
・・・
を入れる。
c1：ｃ15(一部です）は　
a1
a2
a3
b1
b2
b3
b4
b5
c1
c2
c3
c4
c5
d1
d2
を入れる。
Private Sub ComboBox1_Change()
i = ComboBox1.ListIndex
ComboBox2.ListFillRange = Cells(i + 1, "B")
End Sub
でCombobox1で群を選ぶと、Combobox2にその群の商品リストが現れる。
この場合だとコンボ１でaを選ぶと、コンボ２に
a1
a2
a3が現れる。
たった２行でテーブル方式の威力がわかる。
B列のテーブルを商品が増える都度メンテが必要だが。

この回答へのお礼

丁寧な解説ありがとうございます。
No4さんの解説とあわせて取り組み、なんとか形になりそうです。
あとは結果をもとにした計算式を入れればなんとか自分のやりたいことができるかなと思います。
本当にありがとうございました。

お礼日時：2004/10/06 13:24

No.2 回答者： Scotty_99 回答日時： 2004/10/04 23:11

No１さんはたぶんAccessについて回答されていると思いますので、EXCELとは違います。

この回答へのお礼

記入不足で申し訳ありません。お聞きしたいのはエクセルにおけるマクロ及びVBA活用上についてです。


Scotty_99さん　フォローありがとうございました

お礼日時：2004/10/04 23:56

No.1 回答者： tak2003 回答日時： 2004/10/04 22:21

VBAは操作画面の所の調整に使って、本体の重要な部分はSQLで組んだほうがよろしいかと思います。

1800アイテムある食品名と食品コードを一覧表にされているのなら、SQLのSELECTで抽出できます。
後は抽出したデータを画面に表示するだけで完成です。
VBAはクライアントの端末で要求を貰ったり、その要求に対しての回答をフォームにきちんと表示する時に使ったりしますね。
ご参考まで

この回答へのお礼

早速のご回答ありがとうございます。
勉強不足で申し訳ありません。SQLというのは、エクセルの中でVBAと組み合わせられるものなのでしょうか。
検索をしてみましたが、SQL Server というのが必要なのですか？
出来上がったら友人と使いたいので、比較的互換性のあるものがいいのですが・・。
私がやろうとしていることはマクロやVBAでは無謀なのでしょうか・・。
お礼ながら質問みたいですよね・・スミマセン

ありがとうございました

お礼日時：2004/10/04 22:47