【販売管理システム作成】見積データ登録用のフォームを作ろう②【コンボボックスの活用】

販売管理システム【見積編】

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

販売管理システム作成をシリーズでお伝えしています。

前回は、見積データの新規登録用フォームを作り初期値を設定しました。

前回の復習

【販売管理システム作成】見積データ登録用のフォームを作ろう①【フォームの設定】

みなさん、こんにちはケンケンです。販売管理システムをシリーズで作成しています。今回から編集機能を作る予定でしたが、その前段階として新規登録機能を作ります。編集機能を作る前に見積データを登録できる機能を作っちゃ[…]

今回は、フォームに設置した各コントロールにイベントを追加してカスタマイズしていきます。

1 前回の復習
- 1.1 初期値設定項目
2 得意先と商品Noを選択した時の処理
- 2.1 得意先コンボボックスを選択した時の処理
- 2.2 商品Noを選択した時の処理
3 数量を決定した時の処理
- 3.1 コード解説
4 まとめ

前回の復習

前回は、新規登録用フォームを作成し、そのフォームの初期値を設定しました。

フォームと設定した初期値のコードを以下に表示しておきます。

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" &amp; Rows.Count).End(xlUp).Row
    txtMitumoriNo.Text = Format(Sheets("見積データ一覧").Range("A" &amp; Lrow).Value + 1, "00000")
    txtMhiduke.Text = Format(Now, "yyyy/mm/dd")
    
    Dim cnt '得意先コンボボックス初期値
    Dim TLrow
    TLrow = Sheets("得意先一覧").Range("A" &amp; Rows.Count).End(xlUp).Row
    With cboTokuisaki
        .ColumnCount = 2
        .ColumnWidths = "30;150"
        .ListWidth = 180
        
        For cnt = 2 To TLrow
            .AddItem Sheets("得意先一覧").Range("A" &amp; cnt).Value
            .List(.ListCount - 1, 1) = Sheets("得意先一覧").Range("B" &amp; cnt).Value
        Next
    End With
    
    TLrow = Sheets("商品一覧").Range("A" &amp; 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" &amp; cnt).Value
            .List(.ListCount - 1, 1) = Sheets("商品一覧").Range("B" &amp; cnt).Value
            .List(.ListCount - 1, 2) = Sheets("商品一覧").Range("C" &amp; cnt).Value
            .List(.ListCount - 1, 3) = Sheets("商品一覧").Range("D" &amp; 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

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

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

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 = &amp;H80000005
End Sub

Private Sub cboTokuisaki_Change()

txtTokuisaki.Text = cboTokuisaki.List(cboTokuisaki.ListIndex, 1)

txtTokuisaki.BackColor = &H80000005

End Sub

１行目で得意先コンボボックスで選択されたデータの２列目、つまり得意先名を隣のテキストボックス（txtTokuisaki.Text）に転記しています。

コンボボックスの場所を特定する際は、０から始まることを理解しておいて下さい。

よって、Listの列番号は１となっていますが、１は０から数えて２番目なので２列目の得意先名が転記されます。

コンボボックスの構造はリストボックスのそれと酷似しています。

以下の記事でリストボックスの構造を詳しく解説していますのでよかったらそちらを参考にしてください。

リストボックス参考記事

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

みなさん、こんにちはケンケンです。今回は、ユーザーフォームのリストボックスをご紹介します。リストボックスはフォーム上にデータリストを表示したり選択することが出来ます。資料は以前[…]

２行目でテキストボックスの色を白にしています。

前回プロパティの設定でグレーにしていたものです。

このアクションを追加することでユーザーに得意先の選択は滞りなく進んでいる、という認識を与えることができますね。

それでは、フォームを実行し得意先コンボボックスを選択した時のアクションを確認しておきましょう。

下図のようになっていれば成功です。

商品Noを選択した時の処理

続いて商品Noを選択した時の処理を追加します。

方法は得意先コンボボックスの時と同じです。まずは、コードを書いてしまいましょう。

VBEから商品Noコンボボックスをダブルクリックし、Changeイベントプロシージャを生成します。

Dim tanni
Private Sub cboSyouhin_Change()
    txtSyouhin.Text = cboSyouhin.List(cboSyouhin.ListIndex, 1)
    txtSyouhin.BackColor = &amp;H80000005
    txtTanka.Text = Format(cboSyouhin.List(cboSyouhin.ListIndex, 2), "#,###")
    txtTanka.BackColor = &amp;H80000005
    tanni = cboSyouhin.List(cboSyouhin.ListIndex, 3)
End Sub

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へ格納

して、それぞれテキストボックスの色を変更しています。

やっていることは簡単ですが、ポイントが２点あります。

１点目は、コンボボックスで表示されていない単価や単位データを引っ張り出せていること

２点目は、変数tanniをプロシージャの上で宣言していること

が挙げられます。

１点目を解説すると前回初期値を設定する際に、コンボボックスのリストのうち２列を幅０に設定していました。

これが今回生きてきます。コンボボックス上では幅０の部分は見えていませんがデータとしては格納されているわけです。

当然、見えていなくても格納されているわけですから、データをテキストボックスに書き出すことは可能ですね。

なぜ、あえて見せなくするかと言うと、これもユーザーへの配慮です。

商品名を選ぶ画面で単価や単位はそれほど関係ないから余計なものは見えないようにしておくのです。

選択画面で情報が多いとユーザーはとまどってしまうので、細かいですがこのテクニックは有効です。

続いて２点目の説明をしますと、今回のフォームではあえて単位を表示するテキストボックスは作りませんでした。

しかし、最終的にエクセルシートにはその情報を転記したいのでデータとして単位を取得しておく必要があります。

そこで、変数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 = &amp;H80000005'⑥
    txtTax.BackColor = &amp;H80000005'⑦
    txtZeikomi.BackColor = &amp;H80000005'⑧
End Sub