コンボボックスのリストについて（エクセル、マクロ）

質問

またお世話になります。
多分簡単な疑問だと思うのですが・・・、
ユーザーフォームのコンボボックスのRowsourceの
設定に関して可変式にしたいので以下のように
マクロ（一部を抜粋）を組んでみました。
Dim b As Integer
b = 2
Do
b = b + 1
Loop Until Sheets(3).Cells(b, "A") = ""
ComboBox1.RowSource = Range(Cells(2, "A"), Cells(b - 1, "C"))

が、「型が一致しません」と出てうまくいきません。
どうしたらよいでしょうか？

また、
Ａ列　　　Ｂ列　Ｃ列
2005/1/1　東京　10
・
・
・
のようにシートには並んでいるものを
コンボボックスのリストとして取り入れたいのですが
変な５桁の数字しか現れず（たぶんこの数字は
2005/1/1を標準値化した数値なのでしょうが）
３つきれいにそろって表れません。
こちらの方も解決していただけないでしょうか？

回答No.5

KenKen_SP · ベストアンサー

＞選択したものが（2005/1/1 東京 10を選択したなら2005/1/1 東京 10）と並べて表示...

3つのセルの表示文字列を連結して AddItem します。

Private Sub UserForm_Activate()

　　Dim lngLastRow As Long, i As Long
　　Dim strBuf As String

　　'ComboBox初期化
　　With Me.ComboBox1
　　　　.Clear 'クリア
　　　　.ColumnCount = 1 '1列
　　End With

　　'ComboBoxアイテムセット
　　With Sheets("Sheet1") 'データソースがSheet1の場合
　　　　lngLastRow = .Range("A1").End(xlDown).Row
　　　　For i = 2 To lngLastRow
　　　　　　strBuf = _
　　　　　　　　.Cells(i, "A").Text & vbTab & _
　　　　　　　　.Cells(i, "B").Text & vbTab & _
　　　　　　　　.Cells(i, "C").Text
　　　　　　Me.ComboBox1.AddItem strBuf
　　　　Next i
　　End With

End Sub

＞ちなみに教えていただいたコードを実行しますと2005/1/1 東京 10を選択した場合「東京」と表示されます。

コメントに書いておきましたが、BoundColumn プロパティーの設定によるものです。

＞　.BoundColumn = 1 '値として格納されるデータの列番号

2005/1/1 なら　.BoundColumn = 0 となります。

ComboBox コントロールはリスト選択後、BoundColumn で指定した列が表示されます。

お礼

おかげさまでうまくいきました。
実は最初はうまくいかなかったのですがプロパティウィンドウで変に設定をいじっていたのを戻したらうまくいきました。（日付の標準値である５桁の数字が表れた原因だったのかもしれません。）

回答No.6

imogasi

