【ExcelVBA】リストボックスからシートに食品を追加してみよう!【ユーザーフォーム】

Excel

「栄養計算ソフトにユーザーフォームを導入しよう」の連載第3回です。

以下の記事の続きになります。

【ExcelVBA】フォームを設置→開くをやってみよう!【ユーザーフォーム】
「栄養計算ソフトにユーザーフォームを導入しよう」の連載第2回。ExcelVBAのユーザーフォームを栄養計算ソフトに導入します。今回は、ユーザーフォームを実際に設置→開くまでをやってみたいと思います。

今回は、ユーザーフォームにリストボックスを追加し、それを用いて食品を入力できるようにしてみたいと思います。

では行きましょう。

今回目指す形

はじめに、今回最終的に目指す形をお伝えしたいと思います。

まず、以下のようなリストボックスとオプションボタンが配置されたフォームを作成します。

 

そして、リストボックスで食品が選択された状態で「追加する」ボタンをクリックすると、アクティブセルの行の食品番号を入力する列に、食品番号が追加される、という処理を実装していきます。

 

では早速行きましょう。

 

フォームにコントロールを追加しよう

まずは、フォームに各コントロールを追加していきましょう。以下の手順で行ってください。

リストボックスとオプションボタンを追加しよう

今回追加するコントロールは、リストボックスとオプションボタンです。ツールボックスからそれぞれ選択し、フォーム:食品群から入力する に配置してください。先に示した、最終的な完成形と似たような配置であれば、個人の好みで配置してもらっても構いません。

オブジェクト名を変更しよう

追加したコントロールのオブジェクト名を変更します。以下のように変更してください。
・リストボックス:lstFood
・オプションボタン:btnAdd
オブジェクト名には、コントロールの種類を示した接頭語をそれぞれ付けています。リストボックスは食品の一覧を表示するために使用します。またオプションボタンはリストボックスに表示された食品をシートに追加するために使用します。

 

リストボックスに食品を追加しよう

では、リストボックスに食品を追加していきましょう。以下の手順で行ってください。

フォーム起動時の処理を実装しよう

リストボックスに食品を追加するための作業は、ユーザーフォームが開かれた際に、自動で実行されるようにしていきます。まず、ユーザーフォーム上で、ユーザーフォームをダブルクリックしてください。コードエディタが開き、自動で以下のようなコードが作成されますが、それは削除してください。

エディタの上の方に、UserFormなど書かれたウインドウがあるかと思います。

同じ様なウインドウが右側にもありますよね。これを、
・左側:UserForm
・右側:Initialize
に変更してください。すると、以下のようなコードが表示されます。これは削除せずに置いておいてください。

作成されたコードの中に、処理を記述していきます。UserForm_Initializeは、ユーザーフォームが開かれた際に自動で実行されるプロシージャです。したがって、このプロシージャ内に、リストボックスに食品を追加するためのコードを記述すれば良いということになります。以下のようになればOKです。

いきなりコードを示しましたが、何のことかわかりませんよね。解説していきます。

コードの解説

まず2行目は、繰り返し処理を行う際に使用する変数を定義しています。

3行目からFor~Nextステートメントによる繰り返し処理が行われます。開始値が9で終値が2199ですが、これはそれぞれ、本表における食品番号が実際に入力されている行の初めと終わりを示しています。

そして4行目で、リストボックスに食品を追加しています。リストボックス.AddItem()で、引数をリストボックスに追加できます。引数の中身は、3つの文字列を&でつなぎ合わせた形になっています。

1つ目の文字列は食品番号を示しています。コードは Format(Worksheets("本表").Cells(i, 2).Value, "00000") と記述しています。本表シートの2列目には食品番号が入力されており、それを取得している形です。ただ、この列に入力されている食品番号は4桁だったり5桁だったりします。これは、食品番号の先頭の0が省略されたためです。これでは、リストボックスから食品番号を抽出する際などに不都合があります。そのため、Format関数を用いて、すべての食品番号を5桁にそろえてあります。1つ目の引数に対象の文字列、2つ目の引数に書式を入力することで、それに沿った文字列を返します。

2つ目の文字列は半角スペースです。食品番号の後に半角スペースを付与しています。

3つ目の文字列は食品名を示しています。コードは Worksheets("本表").Cells(i, 4).Value と記述しています。本表シートの4列目に食品名が入力されており、それを取得している形です。

つまり文字列は、”食品番号 食品名”の形になるのですね。

実際にやってみましょう。栄養計算シートに設置したボタンからフォームを開いてみてください。以下のような画面になれば成功です。

 

ボタンを押して食品を入力できるようにしょう

では、実際に「追加する」ボタンを押すと、食品を入力できるようにしていきましょう。以下の手順で行ってください。

ボタンを押した際の処理を書くためには?

フォーム開始時の処理を書いた時と同じ様に、ボタンを押した時の処理も書いていきます。ボタンをダブルクリックし、ボタンを押した際に実行されるコードを表示してください。以下のように、btnAdd_Click()が表示されればOKです。

このプロシージャ内に、コードを書いていきます。以下を記述してください。

コードの解説

では、コードの解説をしていきます。

2行目のfoodNumは、食品番号を格納するための変数です。文字列型で宣言しています。

3行目で、実際にfoodNumに食品番号を格納しています。Left関数は置いておいて、まずはその中身をみてみましょう。lstFood.valueとなっていますね。ここでは、リストボックスで、選択されている値を取得しています。先ほど、リストボックスに追加した文字列を、ここで取得しているのですね。

この文字列は、左5文字が食品番号なのでした。そこで、Left関数を用いて、左から5文字を取得しています。Left関数は、1つ目の引数が対象の文字列、2つ目の引数が取得する文字の数で、取得した文字列を返します。

つまり、変数foodNumには、リストボックスで選択された食品の、食品番号が格納されているという状態になっています。

これを、Cells(ActiveCell.Row, 2) の value として設定します。ActiveCell.Row で現在選択した状態になっているセルの行を取得できますので、それを行インデックスとして設定しています。2列目というのは、栄養計算シートにおける食品番号を入力するための列番号のことです。

これで、リストボックスで選択されている食品を、ボタンをクリックすることでシート上に入力することができました。

実際にフォームを起動してみて、狙ったとおりの動作をするか、確かめてみましょう。

 

まとめ

今回は、リストボックスに食品を追加し、それをシート上に入力する処理を実装しました。次回は、食品群毎にリストボックスに追加するような処理を行っていきたいと思います。

 

連載目次

  1. ユーザーフォームを始める最初の1歩
  2. フォームを設置→開くをやってみよう!
  3. リストボックスからシートに食品を追加してみよう!
  4. オプションボタンから動的にリストボックスの内容を変更しよう!
  5. ユーザーフォームにエラー処理を実装しよう!
  6. コードを読みやすいリーダブルコードに改善しよう!
  7. 食品成分表から検索→食品入力をしてみよう!
  8. 栄養計算ソフト+ユーザーフォームの完成!DLも可