【ExcelVBA】ユーザーフォームを作りこむ【コンボボックスの使い方】

みなさん、こんにちはケンケンです。

 

今回も前回に引き続き、ユーザーフォームの活用についてご紹介します。

ユーザーフォームの復習をしたい方は以下をご覧ください。

【ExcelVBA】ユーザーフォーム活用①

【ExcelVBA】ユーザーフォーム活用テクニック編【チェックボックス】

【ExcelVBA】ユーザーフォーム活用テクニック編【リストボックス】

【ExcelVBA】ユーザーフォーム活用テクニック編【スクロールバーについて】

【ExcelVBA】ユーザーフォーム活用【リストボックスから選択・メッセージボックス活用】

 

今回は、コンボボックスの使い方を中心に見ていきます。

コンボボックスの他にもフォームの作り方も合わせてご紹介していきますので参考にしてください

 

フォームを作ろう

まずは、コンボボックスの解説をする前にフォームを作っていきます。

その前に今回作るフォームの完成形を確認しましょう。

 

完成形を確認しよう

以下が今回作成するフォームの完成形です。

 

フォームに必要事項(取引日から数量)を入力して合計計算ボタンをクリックすると、下欄に合計と消費税と税込金額が自動計算され、最後に明細追加ボタンをクリックすると、エクセルシートにデータが転記される仕組みです。

 

仕組みとエクセルシート構成

仕組みは以下のようになります。

  1. 取引日を数字で入力
  2. 会社IDと商品IDをコンボボックスから選択
  3. 商品の数量を入力
  4. 合計計算ボタンクリックすると太線以下の合計・消費税・税込金額が自動計算される
  5. 明細追加ボタンをクリックするとデータがエクセルシートに転記される

 

この仕組みを実現するためにエクセルシートの構成は以下の通りです。

 

  • 「フォーム起動」にはフォームを表示させるボタンを置いておきます。

  • 「商品一覧」には以下のように商品ID・商品名・単価のリストを作成します。

  • 「会社一覧」には会社ID・会社名のリストを作成します。

  • 「取引実績」にはフォームで入力したデータを転記します。

  • 「消費税」には税率を明記しておきます。

 

フォームを作ろう

それでは、フォームを作っていきましょう。

エクセルシート上で「F11」でVBEを起動させ、「挿入」から「ユーザーフォーム」を選択します。

 

続いてフォームに部品を以下のように配置していきます。

会社IDと商品IDはコンボボックスをツールボックスから選択します。

 

配置できたらそれぞれのオブジェクト名を変更します。すぐに変更する癖をつけましょう。

 

 

オブジェクト名を変更するときは以下のようにプロパティウィンドウから変更しましょう。

 

コンボボックスの基礎

ここからはコンボボックスの基礎事項をご紹介します。基礎事項とはいえ使えるテクニックが多いのでしっかり身に着けてください。

フォームの初期値を設定しよう

オブジェクト名を変更出来たらInitializeでコンボボックスの初期値を設定しましょう

フォームの余白部分をダブルクリックしてプロシージャ画面へ飛びます。

上記のようにUserFormからInitializeを選択してプロシージャを作成します。

 

Initializeプロシージャにコンボボックスの初期値を書きます。

コードの解説をします。

①会社IDのコンボボックスの初期値を設定します。以前の記事をご覧いただいた方にはわかるかと思いますが、リストボックスの初期値設定とほとんど同じです。

フォントサイズを12にするのは問題ないかと思います。

ColumnCountは2にします。会社一覧シートのデータが2列(ID・会社名)だからです。

ColumnWidthsでIDと会社名を表示する幅を設定しています。

ListWidthでコンボボックス全体の幅を設定しています。

 

②変数Lrowに会社一覧シートA列最終行を格納します。

 

③会社一覧シートの2行目から最終行(Lrow)まで繰り返す構文です。フォーム起動時にコンボボックスに表示するための構文です。

 

④AddItemでコンボボックスに新しい行を作ります

作った行のコンボボックス一列目に会社一覧シートのA列のデータ、つまりIDを転記します。

 

⑤会社一覧の2列目、つまり会社名をコンボボックスに表示されるコードです。

List(行番号、列番号)はリストボックスの時にやりましたね。

コンボボックスも同様に行と列は0から始まります。ですので行は、ListCountで取得した行数から-1することでコンボボックスの行数と合致させるのです。

列は、コンボボックスの2列目は―1をして「1」になります。

 

理解が不十分の方はリストボックスの回を復習しておいてください。

 

 

商品IDの初期値を設定しよう

続いて商品IDの初期値を設定します。Initializeプロシージャに追記します。

 

会社IDとほとんど同じですが、ひとつ特殊なテクニックを使っています。

①ColumnCountを3つ作って、ColumnWidthsの3つ目の値を0にしています。これが特殊なテクニックです。

