VBA100本ノック 67本目：ComboBoxとListBox

最終更新日：2021-01-16

VBA100本ノック 67本目：ComboBoxとListBox

ユーザーフォームのコンボボックスでデータを絞り込んでリストボックスに表示する問題です。

ツイッター連動企画です。
ツイートでの見やすさを考慮して、ブック・シート指定等を適宜省略しています。

VBAテスト用のサンプルデータは、VBA100本ノックの目次ページ よりダウンロードできます。

マクロVBA情報を中心に、エクセル関数・基本操作までサンプルとともに解説。初心者向けの基本から上級者向けの高度な内容までVBAサンプルコードを掲載解説しています。

出題

#VBA100本ノック 67本目
ユーザーフォームのListBoxに、ComboBoxの値で「リスト」シートのA列を絞りこんだデータを表示します。
ListBoxにはB列～D列を列見出しを付けて表示してください。
適宜作業シートを使用。
※オブジェクト名は任意
※コンボは都道府県が設定済としてイベントの作成です。

サンプルファイルです。
https://excel-ubara.com/vba100sample/VBA100_67.xlsm
https://excel-ubara.com/vba100sample/VBA100_67.zip

頂いた回答

出題ツイートの引用ツイートでの回答

解説

コンボボックスの変更はChangeイベントを使います。
リストボックスの設定は、
複数列：ColumnCount
列幅：ColumnWidths
見出し：ColumnHeads
データ：RowSource
見出しはRowSourceがセル範囲の場合のみ有効。
RowSourceは見出し行を除いたセル範囲のアドレスを文字列で指定します。


  
    
      Private Sub cmb都道府県_Change()

　　Application.ScreenUpdating = False

　　Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("リスト")

　　With lst個人

　　　　.ColumnCount = 4

　　　　.ColumnWidths = "0;95;30"

　　　　.ColumnHeads = True

　　　　.RowSource = getListAddress(ws, 1, Me.cmb都道府県.Value)

　　End With

　　Application.ScreenUpdating = True

End Sub


Private Function getListAddress(ByVal ws As Worksheet, ByVal aCol As Long, ByVal aStr As String) As String

　　Dim wsWork As Worksheet

　　Set wsWork = getWorksheet(ThisWorkbook, "work")

      　　wsWork.Cells.Clear

      　　

　　ws.AutoFilterMode = False

　　With ws.Range("A1").CurrentRegion

　　　　.AutoFilter aCol, aStr

　　　　.Copy Destination:=wsWork.Range("A1")

　　End With

　　ws.AutoFilterMode = False

　　

　　With wsWork.Range("A1").CurrentRegion

　　　　If .Rows.Count = 1 Then Exit Function

　　　　getListAddress = Intersect(.Cells, .Offset(1)).Address(external:=True)

　　End With

End Function


Private Function getWorksheet(ByVal wb As Workbook, ByVal aName As String) As Worksheet

　　On Error Resume Next

　　Set getWorksheet = wb.Worksheets(aName)

　　If Err.Number = 0 Then Exit Function

　　

　　Dim ws As Worksheet: Set ws = ActiveSheet

　　Set getWorksheet = wb.Worksheets.Add(after:=wb.Sheets(wb.Sheets.Count))

　　getWorksheet.Name = aName

　　getWorksheet.Visible = xlSheetVeryHidden

　　ws.Select

      End Function

表示列だけに範囲を絞っても良いですが、列幅を0にすれば見た目は列が無い状態になります。
この非表示列は、リストから選択されているデータを取得する時に使う事も出来ます。
記事にはVBAコードと参考ページへのリンクを掲載しています。

補足

サイト内関連ページをご覧ください。

サイト内関連ページ

ユーザーフォーム入門

エクセル(Excel)マクロ(VBA)をやっていればユーザーフォームの存在を知り、そしてユーザーフォームを使いたくなります。使いたくなるというより、使った方が良い場合が出てきます。しかし、ユーザーフォームは少々とっつきにくく、使い始めて使いこなすには、各種コントロールの特性を理解し、イベントについても深い知識が必要になりす。

第11回．コンボボックス(ComboBox)の追加

ユーザーフォーム入門として基礎から解説します。ユーザーフォームで良く使われるコントロールは、ラベル、テキストボックスこれは当然ですが、その次に良く使われるものは、コンボボックス、リストボックス、チェックボックス、オプションボタンになるでしょう。

第12回．リストボックス(ListBox)の追加

ユーザーフォーム入門として基礎から解説します。リストボックスについて解説します。リストボックスとコンボボックスの使い方はほぼ同様になります。前回コンボボックスで単一列（１列）の場合を説明しましたので、今回は特に複数列について詳しく説明します。

同じテーマ「VBA100本ノック」の記事

61本目：「ふりがな」の取得と設定
 62本目：独自のZLOOKUP関数を作成
 63本目：複数シートの連結
 64本目：リンクされた図（カメラ機能）
65本目：固定長テキスト出力
 66本目：全サブフォルダからファイルを探す
67本目：ComboBoxとListBox
68本目：全テキストボックスの転記
 魔球編：組み合わせ問題
 魔球編：閉領域の塗り潰し
 迷宮編：巡回セル問題

アクセスランキング・・・ランキング一覧を見る

1.最終行の取得（End,Rows.Count）｜VBA入門
2.RangeとCellsの使い方｜VBA入門
3.変数宣言のDimとデータ型｜VBA入門
4.マクロって何？VBAって何？｜VBA入門
5.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
6.繰り返し処理（For Next)｜VBA入門
7.Range以外の指定方法（Cells,Rows,Columns）｜VBA入門
8.セルに文字を入れるとは（Range,Value）｜VBA入門
9.とにかく書いてみよう（Sub,End Sub）｜VBA入門
10.繰り返し処理（Do Loop）｜VBA入門

ホーム
マクロVBA入門編
VBA100本ノック
67本目：ComboBoxとListBox

このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。

記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。

VBA練習問題
VBA100本ノック 67本目：ComboBoxとListBox