【ExcelVBA】ユーザーフォーム活用テクニック編【イニシャライズとリストボックス】

みなさん、こんにちはケンケンです。

 

今回は、ユーザーフォームのリストボックスをご紹介します。

リストボックスはフォーム上にデータリストを表示したり選択することが出来ます。

 

資料は以前にご紹介したものを使いますので以下から復習してください。

【ExcelVBA】ユーザーフォーム活用①

【ExcelVBA】ユーザーフォーム活用テクニック編【チェックボックス】

 

 

 

リストボックスを作ろう

まずリストボックスとはどういうものか完成形から見てみましょう。

完成形は以下のようになります。

社員名簿シートのA~D列までの情報をリストボックスに表示させています。

今回は、リストボックスにデータを表示させるところまで紹介します。

 

リストボックス用のフォームを作る

それではまず、リストボックス用のフォームの作成から始めていきます。

 

「挿入」→「ユーザーフォーム」を選択し新しいフォームを作ります。

 

作ったフォームのオブジェクト名は「frmList」、Captionは「リスト表示」とします。

 

リストボックスを作る

続いて、リストボックスを作ります。

ツールボックスのリストボックスを選択し、フォーム上をドラッグすると任意の大きさで作成できます。

 

上記のように作成したら、オブジェクト名を「lstHyouji」とします。

リストボックスにCaptionは必要ありません。というよりプロパティの項目にないので設定できません。

 

閉じるボタンを作る

閉じる用のボタンも作っておきましょう。

ツールボックスからコマンドボタンを選択しフォームに設置します。

 

オブジェクト名は「btnClose」、Captionは「閉じる」です。

 

ラベルを設置する

ラベルも設置しておきます。

ツールボックスからラベルを選択して、下記のように4つ配置して、それぞれCaptionを変更します

 

部品の配置は以上です。

 

フォームを閉じるコードを書く

続いて、閉じるボタンを押下した際のアクションをコードに書きます。

閉じるボタンをダブルクリックすると、btnClose_Clickプロシージャが生成されます。

そこに「Unload Me」と記述します。

 

Private Sub btnClose_Click()
    Unload Me
End Sub

 

これで、閉じるボタンをクリックするとフォームが閉じるようになります。

 

イニシャライズ(Initialize)をマスターしよう

次はリストボックスに表示させる方法を解説します。

まず、下図のようにフォームをダブルクリックします。

 

 

VBEの右上の「UserForm」と「Initialize」を選択し、UserFor_Initializeプロシージャを生成させます。

 

 

初めて出てきましたがInitializeとは初期化や初期値の設定をすることです。

Initializeで設定したものはフォームが起動する度に実行されます

このプロシージャにリストボックスの初期設定を書き込んでいきます。以下のようにコードを記述しましょう。

 

Private Sub UserForm_Initialize()
    Worksheets("社員名簿").Activate '①
    
    lstHyouji.Font.Size = 11 '②
    lstHyouji.ColumnCount = 4 '③
    lstHyouji.ColumnWidths = "20;60;70;40" '④
    lstHyouji.TextAlign = fmTextAlignLeft '⑤
    
    Dim lstC
    Dim Lrow
    Lrow = Range("A" & Rows.Count).End(xlUp).Row '⑥
    
    For lstC = 2 To Lrow '⑦
        lstHyouji.AddItem Cells(lstC, 1).Value '⑧
        lstHyouji.List(lstHyouji.ListCount - 1, 1) = Cells(lstC, 2).Value '⑨
        lstHyouji.List(lstHyouji.ListCount - 1, 2) = Cells(lstC, 3).Value '⑩
        lstHyouji.List(lstHyouji.ListCount - 1, 3) = Cells(lstC, 4).Value '⑪
    Next
End Sub

それでは、順番に解説します。

①フォーム起動時に社員名簿シートをアクティブにします。つまり、以後の処理はすべて社員名簿シート上でなされます。

 

オブジェクトのプロパティはプロパティウィンドウだけでなくコードでも設定できます。

 

 