ColumnWidthsを0に設定したデータは対象のコンボボックスに表示されません。

つまり、今回は3列目の値を非表示にする、という設定にしています。3列目には単価を入れます。

 

②コンボボックスの3列目に商品一覧シートの3列目のデータを反映させています。

①で説明したように3列目の情報(単価)は表示させない仕様にしたいのですが、データ自体は反映させる必要があります。

3列目に非表示で反映された意味は後で分かります。

 

コンボボックスを表示させよう

コードが書けたら表示されるか確認してみましょう。

▼をクリックすると会社一覧が表示されます。

 

上図のように表示されれば成功です。

 

フォーム起動ボタンを作っておこう

ここでフォーム起動ボタンを作っておきましょう。

標準モジュールにコードを書きます。

おなじみのフォームを起動するときのコードです。

 

続いて、フォーム起動シートにボタンを作ります。

「開発」タブから「挿入」をクリックし、ボタンを作ります。

 

ボタンの名称は好みでOKです。

 

ラベルを追加しよう

会社名と商品名をコンボボックスと連動してラベルに表示されるための仕掛けを作ります。

上図のようにCaptionなしでラベルを2つ配置してください。

オブジェクト名は「lblKaisya」と「lblSyouhin」とします。

 

そして、Initializeプロシージャにコードを追記します。

 

これで、フォームを起動したときに下図のように表記されます。

 

コンボボックスのChangeイベント

コンボボックスを選択すると先ほど作ったラベルが会社名に変わるように設定します。

Changeイベントを使って仕組みを作っていきましょう。

完成形は以下の通りです。

 

コードを書いてみよう

コンボボックス会社ID(cboKaisya)でChangeプロシージャを以下のように作ります。

 

できたプロシージャに以下のコードを書きます。

 

コードの解説をします。

①コンボボックスのListIndexプロパティは選択されなかったときは「ー1」を返します。

その性質を生かし、If文を使ってコンボボックスが選択されなかったときにExitSubでプロシージャを抜けます。

ListIndexは0からスタートすることを忘れないようにしましょう。

 

②cboKaisya.List(cboKaisya.ListIndex, 1)は選択したコンボボックスの行の2列目(会社名)をラベルに転記するコードです。

「会社名あああ」のように表示されます。

 

これで、コンボボックスが選択される度にラベルのCaptionが変わるようになります。

 

続いてコンボボックス商品ID(cboSyouhin)のアクションを作ります。

新たにChangeプロシージャを作ってコードを書きます。

コードの解説をします。

①②は先ほどと同じ考え方です。コンボボックスから商品IDを選択すると「商品名AAA」のようにラベルが切り替わります。

 

商品の単価をlblPriceラベルに転記するします

先ほど商品IDの初期値を設定した際にコンボボックスの3列目に非表示でデータを反映させていましたね。

それがここで生きます。

目には見えていませんが、3列目の単価をラベルに転記することができます。

フォームを作りこむ

ここからはフォームを作る上で使えるテクニックを紹介していきます。

 

オブジェクトの編集をロックしよう

フォームの特性上、ユーザーに触れてほしくなかったり変更されると困る項目があります。

非表示にすると利便性に欠けてしまい表示せざるを得ません。

そんな時に使えるのがオブジェクトをロックしてしまう方法です。

編集をロックする場合は、Lockedプロパティを使います。

 

今回はLockedプロパティを使って以下をロックしていきます。

  • 商品が選択されるまで「数量」を入力できないようにtxtKazuをロックする
  • 合計・消費税・税込金額は自動計算なのでロックする
  • 合計計算が終了するまで「明細追加」ボタンをクリックできないようにロックする

 

上記条件をInitializeイベントに追記します。

 

「オブジェクト名.Locked=True(False)」でロックができます。

Trueでロック、Falseでロック解除ができます。

テクニックとして使えますので覚えておきましょう。

 

ついでに数量(txtKazu)の色も変えておきます。

他と色が変わっているとユーザーは明示的に入力してはいけないと思うので効果的です。

 

条件クリアしたら数量(txtKazu)のロックを解除しよう

先ほどロックしたオブジェクトをある条件がクリアされたらロック解除できるようにしましょう。

cboSyouhin_Changeプロシージャに以下を追記してください。

コンボボックスで商品が選択される条件がクリアされたら①と②のコードが実行されます。

①色を変えておいたテキストボックスtxtKazuのBackColorを白(&H80000005)にします。

②テキストボックスtxtKazuのロックを解除し、入力できるようにします。

 

商品を選択する前と後で出来るアクションが変わることを確認してください。

 

 

変数のスコープを知ろう

突然ですが、変数はスコープがあります。スコープとは変数が使える範囲のことです。

