VBAユーザーフォームとコンボボックス

VBA
スポンサーリンク

ユーザーフォームを使って、データベースのコード列からコードを検索して、レコードを抽出する方法を紹介します。
活用例としては、ユーザーフォームで「社員コード」を選択して、抽出したレコードの各フィールドデータを各種書類に転記します。

スポンサーリンク
スポンサーリンク

コード(コンボボックスへの値の追加まで)

Private Sub UserForm_Initialize()
    Application.ScreenUpdating = False
    Dim MyDic As Object
    Set MyDic = CreateObject("scripting.dictionary") 'ディクショナリで社員コードの重複チェック
    Dim max As Long
    Dim i As Long
    Dim 社員コード As String
    Dim 社員コードvar As Variant
    ThisWorkbook.Sheets("社員データ").Activate
    If ThisWorkbook.Sheets("社員データ").AutoFilterMode = True Then ThisWorkbook.Sheets("社員データ").Range("A1").AutoFilter
    max = ThisWorkbook.Sheets("社員データ").Cells(Sheets("社員データ").Rows.Count, "B").End(xlUp).Row '最終行取得「社員名列」を使用(社員コードの空白セル対応)
    ThisWorkbook.Sheets("社員データ").Range(Cells(1, "A"), Cells(max, "F")).Sort key1:=Sheets("社員データ").Range("A1"), order1:=xlAscending, Header:=xlYes '社員コードで昇順ソート
    '社員コードの重複削除
    For i = 2 To max
        社員コード = ThisWorkbook.Sheets("社員データ").Cells(i, 1).Value
        If 社員コード <> "" Then '社員コードが空欄でなければ
            If Not MyDic.exists(社員コード) Then '重複チェック(条件分岐:重複がなければ)
                MyDic.Add 社員コード, 社員コード 'ディクショナリに追加
            Else
                MsgBox "社員コードに重複があります。" & vbCrLf & "起動を終了します。" & vbCrLf & "社員コード" & 社員コード '重複メッセージ
                ThisWorkbook.Sheets("社員データ").Select
                Call 閉じるbtn_Click
                End
            End If
        Else
            MsgBox "社員コードが空欄のレコードがあります。" '社員コードが空白のメッセージ
            ThisWorkbook.Sheets("社員データ").Select
            Call 閉じるbtn_Click
            End
        End If
    Next i
          
        社員コードvar = MyDic.items 'A列:社員コードリストを一旦バリアント変数に格納(ディクショナリが機能しないため)
        cb開始.List = 社員コードvar 'コンボボックス配列として値を追加
        cb終了.List = 社員コードvar
        cb開始.Text = 社員コードvar(0)
        cb終了.Text = 社員コードvar(0)
    Set MyDic = Nothing
    Application.ScreenUpdating = True
End Sub

コード概要(大まかな流れ)

  1. 社員コードを昇順でソート
  2. 「社員コード」列の空白のチェック
  3. 「社員コード」をディクショナリオブジェクトを使って重複削除
  4. 「社員コード」の値をコンボボックスに追加

※データベースは「社員データ」シートにあります。

本記事では上記コード解説の前に、基本的なユーザーフォームのコンボボックスの値の追加方法について解説します。

スポンサーリンク

ユーザーフォーム

ユーザーフォームとは、入力フォームを自由にデザインして利用できるVBAの機能のことです。
つまり、必要なコントロール(テキストボックス、ボタン、ラベルなど)を配置してオリジナルのユーザーフォームを作成できます。

ユーザーフォームのメリット・使いどころ

  1. 複数人でファイル共有して、データを入力する場合、入力に制限をかけることができるのでワークシート自体の形式や書式が崩れない。
  2. 誰でも入力しやすい

※ただ、これまでの経験として、あまり複雑なフォームを作るのであればGoogleフォーム等の既存フォームを使った方が早いし便利です。
ただ、上記コードのようにワークシート上のデータを検索キーとして利用する場合や簡易的なものであればユーザーフォームは有効です。

ユーザーフォームの設定方法

「挿入」タブ→「ユーザーフォーム」をクリックします。

コントロールの作成

各種コントロールについてはツールボックスから選びます。

スポンサーリンク

コンボボックスの設定

コンボボックスは、ボックス内に選択肢を表示し、ユーザーがその中から1つを選択できるコントロールであり、ユーザーフォームのテキストボックスとリストボックスが合体したコントロールです。

コンボボックスのオブジェクト名の設定