今回は、コードで設定していきます。オブジェクト名.プロパティ名(lstHyouji.Font.Sizeなど)でそれぞれ設定できます。

フォントサイズを11にします。

③実は、リストボックスは作成時には1列しか存在しません。よって、ColumnCountを使って4列(No、氏名、フリガナ、年齢)設定します。

ColumnWidthsでリストボックスの各要素(今回は4列)の列幅を設定します。

TextAlignで文字列の配置を設定できます。「fmTextAlignLeft」は左詰めです。

ちなみに、

「fmTextAlignCenter」は中央揃え

「fTextAlignRight」は右詰

です。

 

ここまでで、リストボックスの前提条件が整いました

データを格納するキャンバスができたと言い換えても良いでしょう。

この状態まで出来たリストボックスに実際にデータを流し込んでいきます。

 

⑥社員名簿シートのA列最終行を取得し変数Lrowに格納します。

⑦ForNext構文を使って社員名簿シートに記載されているデータをリストボックスに流し込みます。

⑧AddItemでリストボックスに行を追加します。追加した行のリストボックス1列目に「Cells(lstC, 1).Value」の値、つまり、社員名簿シートのA列「No」を代入します。

⑨リストボックスに追加した行の2列目に、社員名簿シートのB列の値を代入しています。

⑩⑪は⑨と同じ

⑦~⑪までの繰り返し構文が分かりづらいと思うので補足します。

リストボックスの場所を指定するには

List(行番号,列番号)で指定できます。

しかし、リストボックスは1から始まらず0から始まるため、少しややこしくなります。

リストボックスは以下のような構造になっています。

 

lstHyouji.ListCountでリストボックスに存在する行数をカウントできます。

この条件を元に変数lstCに値を入れながら、ひとつずつ確認します。

まず、変数lstCに2が格納されている場合

⑧lstHyouji.AddItem Cells(2, 1).Value

AddItemでリストボックスに0行目を追加し、社員名簿シートのA2セルの値(1)を0列目に代入します。

下図で確認しましょう。

 

 

 

⑨lstHyouji.List(lstHyouji.ListCount – 1, 1) = Cells(2, 2).Value

ここがややこしいですが、ListCountは存在する行数ですから1から始まりますしかし、Listの行番号を指定するときは、0からです

なので、Listで行番号を指定するときは常にListCountで数えた数値から1マイナスした値となります。

つまり、lstHyouji.ListCount – 1が行を指定するコードになります。

変数lstCが2の場合は、cells(2,2)の値である「田中太郎」をList(0,1)に代入したいわけです。List(0,1)はリストボックスの1行2列目ですね。

 

先ほどAddItemで0行目を作成しListCountは1になるので、ListCount-1でリストボックスの行番号(0)を指定できます。

 

⑩⑪は⑨と同じようにデータを代入します。

以下⑦から⑪を繰り返します。

リストボックスとデータの関係性の説明は以上になります。

 

UserForm_InitializeプロシージャをF5で実行してみましょう。

無事にデータ代入済のリストが表示されました。

 

フォームを連携させよう

今回作成したリストボックス機能があるフォームを、以前作ったフォームと連携させてみましょう。

以前作ったフォームはこちらです。

このフォームにコマンドボタンを追加して、「frmList」を配置させます。

 

ツールボックスからコマンドボタンを選択し、下図のように配置します。

オブジェクト名は「btnList」、Captionは「リスト表示」としました。

リスト表示ボタンをダブルクリックします。

btnList_Clickプロシージャが生成されるので、そこに「frmList.Show」と記述します。

Private Sub btnList_Click()
    frmList.Show
End Sub

 

これで、「btnList」ボタンをクリックするとフォーム「frmList」が起動します。

複数のフォームを連携させられるようになると利便性が増し、業務効率化が促進されるはずです。

いろいろと試してみると良いでしょう。

 

テキストボックス初期化

最後におまけとして、入力した後にフォームを初期化する方法をご紹介します。

