ユーザーフォームを使って、データベースのコード列からコードを検索して、レコードを抽出する方法を紹介します。
活用例としては、ユーザーフォームで「社員コード」を選択して、抽出したレコードの各フィールドデータを各種書類に転記します。
コード(コンボボックスへの値の追加まで)
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
コード概要(大まかな流れ)
- 社員コードを昇順でソート
- 「社員コード」列の空白のチェック
- 「社員コード」をディクショナリオブジェクトを使って重複削除
- 「社員コード」の値をコンボボックスに追加
※データベースは「社員データ」シートにあります。
本記事では上記コード解説の前に、基本的なユーザーフォームのコンボボックスの値の追加方法について解説します。
ユーザーフォーム
ユーザーフォームとは、入力フォームを自由にデザインして利用できるVBAの機能のことです。
つまり、必要なコントロール(テキストボックス、ボタン、ラベルなど)を配置してオリジナルのユーザーフォームを作成できます。
ユーザーフォームのメリット・使いどころ
- 複数人でファイル共有して、データを入力する場合、入力に制限をかけることができるのでワークシート自体の形式や書式が崩れない。
- 誰でも入力しやすい
※ただ、これまでの経験として、あまり複雑なフォームを作るのであれば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つを選択できるコントロール)
- コンボボックスへの値の追加方法(AddItemメソッド、配列を使ったListプロパティ、ワークシートの値を取得するRowSorceプロパティ)
- コンボボックスの初期値の設定(ListIndex プロパティ、Textプロパティと配列番号での初期値設定)
次回、コンボボックスの項目の中から選択された値を取得する方法について紹介します。
コメント