Excelのユーザーフォームを使用して住所録を更新する仕組みを作成します。
住所録データの一覧を表示するフォームと、編集するフォームを作成していきます。
前回の記事でAccessのフォームを使用した住所録の作成方法を紹介しましたが、同じ内容でExcelのユーザーフォームを使用した方法のご紹介です。
完成イメージ
① メニューシートに「住所録」ボタンを配置します。
ボタンをクリックすると住所録一覧フォームが表示されます。
② 一覧フォーム：対象のデータをクリックすると編集フォームが立ち上がります。
例）No.16のデータをクリック
③ 編集フォーム：変更箇所を修正して「登録」ボタンをクリックするとデータを更新します。
例）「岩崎祐一郎」さんを「岩崎祐一」さんに修正して「登録」ボタンをクリックします。
④ 「登録」ボタンをクリックしたあとの画面。
編集フォームが閉じて一覧フォームに戻ります。
「修正が完了しました」のメッセージが出力されて変更点が一覧フォームに反映されています。
例）No.16の岩崎さんの名前が「祐一郎」から「祐一」に変わったことが確認できました。
作成するもの（全体像）
① メニューシートの作成
② dataシートの作成
③ リストシートの作成
④ 「一覧」ユーザーフォームの作成
⑤ 「一覧」ユーザーフォームを開くVBAの作成
⑥ 「一覧」ユーザーフォームから「編集」ユーザーフォームの呼び出し
⑦ 「編集」ユーザーフォームの作成
⑧ 「編集」ユーザーフォームを開くVBAの作成
⑨ 「編集」ユーザーフォームからデータを更新するVBAの作成
① メニューシートの作成
まず住所録ユーザーフォームを開くためのボタンを作成します。
シート名：menu
開発タブの「挿入」メニューから「ボタン」を配置してください。
後ほど⑤の工程でボタンをクリックしたときのVBAを作成しますので今はマクロの登録はまだ行わなくて大丈夫です。
② dataシートの作成
住所録のデータシートを作成します。
住所録の項目（列）
・所属部門
・氏名
・氏名（ひらがな）
・生年月日
・性別
・メールアドレス
・郵便番号
・住所
※住所録データの内容はダミーデータ生成サイトで作成した実在しないデータを使用しています。
③ リストシートの作成
住所録のユーザーフォームで使用するためのリストをリストシートに作成しておきます。
１行目はCOUNTA関数を使用して、データの個数+2で最終行数が表示されるようにしています。
ユーザーフォームのコンボボックスのデータ参照範囲として3行目～最終行数までを範囲とするように指定します。
④ 「一覧」ユーザーフォームの作成
VBEの挿入タブからユーザーフォームを選択し、ユーザーフォームを作成します。
一覧フォームでは「部門」のコンボボックスを配置し、部門で絞込みが可能な仕様にしています。
▽ユーザーフォームのプロパティ
・オブジェクト名：UserForm_AdressDataBase
・Caption:住所録_一覧
▽設置するもの
・部門コンボボックス
・絞込ボタン
・一覧リストビュー
今回使用している「リストビュー」コントロールはデフォルトでは追加されていないため、ツールボックス上で右クリック→コントロールの追加をクリックして、「Microsoft ListView Control,version 6.0」を追加する必要があります。
１度追加すると毎回ツールボックスより使用できるようになります。
リストボックスでも良いのですが、リストビューの方が罫線が引けたり、見やすい一覧が表示できるため今回はリストビューを使用しています。
▽「リストビュー」コントロールの追加方法
⑤ 「一覧」ユーザーフォームを開くVBAの作成
はじめに後ほど使用する共通変数の「TargetNo」変数を標準モジュールで宣言しておきます。
メニューシートの「住所録」ボタンをクリックしたときの一覧フォームを呼び出すコードもこちらに書いておきます。
Public TargetNo As String
Sub 住所録()
UserForm_AdressDataBase.Show
End Sub一覧フォームを表示させるためのコードを作成します。
部門コンボボックスのリストの値は「RowSource」メソッドでリストリートのA3セル～A列の最終行までを指定しています。（最終行は1行目でCountA関数で表示）
リストビューの表示設定をここで作成します。
ヘッダーを追加し、表示させるサイズをそれぞれ指定しています。
リストビューに値をセットするVBAは別のプロシージャ「UpdateListView」で作成し、ここから呼び出します。
Private Sub UserForm_Initialize()
'部門コンボボックスの値をセット
Me.ComboBox_部門.RowSource = "リスト!A3:A" & Worksheets("リスト").Cells(1, 1).Value
'リストビューの表示設定
With Me.ListView1
.View = lvwReport
.LabelEdit = lvwManual
.HideSelection = False
.AllowColumnReorder = True
.FullRowSelect = True
.Gridlines = True
.ColumnHeaders.Add , "Title00", "No.", 25
.ColumnHeaders.Add , "Title01", "部門", 50
.ColumnHeaders.Add , "Title02", "氏名", 60
.ColumnHeaders.Add , "Title03", "ひらがな", 60
.ColumnHeaders.Add , "Title04", "生年月日", 80
.ColumnHeaders.Add , "Title05", "性別", 25
.ColumnHeaders.Add , "Title06", "メールアドレス", 85
.ColumnHeaders.Add , "Title07", "郵便番号", 50
.ColumnHeaders.Add , "Title08", "住所", 120
End With
'リストビューに値をセット
UpdateListView
'リストビューにフォーカスをセット
ListView1.SetFocus
End Sub↓リストビューに値をセットするためのコード（UpdateListView）
dataシートの２行目から最終行までを１行ずつ繰り返し、リストビューに値をセットしていきます。
「部門」コンボボックスが選択されているときは部門名が一致する場合だけ値をセットするようにしています。
Private Sub UpdateListView()
Dim r As Integer
Dim LastRow As Integer
'最終行をセット
LastRow = WorksheetFunction.CountA(Worksheets("data").Range("A:A"))
'コンボボックス部門が選択されていない場合（初回立ち上げ時）
If Me.ComboBox_部門.Value = "" Then
'dataシート2行目～最終行まで繰り返し
For r = 2 To LastRow
'リストビューデータを追加する
With ListView1.ListItems.Add
.Text = r - 1
.SubItems(1) = Worksheets("data").Cells(r, 1).Value
.SubItems(2) = Worksheets("data").Cells(r, 2).Value
.SubItems(3) = Worksheets("data").Cells(r, 3).Value
.SubItems(4) = Worksheets("data").Cells(r, 4).Value
.SubItems(5) = Worksheets("data").Cells(r, 5).Value
.SubItems(6) = Worksheets("data").Cells(r, 6).Value
.SubItems(7) = Worksheets("data").Cells(r, 7).Value
.SubItems(8) = Worksheets("data").Cells(r, 8).Value
End With
Next
Else
'dataシート2行目～最終行まで繰り返し
For r = 2 To LastRow
'コンボボックス部門で選択された値と一致する場合のみリストビューに追加する
If Worksheets("data").Cells(r, 1) = Me.ComboBox_部門.Value Then
With ListView1.ListItems.Add
.Text = r - 1
.SubItems(1) = Worksheets("data").Cells(r, 1).Value
.SubItems(2) = Worksheets("data").Cells(r, 2).Value
.SubItems(3) = Worksheets("data").Cells(r, 3).Value
.SubItems(4) = Worksheets("data").Cells(r, 4).Value
.SubItems(5) = Worksheets("data").Cells(r, 5).Value
.SubItems(6) = Worksheets("data").Cells(r, 6).Value
.SubItems(7) = Worksheets("data").Cells(r, 7).Value
.SubItems(8) = Worksheets("data").Cells(r, 8).Value
End With
End If
Next
End If↓部門名の絞込みボタンをクリックしたときのコード
Private Sub CommandButton_絞込_Click()
'コンボボックス部門で部門名を選択し、「絞込み」ボタンをクリックした場合の動作
'リストビューの値を一旦クリアにする
ListView1.ListItems.Clear
'リストビューに値をセット
UpdateListView
'リストビューにフォーカスをセット
ListView1.SetFocus
End Subここまで作成したら、動きを確認してみましょう。
menuシートの「住所録」ボタンをクリックすると「一覧」フォームが表示されます。
部門名を選択して、絞込ボタンをクリックすると選択した部門名のデータだけに絞り込まれます。
↓絞込ボタンクリック後
⑥「一覧」フォームから「編集」フォームの呼び出し
一覧フォームのVBAの最後はリストビューのデータをクリックしたときの動きを作成していきます。
アイテムをクリックするとアイテムのNo.にプラス1した数を「TargetNo」変数に格納しています。
この数はdataシートの行数を表していて、このあと作成する編集フォームでの値セット時に使用します。
Private Sub ListView1_ItemClick(ByVal Item As MSComctlLib.ListItem)
'リスト内のアイテムをクリック
'→編集フォームに値を反映する
'Itemの数字+1を変数に格納する
TargetNo = Item + 1
'一覧フォームを閉じる
Unload Me
'編集フォーム呼び出し
UserForm_EditView.Show
End Subここまでの作業で、menuシートから住所録の一覧を呼び出す部分までが完了しました。
⑦ 「編集」ユーザーフォームの作成
一覧フォームの次に編集フォームを作成していきます。
所属部門と性別はコンボボックスで、他はテキストボックスで作成します。
▽ユーザーフォームのプロパティ
・オブジェクト名：UserForm_EditView
・Caption:住所録_編集フォーム
⑧ 「編集」ユーザーフォームを開くVBAの作成
先ほど一覧フォームでデータをクリックしたときに保存した「TaegetNo」変数を使用して、dataシートからフォームにデータをセットしていきます。
（TaegetNo変数はdataシートの行数を表しているため、-1してリストNoに合わせています。）
部門と性別のコンボボックスのリストの値もここでセットします。
Private Sub UserForm_Initialize()
'編集フォームを表示
'対象No
TextBox_No.Value = TargetNo-1
With Worksheets("data")
ComboBox_部門.Value = .Cells(TargetNo, 1)
TextBox_氏名.Value = .Cells(TargetNo, 2)
TextBox_ひらがな.Value = .Cells(TargetNo, 3)
TextBox_生年月日.Value = .Cells(TargetNo, 4)
ComboBox_性別.Value = .Cells(TargetNo, 5)
TextBox_メールアドレス.Value = .Cells(TargetNo, 6)
TextBox_郵便番号.Value = .Cells(TargetNo, 7)
TextBox_住所.Value = .Cells(TargetNo, 8)
End With
'部門コンボボックスの値をセット
Me.ComboBox_部門.RowSource = "リスト!A3:A" & Worksheets("リスト").Cells(1, 1).Value
'性別コンボボックスの値をセット
Me.ComboBox_性別.RowSource = "リスト!B3:B" & Worksheets("リスト").Cells(1, 2).Value
End Sub一覧フォームでデータをクリックすると、↓のように編集フォームに対象のデータが表示されるようになりました。
⑨ 「編集」ユーザーフォームからデータを更新するVBAの作成
さいごに、編集フォームで内容を変更して「登録」ボタンをクリックしたときの動きを作成していきます。TaegetNo変数を使用して、フォームの値をdataシートに反映していきます。
反映後は編集フォームを閉じて「修正が完了しました」のメッセージを表示し、再度一覧フォームを立ち上げて終わりです。
Private Sub CommandButton_登録_Click()
'登録ボタンクリック時
'dataシートにフォームの値を反映
With Worksheets("data")
.Cells(TargetNo, 1) = ComboBox_部門.Value
.Cells(TargetNo, 2) = TextBox_氏名.Value
.Cells(TargetNo, 3) = TextBox_ひらがな.Value
.Cells(TargetNo, 4) = TextBox_生年月日.Value
.Cells(TargetNo, 5) = ComboBox_性別.Value
.Cells(TargetNo, 6) = TextBox_メールアドレス.Value
.Cells(TargetNo, 7) = TextBox_郵便番号.Value
.Cells(TargetNo, 8) = TextBox_住所.Value
End With
'編集フォームを閉じる
Unload Me
'完了メッセージ
MsgBox "修正が完了しました。"
'住所録一覧フォームを再度表示
UserForm_AdressDataBase.Show
End Subこれですべて完了です！
一連の動作確認を行ってみてください。
※コードのコピー利用について
・コードのコピーは自由におこなっていただけます。
・気を付けて作成はしていますがコードには誤りがある可能性があります。
・自身の環境で動作確認をしていますが、すべての方の環境で同様に動くことは保証できません。
・データの破損等の責任は負いかねますのでご自身の責任のもとお使いください。
