みなさん、こんにちはケンケンです。
販売管理システム作成をシリーズでお伝えしています。
前回は、見積データの新規登録用フォームを作り初期値を設定しました。
みなさん、こんにちはケンケンです。 販売管理システムをシリーズで作成しています。 今回から編集機能を作る予定でしたが、その前段階として新規登録機能を作ります。 編集機能を作る前に見積データを登録できる機能を作っちゃ[…]
今回は、フォームに設置した各コントロールにイベントを追加してカスタマイズしていきます。
前回の復習
前回は、新規登録用フォームを作成し、そのフォームの初期値を設定しました。
フォームと設定した初期値のコードを以下に表示しておきます。
Private Sub UserForm_Initialize() lblHiduke.Caption = Format(Now, "yyyy/mm/dd hh:mm") Worksheets("見積データ一覧").Range("A1:M1").AutoFilter ActiveWorkbook.Worksheets("見積データ一覧").AutoFilter.Sort.SortFields.Clear ActiveWorkbook.Worksheets("見積データ一覧").AutoFilter.Sort.SortFields.Add Key:= _ Range("A1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _ xlSortNormal With ActiveWorkbook.Worksheets("見積データ一覧").AutoFilter.Sort .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With Sheets("見積データ一覧").Range("A1:M1").AutoFilter Dim Lrow 'frmMitumoriCreatの新規見積No Lrow = Sheets("見積データ一覧").Range("A" & Rows.Count).End(xlUp).Row txtMitumoriNo.Text = Format(Sheets("見積データ一覧").Range("A" & Lrow).Value + 1, "00000") txtMhiduke.Text = Format(Now, "yyyy/mm/dd") Dim cnt '得意先コンボボックス初期値 Dim TLrow TLrow = Sheets("得意先一覧").Range("A" & Rows.Count).End(xlUp).Row With cboTokuisaki .ColumnCount = 2 .ColumnWidths = "30;150" .ListWidth = 180 For cnt = 2 To TLrow .AddItem Sheets("得意先一覧").Range("A" & cnt).Value .List(.ListCount - 1, 1) = Sheets("得意先一覧").Range("B" & cnt).Value Next End With TLrow = Sheets("商品一覧").Range("A" & Rows.Count).End(xlUp).Row '商品コンボボックス初期値 With cboSyouhin .ColumnCount = 2 .ColumnWidths = "30;150;0;0" .ListWidth = 180 For cnt = 2 To TLrow .AddItem Sheets("商品一覧").Range("A" & cnt).Value .List(.ListCount - 1, 1) = Sheets("商品一覧").Range("B" & cnt).Value .List(.ListCount - 1, 2) = Sheets("商品一覧").Range("C" & cnt).Value .List(.ListCount - 1, 3) = Sheets("商品一覧").Range("D" & cnt).Value Next End With With lstMeisai 'リストボックスの初期値 .ColumnCount = 10 .ColumnWidths = "0;0;0;60;36;40;60;60;60;60" .TextAlign = fmTextAlignCenter End With 'コントロール設定 cboTokuisaki.Style = fmStyleDropDownList cboSyouhin.Style = fmStyleDropDownList cmbTuika.Enabled = False cmbSyusei.Enabled = False cmbSakujo.Enabled = False cmbTouroku.Enabled = False End Sub
初期値設定項目
- フォームの右上に現在時刻と日付を表示する
- 見積データ一覧シートを見積No項目を昇順でソートをかける
- フォームの見積Noテキストボックス(txtMitumoriNo)内に新規Noを自動表示させる
- 見積日テキストボックス(txtMhiduke)に現在日付を自動表示させる
- 得意先コンボボックス(cboTokuisaki)に得意先一覧データを流し込む
- 商品Noコンボボックス(cboSyouhin)に商品情報を流し込む
- リストボックス(lstMeisai)の初期値を設定する
得意先と商品Noを選択した時の処理
ではまず、得意先コンボボックスと商品Noコンボボックスを選択した時に隣にあるテキストボックスにデータを表示していきましょう。
Changeイベントを使うと良いでしょう。
具体的なアクションは下図をご覧ください。
得意先コンボボックスを選択した時の処理
得意先コンボボックスから処理していきます。
いつものようにVBEを起動させ、フォーム画面から得意先コンボボックスをダブルクリックしましょう。
生成されたChangeイベントプロシージャにコードを書きましょう。
Private Sub cboTokuisaki_Change() txtTokuisaki.Text = cboTokuisaki.List(cboTokuisaki.ListIndex, 1) txtTokuisaki.BackColor = &H80000005 End Sub
1行目で得意先コンボボックスで選択されたデータの2列目、つまり得意先名を隣のテキストボックス(txtTokuisaki.Text)に転記しています。
コンボボックスの場所を特定する際は、0から始まることを理解しておいて下さい。
よって、Listの列番号は1となっていますが、1は0から数えて2番目なので2列目の得意先名が転記されます。
コンボボックスの構造はリストボックスのそれと酷似しています。
以下の記事でリストボックスの構造を詳しく解説していますのでよかったらそちらを参考にしてください。
みなさん、こんにちはケンケンです。 今回は、ユーザーフォームのリストボックスをご紹介します。 リストボックスはフォーム上にデータリストを表示したり選択することが出来ます。 資料は以前[…]
2行目でテキストボックスの色を白にしています。
前回プロパティの設定でグレーにしていたものです。
このアクションを追加することでユーザーに得意先の選択は滞りなく進んでいる、という認識を与えることができますね。
それでは、フォームを実行し得意先コンボボックスを選択した時のアクションを確認しておきましょう。
下図のようになっていれば成功です。
商品Noを選択した時の処理
続いて商品Noを選択した時の処理を追加します。
方法は得意先コンボボックスの時と同じです。まずは、コードを書いてしまいましょう。
VBEから商品Noコンボボックスをダブルクリックし、Changeイベントプロシージャを生成します。
Dim tanni Private Sub cboSyouhin_Change() txtSyouhin.Text = cboSyouhin.List(cboSyouhin.ListIndex, 1) txtSyouhin.BackColor = &H80000005 txtTanka.Text = Format(cboSyouhin.List(cboSyouhin.ListIndex, 2), "#,###") txtTanka.BackColor = &H80000005 tanni = cboSyouhin.List(cboSyouhin.ListIndex, 3) End Sub
コードの内容は特に問題なく理解できると思います。
簡単に説明すると商品Noコンボボックスに格納されているデータの内、
- 商品名をtxtSyouhinテキストボックスへ転記
- 単価をtxtTankaテキストボックスへ転記
- 単位を変数tanniへ格納
して、それぞれテキストボックスの色を変更しています。
やっていることは簡単ですが、ポイントが2点あります。
1点目は、コンボボックスで表示されていない単価や単位データを引っ張り出せていること
2点目は、変数tanniをプロシージャの上で宣言していること
が挙げられます。
1点目を解説すると前回初期値を設定する際に、コンボボックスのリストのうち2列を幅0に設定していました。
これが今回生きてきます。コンボボックス上では幅0の部分は見えていませんがデータとしては格納されているわけです。
当然、見えていなくても格納されているわけですから、データをテキストボックスに書き出すことは可能ですね。
なぜ、あえて見せなくするかと言うと、これもユーザーへの配慮です。
商品名を選ぶ画面で単価や単位はそれほど関係ないから余計なものは見えないようにしておくのです。
選択画面で情報が多いとユーザーはとまどってしまうので、細かいですがこのテクニックは有効です。
続いて2点目の説明をしますと、今回のフォームではあえて単位を表示するテキストボックスは作りませんでした。
しかし、最終的にエクセルシートにはその情報を転記したいのでデータとして単位を取得しておく必要があります。
そこで、変数tanniを宣言し、単位情報を格納しておきます。
ここで疑問が湧いてくると思います。
単位情報を変数に格納するのはいいけど、変数はどこで使うの?ちがうプロシージャで使うことはできるの?
変数を使う場所は、見積データ一覧シートに転記する際に使うプロシージャ内です。(まだ作っていません)
そうなるとプロシージャを越えて変数を使うことになりますね。それを可能にするためには、変数を宣言セクションで宣言しなくてはなりません。
宣言セクションはすべてのプロシージャの上あたりのことを指します。
このように宣言された変数をモジュールレベル変数と言います(下図参照)
変数についても解決したところで、フォームを起動してアクションを確認しましょう。
数量を決定した時の処理
商品名と単価が決定した後、数量を入力すると金額が自動計算される仕組みを作ります。
AfterUpdateイベントプロシージャを作成します。
初めて出たイベントですが、読んで字のごとくUpdateされた後に起こるアクションを設定できるプロシージャです。
つまり、数量を入力した後に続くアクションを設定するということです。
プロシージャの生成は以下のようにして、コードも書きましょう。
Private Sub txtSuryou_AfterUpdate() If txtSyouhin.Text = "" Then'① Exit Sub End If txtKingaku.Text = Format(txtSuryou.Text * txtTanka, "#,###")'② If txtKingaku.Text = "" Then'③ txtTax.Text = "" txtZeikomi.Text = "" Exit Sub End If txtTax.Text = Format(txtKingaku.Text * 0.08, "#,###")'④ txtZeikomi.Text = Format(txtKingaku.Text * 1.08, "#,###")'⑤ txtKingaku.BackColor = &H80000005'⑥ txtTax.BackColor = &H80000005'⑦ txtZeikomi.BackColor = &H80000005'⑧ End Sub
コード解説
①商品名(txtSyouhin)が空欄だった場合はプロシージャを終了します。
以降の計算ができないので当然するべき対応ですね。
②数量(txtSuryou)×単価(txtTanka)の計算をし表示形式をFormat関数で整えて、金額欄(txtKingaku)に転記しています。
③金額欄が空欄だった場合に、消費税(txtTax)と税込金額(txtZeikomi)も空欄にしてプロシージャから離脱します。
エラーを起こさないように先手を打っておきます。
④金額に税率(今回は8%)をかけた数値を消費税欄(txtTax)に転記します。
⑤金額に消費税を加味した金額(今回は108%)を税込金額(txtZeikomi)に転記します。
⑥~⑧それぞれのテキストボックス(txtKingaku、txtTax、txtZeikomi)の色を白に戻します。
それでは、フォームを表示してみましょう。
まとめ
いかがでしたか。
今回は、見積データ新規登録フォームを少し作り込みました。
コンボボックスとテキストボックスをうまく使って表示する方法はテクニックとして覚えておいてください。
次回はリストボックスにデータを表示させ、そのデータをエクセルシートに転記する方法をご紹介しますのでお楽しみに。
それでは、また次回お会いしましょう。
販売管理システム作成記事一覧~見積編~
エクセルを駆使して販売管理システムを作ってみる【元データの取り扱い方】
【販売管理システム作成】見積書を期間指定して作成する方法①【コードのカスタマイズ】
【販売管理システム作成】見積書を期間指定して作成する方法②【リストボックス作成】
【販売管理システム作成】見積書を期間指定して作成する方法③【リストボックス作成その2】
【販売管理システム作成】見積データ登録用のフォームを作ろう①【フォームの設定】
【販売管理システム作成】見積データ登録用のフォームを作ろう②【コンボボックスの活用】
【販売管理システム作成】見積データ登録用のフォームを作ろう③【リストボックスの活用】
【販売管理システム作成】見積データ登録用のフォームを作ろう④【エクセルシートに登録】