下図のフォームを参考に進めます。

 

入力ボタンをダブルクリックし、btnInput_Clickプロシージャに追記します。

前回までの復習はこちらから

【ExcelVBA】ユーザーフォーム活用①

【ExcelVBA】ユーザーフォーム活用テクニック編【チェックボックス】

 

Private Sub btnInput_Click()
    Dim Lrow
    Lrow = Range("A" & Rows.Count).End(xlUp).Row
    
    If Lrow = 1 Then
        Range("A2").Value = 1
    Else
        Range("A" & Lrow + 1).Value = WorksheetFunction.Max(Range("A2"), Range("A" & Lrow)) + 1
    End If
    
    Range("B" & Lrow + 1).Value = Userform1.txtName.Text
    Range("C" & Lrow + 1).Value = Userform1.txtFurigana.Text
    Range("D" & Lrow + 1).Value = Userform1.txtAge.Text
    Range("E" & Lrow + 1).Value = Userform1.txtJyusyo.Text
    Range("F" & Lrow + 1).Value = Userform1.txtTel.Text
    Range("G" & Lrow + 1).Value = Userform1.txtMail.Text
    Range("H" & Lrow + 1).Value = Userform1.txtTel2.Text
    
    If chkTaisyoku.Value = True Then
        Range("I" & Lrow + 1).Value = "退職"
    Else
        Range("I" & Lrow + 1).Value = "在職"
    End If
    '------------------------------ここから下を追加----------------------------------
    Userform1.txtName.Text = ""
    Userform1.txtFurigana.Text = ""
    Userform1.txtAge.Text = ""
    Userform1.txtJyusyo.Text = ""
    Userform1.txtTel.Text = ""
    Userform1.txtMail.Text = ""
    Userform1.txtTel2.Text = ""
End Sub

コード内の点線以下を追加してください。

コードはいたって単純で、テキストデータをエクセルシートに転記したあとに各テキストボックスを空欄(” ”)にしているだけです。

これで、入力が実行される度にすべてのボックスが初期化され、次のデータを打ち込みやすくなります。

 

まとめ

いかがだったでしょうか。

イニシャライズとリストボックスの表示の仕方を学びましたが、どちらもフォームを作るうえで重要な概念になります。

しかも、応用次第で業務効率をグッと上げることができますので、是非マスターしてください。

 

今回は以上となります。次回はリストボックスの応用的な使い方をご紹介していく予定です。

それではまた次回お楽しみに!

関連記事

みなさん、こんにちはケンケンです。   今回は、ユーザーフォームの活用に欠かせないスクロールバーについてご紹介します。 以前にご紹介した知識が前提になっていますので、まだ記事をご覧になっていない方や、忘れてしまった方は以下か[…]

 

ユーザーフォーム活用関連記事一覧

【ExcelVBA】ユーザーフォーム活用の基礎【業務改善の強力な武器になります】

【ExcelVBA】ユーザーフォーム活用テクニック編【チェックボックス】

【ExcelVBA】ユーザーフォーム活用テクニック編【イニシャライズとリストボックス】

【ExcelVBA】ユーザーフォーム活用テクニック編【スクロールバーについて】

【ExcelVBA】ユーザーフォーム活用【リストボックスから選択・メッセージボックス活用】

【ExcelVBA】ユーザーフォームを作りこむ【コンボボックスの使い方】

 

VBAの基礎を復習したい方は以下をご覧ください。

【エクセルVBA入門編①】VBAで何ができるのだろうか?

【エクセルVBA入門編②】めんどうな仕事はForNext構文で解決

【エクセルVBA入門編③】繰り返し作業はFor Next構文で完全自動化!

【エクセルVBA入門編④】自動記録を使ってみよう

【エクセルVBA入門編⑤】If Then構文で条件分岐をマスター

【エクセルVBA入門編⑥】手順に沿って請求書を作成してみよう【請求書作成自動化】

【エクセルVBA番外編】実務で使えるテクニック紹介します