コンボボックス or リストボックス （複数列表示→値の取得）

質問

マクロ初心者です。（エクセル2003使用-ユーザーフォーム）
先日はお世話になり、ありがとうございました。
作成していくうちにさらに改良を加えたく、再質問させていただきます。

※コンボボックス内の表示を複数行表示（Sheet1の管理番号,品名,注文数量）し、そのデータをSheet2のセルＡ（管理番号）,セルＢ（品名）,セルＣ（注文数量）と貼り付けようとしております。
が、本で探したところ複数行表示のやり方がリストボックスでしかのっていなく、さらに自分で作成したマクロでは動きませんでした。
すみませんが、お力をお貸しください。

（Sheet1)
担当課 客先 管理番号 品名 注文数量 出荷数量
A 岡田さん 1324 りんご 30 20
B 山田さん 1554 みかん 250 70
C 岡田さん 7634 なし 40 25
B 金子さん 4653 みかん 75 70
A 金子さん 6675 りんご 170 60
C 杉浦さん 7789 りんご 200 120

（↓こちらは、前回質問させていただいた内容です。）
Private Sub UserForm_Initialize()
ComboBox1.RowSource = "Sheet1! C2:C" & Worksheets("Sheet1").Range("C" & Rows.Count).End(xlUp).Row　←ここを複数行用に変更するのでしょうか？いろいろ試したのですがダメでした。
ComboBox1.ListIndex = -1
ComboBox1.SetFocus
End Sub

Private Sub CommandButton1_Click()
Dim lRow As Long
With Worksheets("Sheet2")
lRow = .Range("A" & Rows.Count).End(xlUp).Row
.Range("A" & lRow + 1).Value = ComboBox1.Value
End With
End Sub

（↓こちらは、リストボックスでのマクロですが、動きません）
Private Sub UserForm_Initialize()
With UserForm2.ListBox1
.ColumnWidths = "70;50;50"
.ColumnCount = 3
End With

With Worksheets("Sheet1")
Dim MyA As Variant
Dim i As Long
For i = 2 To UBound(MyA, 1)
.AddItem
.List(i - 2, 0) = Cells(i, 1).Value
.List(i - 2, 1) = Cells(i, 2).Value
.List(i - 2, 2) = Cells(i, 3).Value
Next
End With
End Sub

Private Sub CommandButton1_Click()
Dim lRow As Long
With Worksheets("Sheet2")
lRow = .Range("A" & Rows.Count).End(xlUp).Row
.Range("A" & lRow + 1).Value = ListBox1.Value
End With
End Sub

教えていただけましたら幸いです。
よろしくお願いいたします。

回答No.1

うぃず（@Wizard_Zero） · ベストアンサー

Private Sub UserForm_Initialize() からF8を押しながらステップ実行させていけば、どこでエラーになるか分かります。デバッグの基本なので覚えておくと良いでしょう。

ちなみに、ご提示されたコードでは
For i = 2 To UBound(MyA, 1)
でとまります。理由は、MyAが何も代入されないままUBoundに使われているためです。そもそもこのMyAがなぜ出てきたのか不思議ですが…。

また、ここを訂正してもそのあとの
.AddItem
でこけます。WithでくくられているのはWorksheets("Sheet1")ですが、ワークシートにAddItemメソッドはありません。当然ならがそのあとの.Listもすべて×。逆に、Cellsは「.」が抜けているのでSheet1ではなく表示しているシートを参照することになります。

.Range("A" & lRow + 1).Value = ListBox1.Value
についても、複数行のデータはValueでは取得できません。セットするときに List(#, #)=～ としたようにListプロパティでひとつずつ取得します。

> ←ここを複数行用に変更するのでしょうか？いろいろ試したのですがダメでした。

後半のコードと見比べてみましたか？
With UserForm2.ListBox1
.ColumnWidths = "70;50;50"
.ColumnCount = 3
End With
このようにリストボックスの各カラムの幅とカラム数を指定してます。前半のコードで複数行表示ができないのは、これをしていないためです。

以上を踏まえて修正したものです。

Private Sub UserForm_Initialize()
With ListBox1
.ColumnWidths = "70;50;50"
.ColumnCount = 3
.RowSource = "Sheet1!A2:C" & Worksheets("Sheet1").Range("C" & Rows.Count).End(xlUp).Row
End With
End Sub

Private Sub CommandButton1_Click()
Dim lRow As Long
With Worksheets("Sheet2")
lRow = .Range("A" & Rows.Count).End(xlUp).Row
.Range("A" & lRow + 1).Value = ListBox1.List(ListBox1.ListIndex, 0)
.Range("B" & lRow + 1).Value = ListBox1.List(ListBox1.ListIndex, 1)
.Range("C" & lRow + 1).Value = ListBox1.List(ListBox1.ListIndex, 2)
End With
End Sub

コンボボックスの場合は、フォームにComboBox1を設置し、コードのListBox1を全てComboBox1に置き換えてください。ただし、ドロップダウンリストは複数行で表示されますが、コンボボックス自体は複数行表示できないようなので担当課の値しか出てきません。

補足

丁寧なご回答、本当にありがとうございます。
■F8を押しながらステップ実行も知らなかったので、これからは違うと出る理由は何かを考えながら作業しようと思いました。
■ComboBoxでも作動することができました。
（ドロップダウンリストはインストールしてないためできませんでした）

コードを見ていて思ったのですが、
ComboBoxで表示した値を取得することは可能なのでしょうか？

たとえば、ComboBoxのリストで、管理番号７６３４を表示していた場合、その値を取得（Ｃ４であることを認識すること）し、Sheet2のセルＡに７６３４（Ｃ４）を表示、セルＢに岡田さん（Ｂ４）を表示するという方法です。

（Sheet1)
担当課 客先 管理番号 品名 注文数量 出荷数量
A 岡田さん 1324 りんご 30 20
B 山田さん 1554 みかん 250 70
C 岡田さん 7634 なし 40 25
B 金子さん 4653 みかん 75 70
A 金子さん 6675 りんご 170 60
C 杉浦さん 7789 りんご 200 120

すみませんが、今一度お付き合い願いますよう、よろしくお願いいたします。
※もしこの質問は別でした方がよろしければ、そうさせていただきます。

回答No.2

redfox63

シートにあるデータが質問に書かれているデータだけなら

Private Sub UserForm_Initialize()
　　dim r as Range
　　' シートからデータ範囲を取得
　　set r = Worksheets("Sheet1").Range("A1").CurrentRegion.Offset(1)
　　set r = r.Resize( r.rows.count -1 )
　　With UserForm2.ListBox1
　　　　.ColumnWidths = "70;50;50"
　　　　.ColumnCount = 3
　　　　.RowSource = r.Address(0,0)
　　End With
End Sub

Private Sub CommandButton1_Click()
　　Dim r as Range, n as Integer
　　With Worksheets("Sheet2")
　　　　' 転記先のセルの取得
　　　　set r = .Range("A" & Rows.Count).End(xlUp)
　　　　for n = 1 to 3
　　　　　　' データの転記
　　　　　　r.value = ListBox1.List( listBox1.ListIndex, n )
　　　　　　' 右隣のセルを指定
　　　　　　Set r = r.Offset( , 1 )
　　　　next
　　End With
End Sub
といった具合でどうでしょう

お礼

皆様、ご丁寧な回答をありがとうございます。
書いていただいたコードが分かり、自分で使えるようになるには、まだ時間がかかりそうです。
が、ひとつひとつ理解していきたいと思います。
ありがとうございます。