＃２です。補足に対して言い訳ですみませんが・・・
＞プロパティシートを出して、Textcolumnの数値をいじってみましたが、１の場合には日付の整数値、２や３の場合にはちゃんと「東京」や「10」のように表示されるのですが、単独でしか表示されず
－－－－－－
それはそうですね、ＢｏｕｎｄＣｏｌｕｍｎは１列しか指定させてくれないようですね。
３列指定したければ、擬似的に前もって３列結合したデータを別列に持つか、プログラムでクリックイベント後にコンボのテキストに３列結合したデータを自分でセットせざるを得ないでしょう。
＞（範囲指定を）可変にしたい
この点には私のは答えていません。範囲の表現方法
が（他の例を含めると）●Ｒａｎｇｅ（）式というのと●Ａ１：Ｃ３と指定するのとあり、
画面で指定する場合は当然前者の方式ですということを言いたかったまでです。
ＶＢＡの場合は本例では、前者（文字列）のようで２番目のセルの行数を可変（変数で指定）指定にすることになります。
ComboBox1.RowSource = Range(Cells(2・・・と
「Ｒａｎｇｅ」をつけておられたのが
災いしたのでしょう。
・・ということを言いたかったのですが。
全般的には＃１、＃４が詳述されていますので
省略します。

お礼

了解しました。私もなかなかうまくいかずに焦っていた面もあったと思います。わざわざご丁寧に補足していただいて申し訳ないです。

回答No.4

KenKen_SP

【前半部の補足】

RowSource プロパティーにはセル範囲を表す”文字列”を設定しなければなりません。つまり、

ComboBox1.RowSource = "Sheet1!A1:C100"

または、

With Sheets("Sheet1")
　　sSource = .Name & "!" & .Range("A1:C100").Address
End With
ComboBox1.RowSource = sSource

のようしてセル範囲を文字列で指定します。また、ちゃんとシートを指定しないとアクティブシートの A1:C100 が RowSource にセットされますので、注意が必要です。

余談ですが、最終行番号を受ける変数の型はLong型にした方が良いですね。

Dim b As Integer　--＞　Dim b As Long

【後半部】

サンプルコードを示します。RowSource ではなく、List プロパティを使っています。試してみたのですが、RowSource では日付がシリアル値で表示されてしまいました。AddItem で 日付を文字列をして登録していくしかないかなぁと思いましたが、List プロパティでできました。理由は聞かないで下さい（...不明です）

'-------- Sample --------------------------------------------------
Private Sub UserForm_Initialize()

　　Dim lngLastRow As Long
　　Dim Buf As Variant

　　'ComboBox初期化
　　With ComboBox1
　　　　.Clear 'クリア
　　　　.ColumnCount = 3 '3列
　　　　.BoundColumn = 1 '値として格納されるデータの列番号
　　　　.ColumnWidths = "80pt;40pt;40pt" '各列の表示幅
　　End With

　　'ComboBoxアイテムセット
　　With Sheets("Sheet1") 'データソースがSheet1の場合
　　　　'最終行番号取得
　　　　lngLastRow = .Range("A1").End(xlDown).Row
　　　　'データ範囲を配列へ
　　　　Buf = .Range("A2:C" & lngLastRow)
　　　　'ComboBoxコントロールへセット
　　　　ComboBox1.List = Buf
　　End With

End Sub
'------------------------------------------------------------------

最後に、、えーと、ちょっとわからないのですが、、、
ひょっとして、ComboBoxのリストを選択した後に、

2005/1/1 東京 10

と並べて表示したいのですか？

補足

＞余談ですが、最終行番号を受ける変数の型はLong型にした方が良いですね。
これは意外に重要な指摘ですね。ありがとうございます。
＞ひょっとして、ComboBoxのリストを選択した後に、
2005/1/1 東京 10と並べて表示したいのですか？
ユーザーフォーム内でコンボボックスのリストから特定のものを選択した際、コンボボックスのテキストボックス部分みたいなところに選択したものが（2005/1/1 東京 10を選択したなら2005/1/1 東京 10）と並べて表示できるようにしたいのです。ひょっとしてこれは無理なことなのでしょうか？ちなみに教えていただいたコードを実行しますと2005/1/1 東京 10を選択した場合「東京」と表示されます。

回答No.3

Samurai-Jack

No.1です。

私のPCではちゃんと日付の形式で表示されるのですが。。。（Excel2000、Win2000です）

セルの属性はどうなっているのでしょうか？無理やりですが、セルの属性をテキストにして、日付をテキストとして入力すると表示できるかもしれません。

回答No.2

imogasi

＞変な５桁の数字しか現れず（たぶんこの数字は
2005/1/1を標準値化した数値なのでしょうが
エクセルでは日付は日付シリアル値といって１９００年初からの日数を整数値で持っているのは有名な話です。昨日も似た質問さありました。
「標準値化した数」でなく、本来は整数のこちらが本筋で、見た目の日付は書式で帰られた飾られた姿です。
こうすることで日数計算が単純になります。
後半の質問は
ＵｓｅｒＦｏｒｍ１をデザインモードにして、コンボボックスのプロパティシートを出すと、Textcolumnがあるから、これを１，２，３に順次変えてみて、ボックスに値の現れ方（どの列が現れるか）納得してください。
前半の質問も、ＲｏｗＳｏｕｒｃｅはＡ１：Ｃ４のようにプロパティウインドウで指定することを考えてください。
ＶＢ，アクセスＶＢＡ、エクセルＶＢＡで微妙に違うようで混乱します。

補足

うーん、よく分からないのですがプロパティウインドウで指定すると「Ａ１」などのようにセルを明示しないといけないのではないですか？元にするリストが変わったときにも対応させるようにしたいのです。
プロパティシートを出して、Textcolumnの数値をいじってみましたが、１の場合には日付の整数値、２や３の場合にはちゃんと「東京」や「10」のように表示されるのですが、単独でしか表示されず、やはり「2005/1/1 東京 10」のように並んで表示されません。なんとかならないものですかね。

回答No.1

Samurai-Jack

こんな風にしてみてはどうでしょうか？

Dim b As Integer
b = 2

Do
b = b + 1
Loop Until Sheets(3).Cells(b, "A") = ""

ComboBox1.RowSource = "A1:C" & b

End Sub

お礼

なるほど！
言われてみたら単純にその通りですね。
前半部はこれでバッチリです！
あとはどなたか後半部について答えていただけると幸いなのですが・・・。