エクセルの入力をすればデータ集計や検索を行いますが、入力文字の間違えなどで集計や検索がうまくいかなかったことありませんか?
半角や全角の違いや誤字などが原因です。
これを防ぐためにリストボックスに記載されたリスト(項目)から選択させれば、間違えは起きません。
そこで値やリスト(項目)登録・追加などセルに直接記載してもいいのですが、ユーザーフォームを使って誰でも簡単に正確に入力できるように準備しましょう。
フォームとシートの設定
図のように白紙状態のワークシート(sheet1)にユーザーフォームを使って値やリスト(項目)を登録・追加・削除できるようにして、ユーザーフォームにはラベル・テキストボックス・リストボックス・コマンドボタン2つを配置しています。
ユーザーフォームについてはここでは省略しますので下記をご覧くださいませ。
リストボックスを常に自動更新させる
入力フォームを作ろうとすると最初に登録ボタンからVBAコードを記載しがちですが、リストボックスがメインの入力フォームなので準備から先に行いましょう。
なぜならば、いろいろやったとしてもリストボックスのリストが常に最新版のリストになっていなければ、使い物になりません。
そこでユーザーフォームが表示される時に最新の値やリスト(項目)を取得してから、リストボックスに表示させるように設定します。
UserForm_Initialize
ユーザーフォームが表示される時に最新の値やリスト(項目)を取得させるには、UserForm_Initializeを使用します。
CurrentRegion.Addressで、A1セルから下方向へ空白セルを見つけたら、その1つ上のセルまでをリストボックスに表示させるセルの範囲とすることができるので、ユーザーフォームが表示されるたびに最新のセルの範囲を取得することができます。
Worksheet_Change
次にA列に変更があった場合(値やリスト(項目)が追加するされた場合)に、すでに表示中のユーザーフォームのリストボックスに即反映表示させるように設定します。
その方法はリストを記載しているシート(Sheet1)にVBAコードを記載することにより可能となります。
これにより、追加したい値やリスト(項目)入力してを登録ボタンを押したら、セルに記載されてさらにリストボックスにも即座に反映されます。
これで準備完了です。
それでは次に登録ボタンにVBAコードを書いてきましょう。
登録ボタンの設定
UserForm_InitializeとWorksheet_ChangeのVBA設定が完了したことで、これからVBA設定する登録ボタンを押すことによりセルに表示・記載されて、そのままリストボックスにも表示・記載できるようにします。
ここで値やリスト(項目)を初回登録する場合(セルA列が空欄の状態)と、2つ目の値以降の2種類の登録方法がある事を忘れずに設定する必要があります。
これでリストがリアルタイムで追加できるようになりました。
そして入力済みになったらテキストボックスを空欄にして、二重登録を防ぐのと、次のリストを入力する時に空欄にする手間を無くす為に追加しておいた方が便利ですよ。
ちなみにリストボックスはリスト1つではなく2つ以上の複数列表示させる事も可能なのですが、初期設定では使えないので設定変更して複数列対応のリストボックスにすることができます。
削除ボタンの設定
値やリスト(項目)の登録や追加があるならば当然削除も必要で、これが無ければ削除の時だけワークシートを直接触る事になるので、ユーザーフォームでの入力・削除が完結しません。
ただし、ここでは値やリスト(項目)が追加されると自動的にセルの範囲(RowSource)が書き換えられるようにしているので、RemoveItemメソッドは使えません。
そこで、リストボックスで値やリスト(項目)を選択状態にしたら、削除するように設定します。
さらに削除されたことによりセルが空欄になると、リストボックスに空欄以下のリストが表示されなくなってしまうので上方向に空欄を詰めるようにします。
2行目から7行目までが1つのブロックになっていて、リストボックスからリストを選択状態にしてし削除ボタンを押せばA列のセルに記載された値やリスト(項目)が空欄になります。
8行目は9行目に使う入力済みセルの数を数えるCountA関数を使います。
9行目から15行目で空欄セルを見つけたら上方向に詰める為に切り取って貼り付けることにより可能になります。
最後にフォームを閉じて再度開く事により、最新のリストに更新されるという事です。
これでリストボックスに値やリスト(項目)を登録・削除できるオリジナルフォームが出来上がりました。