Excelマクロ(VBA)で作る管理システム③

Excel
スポンサーリンク

今回はExcelマクロを使った仕入管理システムについての3回目です。

ユーザーフォームを使って仕入データをバーコードを条件に検索する方法を2回目でやりましたが、今回はリストボックスからデータを選択して、数量を入力して自動計算する方法を解説します。
コード自体は顧客管理、売上管理、在庫管理への転用など汎用性の高いものになっておりますので、ご自身が業務等に使えそうな部分だけでも是非参考にしていただけたらと思います。
(画面設定やブックの保護・解除などシステムとして動作制限をかけるコードについては省略しています。)
VBAコード全体は基本的なものが中心ですので、ご安心ください。それではまいります。

Excelマクロ(VBA)で作る管理システム①
マクロ(VBA)で仕入管理システムを作成する方法を紹介しています。使用しているコードやユーザーフォームは仕入だけではなく在庫管理や売上管理、顧客管理など汎用性の高いコードです。是非ご自身の業務に活用してください。
Excelマクロ(VBA)で作る管理システム②
マクロ(VBA)で仕入管理システムを作成する方法の2回目です。使用しているコードやユーザーフォームは仕入だけではなく在庫管理や売上管理、顧客管理など汎用性の高いコードです。是非ご自身の業務に活用してください。
スポンサーリンク

概要

完成図①
完成図②

シートは3つあります。

  1. 「商品マスタ」シート:商品データ(バーコード、商品名、取引先、仕入単価)を登録しておくシートです。
  2. 「DB」シート:仕入が発生した時に入力しておくシートです。マスタシートからデータ参照して日付、バーコード、取引先名、数量、単価を入力します。バーコードからマスタデータを検索可能です。 ※今回はここの部分の説明のつづきです。
  3. 「仕入帳」シート:月次仕入帳を作成します。DBシートで入力したデータから月次仕入帳を作成します。

フォームコントロール(ふりかえり)

仕入商品入力フォーム

前回までは、⑦「検索」ボタンをクリックすると③バーコードテキストボックスに入れた数字から商品を検索・抽出して、⑥リストボックスに表示するというところまでをやりました。

検索ボタンの機能

今回の大まかな流れ

  • リストボックスの商品データを選択する
  • ④数量テキストボックスに数値を入力すると、⑤金額テキストボックスに計算結果が表示される
  • ⑨仕入入力ボタンをクリックしてDBシートの仕入データベースに転記させる

コード(仕入金額の計算)


Sub 金額合計()
    Dim 数量 As Long
    Dim 単価 As Long
    On Error GoTo label01      
  '解説① If IsNumeric(ListBox1.List(ListBox1.ListIndex, 2)) = True Then '数量が数値データならば
        数量 = txt数量.Text
        単価 = ListBox1.List(ListBox1.ListIndex, 2)
        txt金額.Text = Format(数量 * 単価, "#,###") '金額欄に数量×単価を入力
    Else
        txt金額 = ""
    End If
  On Error GoTo 0
label01:
    ListBox1.SetFocus
End Sub

解説①

まず、こちらのコードは「リストボックスの選択した部分が数値データならば」というIf関数による条件分岐です。

ListIndexプロパティは、リストボックスで選択しているリストの行位置を取得するプロパティです。

仕入商品入力フォーム

上図の例では、青塗部分が選択されている部分ですが、ListBox1.List(ListBox1.ListIndex, 2)でListBox1.List(4,2)となり、「260」が取得できます。

リストボックスは配列形式で管理されているとは前回説明しました。

IsNumeric関数・・数値と認識できる場合はTrueを返します。引数が単価の数値の場合は

txt金額.Text = Format(数量 * 単価, “#,###”)

となり、⑤金額テキストボックスに「数量×単価」の計算結果が表示されます。

行タイトルを選択した場合は文字列データのためFalseとなり、txt金額 = “”⑤金額テキストボックスは空欄になります。

エラー処理

後述するリストボックスをクリックせず、仕入入力ボタンを押してしまった場合など、エラーが発生するので、On Error GoTo label01で対処しています。

コード(⑥リストボックスのデータをクリックした場合)


Private Sub ListBox1_Click() 'リストボックスをクリックすると
    On Error GoTo label02
    Call 金額合計 '金額合計プロシージャへ
label02:
    txt数量.SetFocus '数量が入ってなければ数量欄へ
End Sub

リストボックス内のデータをクリックした場合、先述の金額合計プロシージャが呼び出されて⑤金額テキストボックス計算結果が表示されます。

しかし、数量テキストボックスに入力していない場合、エラーになるので、エラー処理としてOn Error GoTo label02で④数値テキストボックスが選択されるようにしています。

⑨仕入入力ボタン

Private Sub cmb仕入入力_Click()
    Dim 最終行 As Long
'解説①
  If IsDate(txt日付.Text) = False Then
        MsgBox "日付を入力してください"
        txt日付.SetFocus
        Exit Sub
    End If
'解説②   
    If IsNumeric(txt数量.text) = False Or IsNumeric(txt金額.text) = False Then '入力欄が数値データでなければ(数値以外入力不可)
        MsgBox "入力欄に空欄箇所があります"
        txtバーコード.SetFocus
        Exit Sub
    End If
       
    最終行 = ThisWorkbook.Sheets("DB").Cells(Rows.Count, 2).End(xlUp).Row + 1
 '解説③   
    With ThisWorkbook.Sheets("DB")
        .Range("B100000").Select
        .Range("B" & 最終行).Select
        .Cells(最終行, 2).Value = Format(txt日付.Text, "yyyy/mm/dd") '日付
        .Cells(最終行, 3).Value = ListBox1.List(ListBox1.ListIndex, 0) 'バーコード
        .Cells(最終行, 4).Value = ListBox1.List(ListBox1.ListIndex, 3) '取引先名
        .Cells(最終行, 5).Value = ListBox1.List(ListBox1.ListIndex, 1) '商品名
        .Cells(最終行, 6).Value = ListBox1.List(ListBox1.ListIndex, 2) '単価
        .Cells(最終行, 7).Value = txt数量.Text '数量
        .Cells(最終行, 8).Value = txt金額.Text '金額
        
    txtバーコード.Text = ""
    txtバーコード.SetFocus

    End With
End Sub

解説①②

日付テキストボックス、数量、金額テキストボックスが指定の形式でない場合に、処理が終了するコードです。

解説③

こちらについては「DB」シートの最終行にユーザーフォームに表示されているデータを転記するコードです。

仕入入力

.Range(“B100000”).Select                               .Range(“B” & 最終行).Select

入力時にPC画面上に入力したデータが表示されるように画面スクロールを調整するためのコードです。

txtバーコード.Text = “”                                   txtバーコード.SetFocus

次のバーコードを入力しやすいように、データ転記後は再びバーコードテキストボックスがセレクトされるようにしています。

まとめ

今回は、選択した商品データの金額の自動計算とシートへの転記を行いました。

次回以降、ユーザーがもっと使いやすくなるような項目の移動設定、そして月次仕入帳作成を行っていきます。

お楽しみに!!

Excelマクロ(VBA)で作る管理システム③-1
ユーザーフォームのボタンの移動について解説しています。
Excelマクロ(VBA)で作る管理システム④
今回は、データベースから指定の年月で抽出して、月次の仕入元帳を作成するコードとプロシージャについて解説します。

コメント

タイトルとURLをコピーしました