VBA 複数のセルの値をテキストボックスに表示する

質問

いつもお世話になっております。
初歩的質問かも知れませんが、宜しくお願いします。

EXCEL2007使用。

Sheet２に下記のような値があります。

　　　A　　　　B　　　　C　　　　D　　　　E
1　あか　　きいろ　みどり　みどり　　しろ
2　しろ　　　　　　　　しろ　　　しろ　 　くろ
3　しろ　　　　　　　　あか　　 しろ　　 あお
4　きいろ　　　　　　 あお　 　しろ　　 あお
5　くろ　　　　　　　　　　　　　　　　 　　あか
6　くろ　　　　　　　　　　　　　 　　　　　あお
7　　　　　　　　　　　　　　　　　　　　　　しろ

A1：E7までの範囲の値を、
Sheet1にあるテキストボックス（またはコンボボックス）に縦に表示したいのです。
A～Eの複数の列の値には空白も含まれていて、
縦一列に値を表示するにも、空白を省く処理ですでに躓いております。

何か良い方法がありましたら、
ぜひご教授お願いいたします。

回答No.3

mt2008 · ベストアンサー

テキストボックスって、シートに貼り付けたテキストボックスでいいですか？

Sub sample()
　　Dim vData As Variant
　　Dim oTextbox As OLEObject
　　Dim nFlag, i, j

　　'テキストボックスを探す（今回は最初に見つけたテキストボックスが対象）
　　nFlag = 0
　　For Each oTextbox In Worksheets("Sheet1").OLEObjects
　　　　If TypeName(oTextbox.Object) = "TextBox" Then
　　　　　　nFlag = 1
　　　　　　Exit For
　　　　End If
　　Next
　　If nFlag = 0 Then
　　　　MsgBox ("テキストボックスがありません")
　　　　Exit Sub
　　End If

　　oTextbox.Object.Value = "" 'テキストボックスのクリア
　　oTextbox.Object.MultiLine = True '念のため複数行をTrueに

　　'テキストボックスに値を入れる
　　vData = Worksheets("Sheet2").Range("A1:E7")
　　For i = 1 To 5
　　　　For j = 1 To 7
　　　　　　If vData(j, i) <> "" Then
　　　　　　　　oTextbox.Object.Value = oTextbox.Object.Value & vData(j, i) & vbCrLf
　　　　　　End If
　　　　Next j
　　Next i
End Sub

お礼

ありがとうございます。
思った通りになりました。
空白を詰める方法がどうしてもわからず、
四苦八苦していましたが・・・
こんな方法があったとは。
本当に勉強になります。
ありがとうございました。

回答No.2

keithin

ListBox（またはコンボボックス）を配置し，プロパティで
ColumnCountを５
ColumnWidthを例えば45;45;45;45;45
ListFillRangeにSheet2!A1:E7
と設定してみます。

お礼

今回はmt2008様の方法で対処いたしましたが、
keithin様の方法も大変勉強になりました。
またわからないときは質問させていただきますので、
その時は宜しくお願いいたします。
ありがとうございました。

補足

ありがとうございます。
素人の私にも直下的に理解できるものでした。
しかし、私の求めている結果とは少し違います。
値を全て縦に表示したいのです。
A列の値の下にB列の値といった感じです。
A１：E7の範囲内の値を、空白のない状態で詰めて
縦表示することはできませんか？

説明不足で申しわけありません。
宜しくお願いいたします。

回答No.1

mu2011

一例です。
A1→B1→C1～A2→B2～E7順です。

For Each a In Range("A1:E7")
If a.Value <> "" Then
テキストボックス転送
Endif
Next

お礼

ありがとうございました。
変数の型で躓くほどの素人ですが、
良い参考となりました。
自分なりに改良して頑張りたいと思います。