コンボボックスを複数設定する場合、区別できるように任意の名前に設定しておきます。オブジェクト名にcbとかcmbを付けると名前でコンボボックスだと分かりやすいです。
コンボボックスを選択して、VBE左側のプロパティウィンドウでコンボボックスのオブジェクト名を変更できます。

下図では「cbごはん」と設定しています。

スポンサーリンク

コンボボックスへの値の追加

コンボボックスに値を追加する方法を紹介していきます。

コンボボックスへの値の追加は、ユーザーフォームが表示される前に発生する Initialize イベントに対するイベントプロシージャにコードを記述します。

Initialize

「ユーザーフォーム」をダブルクリックまたは右クリック:「コード表示」エディタのタブをInitializeに変更してプロシージャを表示します。

AddItemメソッド

AddItemメソッドを使って、コンボボックスに値を追加します。

Private Sub UserForm_Initialize()
  cbごはん.AddItem "カレー"
    cbごはん.AddItem "ラーメン"
    cbごはん.AddItem "焼肉"
    cbごはん.AddItem "スパゲッティ"
End Sub

Listプロパティ

Listプロパティ配列を使ってコンボボックスに値を一括で追加することができます。

Private Sub UserForm_Initialize()
    Dim 食べ物リスト(3) 
    食べ物リスト(0)= "カレー"
  食べ物リスト(1)= "ラーメン"
  食べ物リスト(2)= "焼肉"
  食べ物リスト(3)= "スパゲッティ"
    cbごはん.List = 食べ物リスト
End Sub

または、ワークシートの値を配列に格納して、Listプロパティで追加します。※2次元配列になります。

Private Sub UserForm_Initialize()
    Dim 食べ物リスト As Variant
    食べ物リスト = Sheets("食べ物").Range(Cells(2, 1), Cells(5, 1))
    cbごはん.List = 食べ物リスト
End Sub

RowSorceプロパティ

RowSorceプロパティを使ってワークシート上のセル範囲のデータをコンボボックスに追加することができます。

(注意)「External:=True」を設定しておかないと、アクティブになっているシートのセル範囲が値リストに設定されます。

Private Sub UserForm_Initialize()
    cbごはん.RowSource = Sheets("食べ物").Range(Cells(2, 1), Cells(5, 1)).Address(external:=True)
End Sub

RowSorceプロパティをプロパティウィンドウで設定

プロパティウィンドウにシート名セル番地を入力して値を設定します。

スポンサーリンク

コンボボックスへの初期値の設定

これまでコンボボックスへの値の追加方法を紹介しましたが、ユーザーフォームが立ち上がった時点ではコンボボックスは空欄になります。(下図)

そこでユーザーフォームが立ち上がった時点でのコンボボックスへの値の設定方法を紹介します。(初期値の設定)

ListIndex プロパティ

ListIndexプロパティで選択しているデータの行位置を取得できます。
インデックス値はリストの一番上を「0」として数えます。

Private Sub UserForm_Initialize()
    cbごはん.ListIndex = 1
End Sub

Textプロパティと配列番号での初期値設定

Textプロパティと配列番号を使って初期値を設定できます。

Private Sub UserForm_Initialize()
    Dim 食べ物リスト(3)
    食べ物リスト(0) = "カレー"
    食べ物リスト(1) = "ラーメン"
    食べ物リスト(2) = "焼肉"
    食べ物リスト(3) = "スパゲッティ"
    cbごはん.Text = 食べ物リスト(0) '初期値設定
End Sub

初期値として、食べ物リスト(0)のラーメンが追加されます。

スポンサーリンク

まとめ

ユーザーフォームのコンボボックスの値の追加方法について紹介しました。

  1. ユーザーフォームの挿入
  2. コンボボックス(ボックス内に選択肢を表示し、ユーザーがその中から1つを選択できるコントロール)
  3. コンボボックスへの値の追加方法(AddItemメソッド、配列を使ったListプロパティ、ワークシートの値を取得するRowSorceプロパティ)
  4. コンボボックスの初期値の設定(ListIndex プロパティTextプロパティと配列番号での初期値設定

次回、コンボボックスの項目の中から選択された値を取得する方法について紹介します。

ユーザーフォームとコンボボックスの値の取得
コンボボックスの値を取得するためのTextプロパティ、Valueプロパティ、Listプロパティ/ListIndexプロパティを紹介します。

コメント

タイトルとURLをコピーしました