[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『セルの値をリストボックスへ』（ゆら）
・１０列以上のリストボックス表示 ・リストボックスに表示する列を指定する をしたいのですが、なかなか希望する結果が得られません。
元々アクセスでのデータベース作成は初心者レベルでしていましたが、 この度、初めてエクセルでデータベースっぽいものを作成しています。
VBAに初めて挑戦している状態で、「わからないことを調べる」事すら大変です。 求める動作を表現する言葉すらよくわからないので、 うまく質問できるか不安ですが、 今後の作業にも何度となく出てくる予定なので、 詳細な解説をつけていただけると助かります。 どうか、よろしくお願いします。
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
ユーザーフォーム上に検索スペースを設けてあります。 このフォームの動作は 検索結果をリスト表示し、リストから選び、 選んだ内容の詳細がテキストボックスに転記され、 編集や情報の追加をしてシートに戻す。 という工程を想定しています。
検索はテキストボックスに入力した文字を検索キーにして フィルターオプションで検索し指定したセル位置（抽出範囲）に結果を抽出。 その抽出結果をリストボックスに表示します。
抽出結果は20列ほどありますが、リストボックスに表示したいのは、 そのうちの15列程度です。 残りはリストを選択したときに、テキストボックスに詳細内容のような形で転記する場合もあります。
検索用のシートを設けていて、このシートは他の検索フォームでも共有しています。 抽出結果の行数は当然ながら変動します。
共有しているので、同じ抽出結果のセル範囲から、検索フォーム別に必要な列だけをリストボックスに表示させたいのです。
抽出した10列以上ある結果をリストボックスに表示するところは、 「セルの値を配列で格納することで表示ができる」 というところまではなんとかたどりつきました。
しかし、調べて作ったものは セル範囲をそっくりそのまま配列に格納するもので表示する列を選べません。
※抽出結果が 検索用シートのK10を基点に表示されているとして
Private Sub UserForm_Initialize()
Dim myData As Variant
myData = ActiveSheet.Range("k10").CurrentRegion.Offset(1)
‘見出し行の1行下からリストに表示させる
With ListBox1
.ColumnCount = 15
.ColumnWidths = "35;35;35;35;35;35;35;35;35;35;35;35;35;35"
.List = myData
End With
End Sub
現在は上記の方法でリストボックスに表示しており、 リストボックスの列の並びは、検索抽出結果の見出しを並べ替えて そのまま表示させています。
それぞれの検索フォームで主として求めている情報が違っているので、 検索フォームごとに抽出場所を変えて見出しを並べ替えて表示させています。
なので、検索結果シートには、検索フォームの数だけ、抽出場所を用意している状態です。 これでは、キリがありません。
抽出範囲に抽出された結果から リストボックス表示列を指定する、 もしくは列の位置を入れ替える、などしてリストボックスに表示する方法 または、
Private Sub UserForm_Initialize()
Dim myData
With Worksheets("Sheet1")
myData = .Range(.Cells(1, 1), .Cells(Rows.Count, 15).End(xlUp)).Value
End With
With ListBox1
.ColumnCount = 15
.ColumnWidths = "35;35;35;35;35;35;35;35;35;35;35;35;35;35"
.List = myData
Dim i As Integer
For i = 2 To 15
.AddItem ""
.List(i - 2, 0) = Cells(i, 1)
.List(i - 2, 1) = Cells(i, 2)
.List(i - 2, 2) = Cells(i, 3)
.List(i - 2, 3) = Cells(i, 4)
.List(i - 2, 4) = Cells(i, 5)
.List(i - 2, 5) = Cells(i, 6)
.List(i - 2, 6) = Cells(i, 7)
.List(i - 2, 7) = Cells(i, 8)
.List(i - 2, 8) = Cells(i, 9)
.List(i - 2, 9) = Cells(i, 10)
.List(i - 2, 10) = Cells(i, 11)
.List(i - 2, 11) = Cells(i, 12)
.List(i - 2, 12) = Cells(i, 13)
.List(i - 2, 13) = Cells(i, 14)
.List(i - 2, 14) = Cells(i, 15)
Next
End With
End Sub
上記のシートのA1からのセル範囲をリストボックスに格納し、表示列を選べるものがあります。 これを抽出結果のあるK11からの値を取り込むものに変えるには どうしたらいいでしょうか。
基本的な配列もよくわかっていない状態で 見出し行を除く抽出結果をリストボックスに表示するために、-2にしましたが、 なぜこう書くと、見出し行の下から表示されるのか、よくわかっていません。
「A1を基点にしていないセル範囲をリストボックスに10列以上表示する」と 「表示する列を指定する」方法を教えていただけませんか。
既出の質問なのかもしれませんが、うまく捜せませんでした。 なにをどうしたら希望の結果が得られるか、助けてください。 よろしくお願いします。
＜ 使用 Excel：Excel2013、使用 OS：Windows7 ＞
＞ 抽出結果は20列ほどありますが、リストボックスに表示したいのは、 ＞ そのうちの15列程度です。
そこ、ちょっと分かりにくいです。
20列中、15列程度の飛び飛び列ですか？ それとも、 20列中、15列程度の連続列ですか？
＞ For i = 2 To 15 ＞ .AddItem "" ＞ .List(i - 2, 0) = Cells(i, 1) ＞ ： ： ＞見出し行を除く抽出結果をリストボックスに表示するために、-2にしましたが、 ＞ なぜこう書くと、見出し行の下から表示されるのか、よくわかっていません。
iは2から始まっているので、Cells(i, 1)は、アクティブシートの2行1列目を意味する。 すなわちA2セルのデータ(見出し行の一つ下）から、List最上段を頭にリストが再セットされる。
（半平太） 2018/03/20(火) 08:42
20列中、飛び飛びで表示させたいです。
フォーム1のリストボックス1には
1、3、5、10～18列目
フォーム2のリストボックス2には
1、2、5、8、11～15、20列目を表示
など、連続しない列を表示させたいです。
2個目のコードだと、A１から始まるセル範囲で
>> .List(i - 2, 1) = Cells(i, 18) として、列を選んで表示できたのですが、 k10始まりの範囲に書き換える箇所がわからずです。
（ゆら） 2018/03/20(火) 12:51
＞フォーム1のリストボックス1には ＞1、3、5、10～18列目 ＞フォーム2のリストボックス2には ＞1、2、5、8、11～15、20列目を表示
ユーザーフォームが複数あり、それぞれ処理すべき列番が固有の飛び方をしている、との理解でいいですね？
以下、「1、2、5、8、11～15、20列目を表示」のケースについて考えてみます。
Private Sub UserForm_Initialize()
Dim Wsh1 As Worksheet
Dim stCell As Range
Dim myList()
Dim i As Integer
Dim colsAry
Dim colNums()
Dim cel As Range
Dim lastRowReal As Long
Dim desgCol, TempCol
Dim RW As Long, CL As Long
Set Wsh1 = Sheets("Sheet1")
Set stCell = Wsh1.Range("K10") 'スタートするセルを決める
colsAry = Array(1, 2, 5, 8, "11:15", 20)
ReDim colNums(0)
For Each desgCol In colsAry
TempCol = Split(desgCol & ":" & desgCol, ":")
For i = TempCol(0) To TempCol(1)
ReDim Preserve colNums(UBound(colNums) + 1)
colNums(UBound(colNums)) = i
Next i
Next
With Wsh1
lastRowReal = .Cells(.Rows.Count, stCell.Column + colNums(UBound(colNums) - 1)).End(xlUp).Row
ReDim myList(1 To lastRowReal - stCell.Row + 1, 1 To UBound(colNums))
End With
For RW = 1 To UBound(myList)
For CL = 1 To UBound(colNums)
myList(RW, CL) = stCell.Cells(RW, colNums(CL))
Next CL
Next RW
With ListBox1
.ColumnCount = UBound(colNums)
.ColumnWidths = Application.Rept("35", UBound(colNums) - 1) & 35
.List = myList
End With
End Sub
（半平太） 2018/03/20(火) 19:43
どこの箇所を変更すれば他のシート、セルの開始位置、列の選択ができるかは
わかったのですが、コード全体の意味がよくわかりません。
大雑把になんとなくこんな作業をしているのだろう、というのはわかるのですが、
それぞれの行の意味しているところがわかりません。
解説していただくわけにはいかないでしょうか？
‘飛び飛びの列を格納するための作業？
> ReDim colNums(0)
> For Each desgCol In colsAry
> TempCol = Split(desgCol & ":" & desgCol, ":") ‘← "11:15"のところをばらして格納してる？
> For i = TempCol(0) To TempCol(1)
> ReDim Preserve colNums(UBound(colNums) + 1) > colNums(UBound(colNums)) = i > Next i > Next
‘ワークシートのセル範囲？
> With Wsh1
> lastRowReal = .Cells(.Rows.Count, stCell.Column +
colNums(UBound(colNums) - 1)).End(xlUp).Row
> ReDim myList(1 To lastRowReal - stCell.Row + 1, 1 To
UBound(colNums))
> End With
> For RW = 1 To UBound(myList) ‘ > For CL = 1 To UBound(colNums) ‘ > myList(RW, CL) = stCell.Cells(RW, colNums(CL)) > Next CL > Next RW
‘リストボックスの設定
> With ListBox1
> .ColumnCount = UBound(colNums) ‘列数
> .ColumnWidths = Application.Rept("35", UBound(colNums) - 1) & 35 ‘列幅35を表示列の数だけ繰り返す。
> .List = myList
> End With
> End Sub
（ゆら） 2018/03/21(水) 11:18
＞‘飛び飛びの列を格納するための作業？ ＞ > ReDim colNums(0) ＞ > For Each desgCol In colsAry ＞ > TempCol = Split(desgCol & ":" & desgCol, ":") ‘← "11:15"のところをばらして格納してる？ ＞ > For i = TempCol(0) To TempCol(1) ＞ > ReDim Preserve colNums(UBound(colNums) + 1) ＞ > colNums(UBound(colNums)) = i ＞ > Next i ＞ > Next
その通りです。
初めから = Array(1, 2, 5, 8,11,12,13,14, 20) と書いてくれる方式なら そんなループ処理は必要ないんですけどね。
・・で、"11:15" とまとめて書く方式を認めると、今度は単発の8とかの処理に困るので、 強制的に"8:8"とか、"11:15:11:15" とか言う形を作って、 頭の2つだけ取出せば、ループで全部処理できる。
‘ワークシートのセル範囲？
> With Wsh1
> lastRowReal = .Cells(.Rows.Count, stCell.Column +
colNums(UBound(colNums) - 1)).End(xlUp).Row
> ReDim myList(1 To lastRowReal - stCell.Row + 1, 1 To
UBound(colNums))
> End With
そちらのコードを見ると、最終列(20番目)の最後尾のデータセルを最終行番号としていたので、 それに合わせてあります。 すなわち、その列はK10セルからみると30番目の列なので、その列で最終行番号を求めるようにしてあります。 K列で判定してよければもっと簡単なんですけど、いいかどうか分からないので、そんな形にしてあります。
> For RW = 1 To UBound(myList) ‘ > For CL = 1 To UBound(colNums) ‘ > myList(RW, CL) = stCell.Cells(RW, colNums(CL)) > Next CL > Next RW
myListは飛び飛び状態のものを左に詰めて格納する必要があります。
例えば、myListの3列目(CL)は、シートの5列目なので、3なら「5」と言う数値が分かる必要があります。 ・・で、colNumsの3番目に入っているのが「5」なので、そこから取り出せば目的を達する事が出来ます。
＞‘リストボックスの設定
＞
＞ > With ListBox1
＞ > .ColumnCount = UBound(colNums) ‘列数
＞ > .ColumnWidths = Application.Rept("35", UBound(colNums) - 1) & 35 ‘列幅35を表示列の数だけ繰り返す。
＞ > .List = myList
＞ > End With
＞ > End Sub
ごめんなさい。間違いです。
＞ .ColumnWidths = Application.Rept("35", UBound(colNums) - 1) & 35
↓
.ColumnWidths = Application.Rept("35;", UBound(colNums) - 1) & 35
35を「;」で繋げて行くので、「35;」を一つ少なく連続結合して、最後に35をくっつける、です。 m(__)m
（半平太） 2018/03/21(水) 12:38
解説ありがとうございます。
当方で載せたコードは意味を理解しきれず、
書籍やサイトなどから、それらしいものを継ぎはぎで組み立てたものなので、
「なんだかよくわからないけど動いてる」状態でした。
やりたかったことは
◎検索をかけ抽出された結果をリストボックスへ表示する
・検索結果用のシートを沢山作りたくないので、1シートを利用したい。
・検索結果は「K10」始まりの範囲に置いてある
※今後の作業でK10以外になる可能性がある。
・検索結果は10項目以上になるので、リストボックスに10列以上表示させたい。
・ユーザーフォームによって表示させたい内容、列数が違うので、列の増減と表示を自由に並べ変えたい。
なので、作っていただいたコードで全部の条件をクリアすることができました。
既存のフォーム全部に差し込んでみましたが、うまく動いてくれています。
列の格納で11：15とするところも、コードが簡単になるのであれば、
11,12,13と個別に入力しても問題ありません。
いざ、書き換えるときに、個別に入力したほうが迷わないのかもと思ったりしています。
その場合、どのあたりを書き換えればよいのでしょうか？
セル範囲も今後の制作過程で変わることが予想されるので
スタート地点を決めてやるだけで、セル範囲の最終行列を求めてくれる方法はとてもありがたいです。
スタートセルと、最終列は決まったところに固定することは可能ですが
作っていただいたコードだと、スタートセルを変更しても、（K10をP11などに変更）
最終行、列に関しては書き換え不要ということですよね。
列幅のところは、全部を同じ幅にしない場合は、地道に幅を打ち込めばよい ということですよね。
“35；40；120・・・”
わかりやすい解説ありがとうございます。
自分的巨大な壁だったので、作業がストップしておりまして、本当に助かりました。
（ゆら） 2018/03/21(水) 15:13
＞列の格納で11：15とするところも、コードが簡単になるのであれば、 ＞11,12,13と個別に入力しても問題ありません。 ＞いざ、書き換えるときに、個別に入力したほうが迷わないのかもと思ったりしています。 ＞その場合、どのあたりを書き換えればよいのでしょうか？
その場合はこうしてください。
↓
colNums = Array(1, 2, 5, 8, 11, 12, 13, 14, 15, 20) '1列ずつ指定する
注意: colNumsの配列は最初が0スタートになるので、その調整(+1）が必要になります。 例えば、全9列なら(0 TO 8) の次元になります。
＞スタートセルと、最終列は決まったところに固定することは可能ですが
いや、私が言ったは、最終行がどこまであるのか判定する時に、 スタートセルのある列の最後尾のデータが、範囲の最終行と判定していいか、と言うことです。 下記コードはそれでいいとした場合のものです。
＞作っていただいたコードだと、スタートセルを変更しても、（K10をP11などに変更） ＞最終行、列に関しては書き換え不要ということですよね。
それが目的なので、そのハズです。 ＞列幅のところは、全部を同じ幅にしない場合は、地道に幅を打ち込めばよい ということですよね。 ＞“35；40；120・・・”
規則性が無ければ、一つずつ書くしかないです。
Private Sub UserForm_Initialize()
Dim Wsh1 As Worksheet
Dim stCell As Range
Dim myList()
Dim colNums()
Dim lastRowReal As Long
Dim RW As Long, CL As Long
Set Wsh1 = Sheets("Sheet1")
Set stCell = Wsh1.Range("K10") 'スタートするセルを決める
colNums = Array(1, 2, 5, 8, 11, 12, 13, 14, 15, 20) '1列ずつ指定する
With Wsh1
lastRowReal = .Cells(.Rows.Count, stCell.Column).End(xlUp).Row
ReDim myList(1 To lastRowReal - stCell.Row + 1, 1 To UBound(colNums) + 1)
End With
For RW = 1 To UBound(myList)
For CL = 1 To UBound(myList, 2)
myList(RW, CL) = stCell.Cells(RW, colNums(CL - 1))
Next CL
Next RW
With ListBox1
.ColumnCount = UBound(myList, 2)
.ColumnWidths = Application.Rept("35;", UBound(myList, 2) - 1) & 35
.List = myList
End With
End Sub
（半平太） 2018/03/21(水) 16:47
＞いや、私が言ったは、最終行がどこまであるのか判定する時に、
＞スタートセルのある列の最後尾のデータが、範囲の最終行と判定していいか、と言うことです。
＞ 下記コードはそれでいいとした場合のものです。
勘違いしました。
Kスタートなら、Kの最後の行を、範囲の最終行にしてもいいのか、ということですね。
それでよいです。
スタートセルの列は空行のない列なので、最終行を判定する列にします。
書き換えていただいた、コードも問題なく動きました。
「スタートセルを変えて、リストボックス１０列以上表示、さらに列を飛び飛びにする」が
こんなに、手の込んだ作業になるとは思いませんでした。
細かな解説まで、書いていただいて
実際に自分の環境に合わせた数字などを入れていただいたおかげで、
どこでなにをさせているのかわかりました。
はじめから自分で書くことは当分難しそうですが、
参考にさせていただいて、スキルアップを図りたいと思います。
ありがとうございました。
（ゆら） 2018/03/21(水) 19:42
先日、上記で教えていただいた、
「リストボックスに10列以上表示し、飛び飛びの列を指定する方法」を
数個あるフォームに入れています。
リストボックスに表示するだけなら、上手く動いているのですが、
このコードで作ったリストボックスで選択した行を
「同じフォーム内にある、テキストボックスに転記」
「別のシートにあるセルに転記」させると
10個目でListプロパティエラーがでます。
教えていただく前に入れていた
Private Sub UserForm_Initialize()
Dim myData As Variant
myData = ActiveSheet.Range("k10").CurrentRegion.Offset(1)
With ListBox1
.ColumnCount = 15
.ColumnWidths = "35;35;35;35;35;35;35;35;35;35;35;35;35;35"
.List = myData
End With
End Sub
に戻すと、エラーは出ず、転記されます。
この違いはなんでしょうか？
リストボックス10列以上の表示は
セルの値を配列格納することで可能になり
10列以上表示のリストボックスから値を取得し、
テキスボックスやシートへの転記もできると思っていたのですが、
つまづいてしまいました。
ユーザーフォーム１に
ListBox1、ListBox2 を置き
ListBox1 は、検索結果を表示させるリストで、検索用のシート「検索用」に抽出された検索結果を15列で表示させます。
ListBox２ は 印刷用のデータが置いてあるシート「印刷データ」の値を18列で表示しています。
この二つのListBoxに教えていただいたコードを入れました。
ListBox１で選択した行の値を、「印刷データ」シートに追加し3列の差分情報を追記して
ListBox２に追加表示する。
ListBox２で選択した行の値を印刷フォーマットのあるシート「印刷様式」の対応セルに転記し印刷する。
印刷様式は3種類あり、データの内容次第で印刷時に振り分けられます。
「印刷データ」は、新規登録のフォームと更新登録のフォームから登録の都度、
必要な部分のデータが転記され、印刷様式に足りない情報を追記して蓄積し、
「印刷様式」シートにはめ込んで印刷します。
ListBox2で選択して印刷させるのは、選択してテキストボックスに詳細内容を表示し、内容の確認をする。
内容に間違えがなければ、様式に合わせた台紙をプリンタに差し込んで印刷する作業のためです。
ListBox１で検索し追加するデータは、新規や更新以外で再印刷などが必要なものを「印刷データ」に追加する。
また、「印刷データ」シートが空の時、つまり新規や更新の作業がなく再印刷だけの場合、
印刷対象を検索して追加するためです。
3項目の追記や、印刷フォーマットへのはめ込み方法など、
現段階でそこまで考えが及んでいませんが、
その前段階のリストボックスからシートへの転記で躓いています。
解決策を教えていただけないでしょうか。
（ゆら） 2018/03/24(土) 17:50
＞この違いはなんでしょうか？
前のはロクに読み込んでいないので、よく分かりません。m(__)m
私としては、現在時点で、何故10個目でListプロパティエラーが出るのかを考えたいです。
・・と言っても、トラブル再現用のデータも無いし、 その時使った転記コードがどんなものなのかも分からないので、 こちらとしてはお手上げです。
（半平太） 2018/03/24(土) 20:55
このことから、なにかコードを修正したのだとおもうんですが、修正したコードの提示がないと検証のしようがないのでわかりません。
質問されるときは、どのようなコードなのか。また、どのようなエラーなのかを伝えるようにしたほうがよいとおもいます。
（もこな２） 2018/03/24(土) 21:09
コメントありがとうございます。
一から作りなおしてみたら、ちゃんと動きました。
どこかを打ち間違っていたようです。
どこかはわからないのですが、
ない列を設定したか、ないテキストボックスに設定したか
名前の付け方の間違いとか、そういった感じのボンミスでした。
早とちりして書き込んでしまいました。
申し訳ありません。
打ち間違いで数日悩んだかと思うと、情けないです。
制作を急かされていたため、あわてましたが、
もっと、落ち着いて作業します。
お騒がせしました。
（ゆら） 2018/03/24(土) 21:21
コメントありがとうございました。
どうやら、自分のボンミスだったようです。
やり直してみたら、希望の動作をしてくれました。
大変お騒がせいたしました。
（ゆら） 2018/03/24(土) 21:24
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.