リストボックスに値やリストを登録・追加・削除フォームを作成

2021年5月28日2024年9月10日

エクセルの入力をすればデータ集計や検索を行いますが、入力文字の間違えなどで集計や検索がうまくいかなかったことありませんか？

半角や全角の違いや誤字などが原因です。

これを防ぐためにリストボックスに記載されたリスト（項目）から選択させれば、間違えは起きません。

そこで値やリスト（項目）登録・追加などセルに直接記載してもいいのですが、ユーザーフォームを使って誰でも簡単に正確に入力できるように準備しましょう。

フォームとシートの設定

図のように白紙状態のワークシート（sheet1）にユーザーフォームを使って値やリスト（項目）を登録・追加・削除できるようにして、ユーザーフォームにはラベル・テキストボックス・リストボックス・コマンドボタン2つを配置しています。

ユーザーフォームについてはここでは省略しますので下記をご覧くださいませ。

あわせて読みたい

ユーザーフォームでオリジナル入力画面を作るエクセルのデータ入力・検索・修正作業ってとても面倒で時間と手間がかかりませんか？この作業を簡単に素早く終わらせたくないですか？エクセル使うなら今話題の時短...

リストボックスを常に自動更新させる

入力フォームを作ろうとすると最初に登録ボタンからVBAコードを記載しがちですが、リストボックスがメインの入力フォームなので準備から先に行いましょう。

なぜならば、いろいろやったとしてもリストボックスのリストが常に最新版のリストになっていなければ、使い物になりません。

そこでユーザーフォームが表示される時に最新の値やリスト（項目）を取得してから、リストボックスに表示させるように設定します。

UserForm_Initialize

ユーザーフォームが表示される時に最新の値やリスト（項目）を取得させるには、UserForm_Initializeを使用します。

UserForm_Initialize

Private Sub UserForm_Initialize()
ListBox1.RowSource = Range(“A1”).CurrentRegion.Address
End Sub

VBA解説

ユーザーフォームイニシャライズイベントを実行する
リストボックス1のセル範囲の値はセルA1を含む入力済みセルの範囲とする
マクロ記録終了

CurrentRegion.Addressで、A1セルから下方向へ空白セルを見つけたら、その1つ上のセルまでをリストボックスに表示させるセルの範囲とすることができるので、ユーザーフォームが表示されるたびに最新のセルの範囲を取得することができます。

あわせて読みたい

UserForm_Initializeでセル値を取得してから表示する方法オリジナル入力フォームとして使えるエクセルVBAのユーザーフォーム。ラベルやコマンドボタンを複数配置するのは当たりまえですが、顧客リストなどで名前や性別などを...

Worksheet_Change

次にA列に変更があった場合（値やリスト（項目）が追加するされた場合）に、すでに表示中のユーザーフォームのリストボックスに即反映表示させるように設定します。

その方法はリストを記載しているシート（Sheet1）にVBAコードを記載することにより可能となります。

Worksheet_Change

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
UserForm1.ListBox1.RowSource = Target.CurrentRegion.Address
End If
End Sub

VBA解説

シートに変更があった場合に処理をする
もしセルA列に変更があったら
ユーザーフォーム1のリストボックス1のリスト取得範囲は、変更されたセルを含む入力済みセル番地とする
Ifステートメント終了
マクロ記録終了

これにより、追加したい値やリスト（項目）入力してを登録ボタンを押したら、セルに記載されてさらにリストボックスにも即座に反映されます。

あわせて読みたい

リストボックスRowSourceプロパティ自動更新機能VBA ユーザーフォームのリストボックスを使う時に、表示させるリスト（項目）をセル範囲とリンク・連動させるためにプロパティのRowSourceを設定しますが、リスト（項目）追...

とりっぷぼうる

これで準備完了です。

それでは次に登録ボタンにVBAコードを書いてきましょう。

登録ボタンの設定

UserForm_InitializeとWorksheet_ChangeのVBA設定が完了したことで、これからVBA設定する登録ボタンを押すことによりセルに表示・記載されて、そのままリストボックスにも表示・記載できるようにします。

ここで値やリスト（項目）を初回登録する場合（セルA列が空欄の状態）と、2つ目の値以降の2種類の登録方法がある事を忘れずに設定する必要があります。

リスト登録ボタンの設定

Private Sub CommandButton1_Click()
If Range(“A1”).Value = “” Then
Range(“A1”).Value = TextBox1.Value
TextBox1.Value = “”
Else
Cells(Rows.Count, 1).End(xlUp).Offset(1, 0) = TextBox1.Value
TextBox1.Value = “”
End If
End Sub

VBA解説

コマンドボタン1がクリックされたら
もしセルA1が空欄だったら（初回項目登録の時）
セルA1にテキストボックス1の入力文字を追加する
テキストボックスを空欄にする
そうでなかったら（2つ目以降）
最初の列（A列）の入力済み最終行（空欄セルの1つ上のセル）の1つ下のセルにテキストボックス1の入力文字を追加する
テキストボックスを空欄にする
Ifステートメント終了
マクロ記録終了

これでリストがリアルタイムで追加できるようになりました。

そして入力済みになったらテキストボックスを空欄にして、二重登録を防ぐのと、次のリストを入力する時に空欄にする手間を無くす為に追加しておいた方が便利ですよ。

ちなみにリストボックスはリスト1つではなく2つ以上の複数列表示させる事も可能なのですが、初期設定では使えないので設定変更して複数列対応のリストボックスにすることができます。

あわせて読みたい

セル範囲を自動取得してリストボックスの項目自動追加リストボックスのリストの追加って自動でできないものか？ A1～A10までの範囲を超えてリストが増えたら時は、リストボックスのプロパティでRowSouceを修正したり、VBAコ...

削除ボタンの設定

値やリスト（項目）の登録や追加があるならば当然削除も必要で、これが無ければ削除の時だけワークシートを直接触る事になるので、ユーザーフォームでの入力・削除が完結しません。

ただし、ここでは値やリスト（項目）が追加されると自動的にセルの範囲（RowSource）が書き換えられるようにしているので、RemoveItemメソッドは使えません。

そこで、リストボックスで値やリスト（項目）を選択状態にしたら、削除するように設定します。

さらに削除されたことによりセルが空欄になると、リストボックスに空欄以下のリストが表示されなくなってしまうので上方向に空欄を詰めるようにします。

リスト削除ボタン

Private Sub CommandButton2_Click()
With ListBox1
For i = 0 To .ListCount – 1
If .Selected(i) = True Then
Cells(i + 1, 1).Value = “”
End If
Next i
End With
F = WorksheetFunction.CountA(Range(“A:A”))
For j = 1 To F
If Cells(j, 1).Value = “” Then
Cells(j, 1).End(xlDown).Cut
Cells(j, 1).Select
ActiveSheet.Paste
End If
Next j
Unload Me
UserForm1.Show
End Sub