例えばプロシージャ内にDimを付けて変数を宣言すると、そのプロシージャ内だけで使えます。

ところが、プロシージャの外で変数を宣言すると、モジュールの中すべてで使えます。

さらに、変数の前にPublicを付けて宣言すると、その変数はプロジェクト全体で使えます。この変数をグローバル変数と言います。

今回は、プロシージャの外で変数を宣言してモジュール内で使い回してみます。

 

変数に格納した値を使い回す

それでは、具体的に見ていきます。

上記赤枠部分を変数に格納して使い回していきます。

まず、変数の宣言する位置が変わります。宣言する位置は一番上のプロシージャの上で宣言します。

 

こうすることで、モジュール内で変数が共有され他のプロシージャでも使えるようになります。

 

それでは、コンボボックスのプロシージャにコードを追記していきます。

まず、Option Explicitと最初のプロシージャの間に3つ変数を宣言してください。

 

そして、cboKaisya_ChangeプロシージャとcboSyouhin_Changeプロシージャそれぞれにコードを追記します。

①②③はそれぞれ、会社名・商品名・単価を宣言した変数に格納しています。

この変数は後で他のプロシージャに使っていきます。

 

合計計算ボタンのアクションを作ろう

合計計算ボタンをクリックした際に起こるアクションを作ります。

下図のように、クリックしたら合計・消費税・税込金額を自動計算しテキストボックスに表示させます。

それでは、プロシージャを作成してコードを書いていきましょう。

 

 

①はまず、txtKazu(数量)が入力されていなかったら、確認メッセージを出してプロシージャを抜けます。

メッセージボックスの活用は以前に紹介していますのでそちらを参考にしてください。

第二引数にvbOKOnlyを設定することでユーザーは選択肢なく強制的にプロシージャを離脱します。

 

②先ほど作った変数tankaの出番です。コンボボックスで商品を選択した際に商品単価を変数tankaに格納しているので、その値にtxtKazu(数量)を掛けて変数goukeiに格納します。

 

③変数goukeiの値をテキストボックスtxtGoukeiに転記します。

 

④消費税を計算し、テキストボックスtxtZeiに転記します。変数goukeiに消費税率を掛けています。

Sheets(“消費税”).Range(“A2”).Valueとすることで、税率が変更されてもエクセルシートを変更すれば対応でき、直接コードをいじる必要がなくなり、メンテナンス性にも優れます。

 

⑤税込金額をテキストボックスtxtKomiに転記します。

 

⑥合計の計算が終了した段階で明細追加ボタン(cmbMeisai)のロックを解除します。Initializeでロックしていましたね。

 

フォームを起動して確認してみましょう。

 

明細をエクセルシートへ転記しよう

フォーム上で計算したデータをエクセルシートへ転記していきます。

完成形は以下のようになります。

それでは、プロシージャを作成し、コードを書いていきましょう。

 

コードの解説をします。

①明細を入力するかどうか確認のメッセージを出します。vbNoの場合は、プロシージャから離脱します。

 

②入力後、取引実績シートをアクティブにします。

 

③取引実績シートのA列最終行+1行目を変数Lrowに格納します。

 

④A列にIDを振ります。Lrow-1の値が入るようにします。

 

⑤⑨~⑫各テキストボックスの値をそれぞれ取引実績シートの各列に転記します。

 

⑥~⑧モジュール全体で使える変数に格納した値をそれぞれC~E列に転記します。

これで完成です。

実際に起動して動作を確認してください。

 

まとめ

いかがだったでしょうか。

今回は、コンボボックスを中心にお伝えしましたが、他の点も盛りだくさんだったと思います。

フォームを作るときは、他の項目とどのように関わってくるかを常に考えておかないとうまく動作しません。

また、動作してもユーザーにとって使いにくいものだと意味がありません。

 

今回作ったフォームも決して完璧なものではないので、工夫してカスタマイズしていかなくてはなりません。

例えば、閉じるボタンがありませんし、取引日は日付でない文字列でも入力できてしまいます。

このようなことに対しても工夫を凝らしていくことで、少しずつ良いものに進化させることができますのでみなさんも工夫してどんどんレベルアップしてください。

以上です。次回もお楽しみに!

 

ユーザーフォーム活用関連記事一覧

【ExcelVBA】ユーザーフォーム活用の基礎【業務改善の強力な武器になります】

【ExcelVBA】ユーザーフォーム活用テクニック編【チェックボックス】

【ExcelVBA】ユーザーフォーム活用テクニック編【イニシャライズとリストボックス】

【ExcelVBA】ユーザーフォーム活用テクニック編【スクロールバーについて】

【ExcelVBA】ユーザーフォーム活用【リストボックスから選択・メッセージボックス活用】

【ExcelVBA】ユーザーフォームを作りこむ【コンボボックスの使い方】