みなさんこんにちはケンケンです。
今回は、前回お伝えしたユーザーフォームにデータを流し込んでみたいと思います。
以前にも紹介したことがあるのですが、その時とちょっと違う方法をお伝えしたいと思います。
ではいってみましょう。
みなさんこんにちは、ケンケンです。 今回は、VBAのユーザーフォームにひと工夫することで有効活用する方法をご紹介します。 テクニックのひとつとして覚えておくといいでしょう。 それではいってみましょう。 […]
リストボックスにデータを流し込む方法
方法はいくつかあるので、それぞれ紹介します。
前回作ったフォームはこんな感じでした。
プロパティウィンドウで設定する
これが一番簡単な方法でしょうか。
その代わりあまり汎用性はありません。ひとつの例として覚えておくとよいでしょう。
マスターを作ろう
データを流し込むために元データとして以下の３つのマスターを作りました。
リストボックスのプロパティウィンドウで設定する
リストボックスに表示する方法のひとつとしてVBEのプロパティウィンドウで設定する方法があります。
上図のように「ColumnCount」と「ColumnWidths」と「RowSource」をそれぞれ設定します。
すると簡単にデータを流し込めます。
この方法は、汎用性がないのでデータが固定されている場合に使うことを推奨します。
コードで設定する（シート名を指定）
シート名を指定するコードを記述することでデータを流し込んでいきます。
UserForm_Initializeプロシージャに記述しよう
「F_Mst_List」フォームの「 UserForm_Initialize」プロシージャに以下のように記述します。
|
1 2 3 4 5 6 7 8 9 |
Private Sub UserForm_Initialize() Dim opt As MSForms.OptionButton For Each opt In F_Master.frm_select.Controls If opt.Value = True Then Me.lbl_title.Caption = opt.Caption End If Next opt Me.lbx_table.RowSource = Me.lbl_title.Caption & "!A2:B10" '① End Sub |
①このように記述することでRowSourceプロパティを設定できます。
ただし、このままでは範囲が「A2からB１０」までで固定されてしまいます。
「B１０」の「１０」を最終行として値を取得出来たら、データの増減に対応できそうです。
Functionプロシージャを作ってみよう
最終行を取得する用の関数を自前で書いてしまいましょう。
標準モジュールを作成し、モジュール名を「Function_1」として関数を記述してみます。
|
1 2 3 |
Public Function getLrow(ByVal ws As Worksheet) As Long getLrow = ws.Cells(Rows.Count, 1).End(xlUp).Row End Function |
このように記述すると、指定されたシートのA列の最終行を取得してくれます。
よく使うコードはこのようにFunctionプロシージャにまとめておくとよいでしょう。
関数を使えるようにしてみよう
Functionプロシージャを使えるようにするには、シートを引数に使えなくてはなりません。
「ByVal ws As Worksheet」←この部分が「ワークシートを引数にとりますよ」という意味です。
なので、関数を呼び出したいプロシージャにはシートを「名前」ではなく「オブジェクト」としてしっかり定義する必要があります。
コードで表現すると以下のようになります。
|
1 2 3 4 5 6 7 8 9 10 11 |
Private Sub UserForm_Initialize() Dim ws As Worksheet '① Dim opt As MSForms.OptionButton For Each opt In F_Master.frm_select.Controls If opt.Value = True Then Set ws = Sheets(opt.Caption) '② End If Next opt Me.lbl_title.Caption = ws.Name '③ Me.lbx_table.RowSource = ws.Name & "!A2:B" & getLrow(ws) '④ End Sub |
①ワークシート型の変数を宣言します。
②オプションボタンがTrueだったらシートを変数に代入します。
③ワークシート名にタイトル名を変更します。
④B列最終行を関数を使って取得します。
これで、過不足なくそれぞれ選択したマスターの一覧を取得することができるようになります。
以下のようにリストボックスに表示出来たら成功です。
工夫次第でいろいろな方法が考えられるので、幅を広げるためにも試してみてください。
みなさん、こんにちはケンケンです。 今回は、ユーザーフォームの作り方をご紹介します。 実務でよく耳にするのは「管理データを作っているけど、横に長い表にたくさんの項目を入力するのが面倒！」といった声です。 […]
