Excel VBA ユーザーフォームのリストボックスにRecordSetの値を入れた際のラベル行

質問

以下のようなExcelシートがあります。
1行目はラベル行です。

[製品管理表]

1| 管理番号| 製品名 | 在庫数 |
2| A001 | 製品Ａ | 2000 |
3| A002 | Ｂ製品 | 1700 |
4| A002 | Ｂ製品 | 1700 |

......以下1000行くらい続く

この中から製品名で検索をかけて、検索結果をユーザーフォームの
リストボックスに一覧表示させたいのですが、

現在の方法は、
検索条件を元にSQL文を生成、
ADOでシートをRecordSetに格納し、
RecordSetを2次元配列に格納、
2次元配列をリストボックスの要素にする。

ソースは以下の通りです。

==========================================================
Sub Test()

Dim CN As New ADODB.Connection
Dim RS As New ADODB.Recordset
Dim strSQL As String
Dim strName as String
Dim strElements() As String
Dim intRow As Integer
Dim i, ii As Integer

strName = Form1.TextBox1.Value

With CN
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Properties("Extended Properties") = "Excel 8.0"
.Open ThisWorkbook.FullName
End With

If strName <> "" Then
strSQL = "select * from [製品管理表$] " _
& where 製品名 Like '%" & strName & "%'"
Else
strSQL = "select * from [製品管理表$]"
End If

RS.Open strSQL, CN, adOpenStatic, adLockReadOnly

intRow = RS.RecordCount

If intRow = 0 Then
MsgBox "条件に一致する製品名はありません"
Exit Sub
End If

ReDim strElements(intRow, 3)

For i = 0 To intRow - 1
For ii = 0 To 2
strElements(i, ii) = RS(ii).Value
Next
RS.MoveNext
Next

With Form1.ListBox1
.ColumnCount = 3
.BoundColumn = 1
.List = strElements
.ColumnHeads = True
End With

End Sub

==========================================================

この方法でも動くのですが、
問題点はラベル行が空白になってしまいます。

同じくリストボックスの要素を入れる方法として、
RowSource を使用すると ColumnHeads = True のときに
1行目がラベル行になるので、空白は想定外でした。
（1行目がラベルになるなら、配列にラベル名をぶち込んで
しまえばいいと考えていたもので…）

そこで質問なのですが、
(1)大枠はこのままで1行目をラベル行にする方法はあるのでしょうか？
(2)ラベル行を明示的に指定する、
またはラベル名を手打ちするコードはあるのでしょうか？

あと、
RecordSetから直接リストボックスの要素を入れる方法も
あったら知りたいと思います。

皆さんのお知恵を是非貸してください！！
よろしくお願いいたします。

回答No.1

end-u · ベストアンサー

(1)(2)ともに無いようですね。
どうも
http://support.microsoft.com/kb/164343/ja
この頃からの仕様のようです。
Sheet上にデータを置いてRowSourceを使うか、
Labelコントロールなどで代用するしかないのかもしれません。

>RecordSetから直接リストボックスの要素を入れる方法...
With Form1.ListBox1
　　.ColumnCount = 3
　　.Column = RS.GetRows()
End With
みたいな事でしょうか。
http://msdn.microsoft.com/ja-jp/library/cc364163.aspx

以下、余談ですが、
>.Open ThisWorkbook.FullName
本番コードもこのように、自Bookに対してADOでアクセスする場合、メモリリークが発生するようですから、
その点を把握された上で運用なさったほうが良いかと。
http://support.microsoft.com/kb/319998/ja

お礼

>>RecordSetから直接リストボックスの要素を入れる方法...
>With Form1.ListBox1
>　　.ColumnCount = 3
>　　.Column = RS.GetRows()
>End With
>みたいな事でしょうか。

まさに想像通りです！！ありがとうございます。

ただ（１）も（２）もだめなら
end-uさんの仰るとおりシートにコピーしてからRowSourceにするか
ColumnHeadsをやめてフォーム上にラベルを貼り付ける力技になりそうです。

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