ous">

小さな在庫管理

在庫管理導入からシステム作成まで詳細解説!

商品出庫画面の出庫リスト作成 [VBA在庫管理システムVer01#20]

前回、商品出庫画面の商品検索・出庫入力を作成しました。

この記事では、商品出庫画面の出庫リストを作成していきます。


VBA在庫管理システムVer01

商品出庫の出庫リスト作成

入出庫抽出シートの確認

商品入庫画面で入庫リスト抽出用に作成した、データブック(在庫管理DATA.xlsx)の入出庫抽出用のシートを確認します。

出庫リストの抽出には、この入出庫抽出用シートを使用します。

検索条件は4項目、入力しておきます。
商品ID 入出庫日 入出庫区分 削除

抽出項目は6項目、入力しておきます。
入出庫ID 入出庫日 商品ID 商品名称 入出庫数 入出庫内訳


出庫リストの抽出には、エクセルのフィルターオプション機能を活用していきます。
VBAではエクセルの機能を使用する場合がありますが、エクセル上での動作状況を充分に理解する必要があります。

フィルターオプション機能をあまり使用したことの無い方は、下記の記事で解説していますので参考にしてみてください。

出庫予定リストの表示

当日以降の出庫予定リストを出庫リストボックスに表示させるプロシージャーをサブルーチンとして作成します。

Sub ShowOutputAllScheduleList()

        Application.ScreenUpdating = False

'作業ブックを開く
Workbooks.Open データ位置 & "在庫管理DATA.xlsx"

'オブジェクト変数の取得
Dim wsInOut As Worksheet, wsInOutExtract As Worksheet
Set wsInOut = Workbooks("在庫管理DATA.xlsx").Sheets("T_入出庫")
Set wsInOutExtract = Workbooks("在庫管理DATA.xlsx").Sheets("入出庫抽出")

'検索条件の入力
With wsInOutExtract
    .Activate
    .Cells(2, 2).Value = ">=" & Date
    .Cells(2, 3).Value = " 出庫"
    .Cells(2, 4).Value = 0
End With

'フィルターオプションの実行
wsInOut.Columns("A:H").AdvancedFilter Action:=xlFilterCopy, _
 CriteriaRange:=wsInOutExtract.Range("B1:D2"), _
 CopyToRange:=wsInOutExtract.Range("G1:L1")

'抽出結果の最終行取得
Dim wsInOutExtractRow As Long
wsInOutExtractRow = wsInOutExtract.Cells(Rows.Count, 7).End(xlUp).Row

If wsInOutExtractRow > 1 Then

    '入出庫日を昇順で並べ替え
    wsInOutExtract.Range("G1").Sort key1:=wsInOutExtract.Range("H1"), order1:=xlAscending, Header:=xlYes
    
    '配列変数への書込み
    Dim aryOutputDetail() As Variant
    Dim i As Long
    
    ReDim aryOutputDetail(wsInOutExtractRow - 2, 5) As Variant
    For i = 2 To wsInOutExtractRow
        aryOutputDetail(i - 2, 0) = wsInOutExtract.Cells(i, 7)
        aryOutputDetail(i - 2, 1) = Format(wsInOutExtract.Cells(i, 8), "yy/mm/dd")
        aryOutputDetail(i - 2, 2) = wsInOutExtract.Cells(i, 9)
        aryOutputDetail(i - 2, 3) = wsInOutExtract.Cells(i, 10)
        aryOutputDetail(i - 2, 4) = wsInOutExtract.Cells(i, 11)
        aryOutputDetail(i - 2, 5) = wsInOutExtract.Cells(i, 12)
    Next
        
    '出庫リストの表示
    With lstOutput
        .Clear
        .ColumnCount = 6
        .ColumnWidths = "0;100;80;380;100;90"
    
        .List = aryOutputDetail()
    End With

ElseIf wsInOutExtractRow = 1 Then               '出庫予定無し時の回避
    
    '出庫リストの表示
    lstOutput.Clear

End If

'作業ブックを閉じる
Workbooks("在庫管理DATA.xlsx").Close savechanges:=False

        Application.ScreenUpdating = True

'オプションボタン設定
optOutputAllScheduleList.Value = True

End Sub

@1. 作業ブックを開く
エクセルの描画を止め、データブックを開き、作業シートを変数に格納します。

@2. フィルターオプションの設定
検索条件を書き込みます。入出庫日は今日以降とし、出庫予定表を抽出します。また、入出庫区分は" 出庫"(出庫の前に、全角のスペースを入れてください)とします。

@3. フィルターオプションの実行
フィルターオプションを実行し、取り込み用に抽出結果の最終行を取得します。取得列はG列となります。
また、検索条件範囲は"B1:D2"となるので、注意してください。

@4. 出庫リストへの表示
抽出結果のリストが有るときは、抽出結果を変数に取り込み、リストへ表示します。
抽出結果のリストが無いときは、リストはクリアして表示なしとします。

@5. 作業ブックを閉じる
作業ブックは保存しないで閉じます。

@6. オプションボタンの設定
出庫予定リストのオプションボタンを選択状態にします。
 

商品別リストの表示

選択された商品の棚卸日以降の出庫リストを出庫リストボックスに表示させるプロシージャーをサブルーチンとして作成します。

Sub ShowOutputItemScheduleList()

'異常値の回避
If cmbItemID.Text = "" Then
    optOutputAllScheduleList.Value = True
    Exit Sub
End If

        Application.ScreenUpdating = False

'作業ブックを開く
Workbooks.Open データ位置 & "在庫管理DATA.xlsx"
    
'オブジェクト変数の取得
Dim wsItemMaster As Worksheet, wsInOut As Worksheet, wsInOutExtract As Worksheet
Set wsItemMaster = Workbooks("在庫管理DATA.xlsx").Sheets("M_商品")
Set wsInOut = Workbooks("在庫管理DATA.xlsx").Sheets("T_入出庫")
Set wsInOutExtract = Workbooks("在庫管理DATA.xlsx").Sheets("入出庫抽出")

'棚卸し情報取得
Dim 検索行 As Long
検索行 = wsItemMaster.Range("A:A").Find(cmbItemID.Text, lookat:=xlWhole).Row
Dim 棚卸日 As Date
棚卸日 = wsItemMaster.Cells(検索行, 11).Value

'検索条件の入力
With wsInOutExtract
    .Activate
    .Cells(2, 1).Value = cmbItemID.Text
    .Cells(2, 2).Value = ">" & 棚卸日
    .Cells(2, 3).Value = " 出庫"
    .Cells(2, 4).Value = 0
End With

'フィルターオプションの実行
wsInOut.Columns("A:H").AdvancedFilter Action:=xlFilterCopy, _
 CriteriaRange:=wsInOutExtract.Range("A1:D2"), _
 CopyToRange:=wsInOutExtract.Range("G1:L1")

'抽出結果の最終行取得
Dim wsInOutExtractRow As Long
wsInOutExtractRow = wsInOutExtract.Cells(Rows.Count, 7).End(xlUp).Row

If wsInOutExtractRow > 1 Then

    '入出庫日を昇順で並べ替え
    wsInOutExtract.Range("G1").Sort key1:=wsInOutExtract.Range("H1"), order1:=xlAscending, Header:=xlYes
    
    '配列変数への書込み
    Dim aryOutputDetail() As Variant
    Dim i As Long
    
    ReDim aryOutputDetail(wsInOutExtractRow - 2, 5) As Variant
    For i = 2 To wsInOutExtractRow
        aryOutputDetail(i - 2, 0) = wsInOutExtract.Cells(i, 7)
        aryOutputDetail(i - 2, 1) = Format(wsInOutExtract.Cells(i, 8), "yy/mm/dd")
        aryOutputDetail(i - 2, 2) = wsInOutExtract.Cells(i, 9)
        aryOutputDetail(i - 2, 3) = wsInOutExtract.Cells(i, 10)
        aryOutputDetail(i - 2, 4) = wsInOutExtract.Cells(i, 11)
        aryOutputDetail(i - 2, 5) = wsInOutExtract.Cells(i, 12)
    Next
        
    '出庫リストの表示
    With lstOutput
        .Clear
        .ColumnCount = 6
        .ColumnWidths = "0;100;80;380;100;90"
    
        .List = aryOutputDetail()
    End With

ElseIf wsInOutExtractRow = 1 Then               '出庫予定無し時の回避
    
    '出庫リストの表示
    lstOutput.Clear

End If

'作業ブックを閉じる
Workbooks("在庫管理DATA.xlsx").Close savechanges:=False

        Application.ScreenUpdating = True

'オプションボタン設定
optOutputItemScheduleList.Value = True

End Sub

@1. 異常値の回避
商品選択が無いときは、出庫予定リストを表示し、プロシージャを抜けます。

@2. 作業ブックを開く
エクセルの描画を止め、データブックを開き、作業シートを変数に格納します。
このとき、棚卸日の取得用に商品マスタシートも変数に格納します。

@3. 棚卸情報の取得
商品IDをキーに、商品マスタシートから棚卸日を取得します。

@4. フィルターオプションの設定
検索条件を書き込みます。入出庫日は棚卸日より後とし、商品別リストを抽出します。また、入出庫区分は" 出庫"(出庫の前に、全角のスペースを入れてください)とします。商品IDの入力も忘れないでください。

@5. フィルターオプションの実行
フィルターオプションを実行し、取り込み用に抽出結果の最終行を取得します。取得列はG列となります。
また、検索条件範囲は"A1:D2"となるので、注意してください。

@6. 出庫リストへの表示
抽出結果のリストが有るときは、抽出結果を変数に取り込み、リストへ表示します。
抽出結果のリストが無いときは、リストはクリアして表示なしとします。

@7. 作業ブックを閉じる
作業ブックは保存しないで閉じます。

@8. オプションボタンの設定
商品別リストのオプションボタンを選択状態にします。
 

出庫リストの表示設定

出庫リストボックスには、初期値は出庫予定リストを表示させ、商品選択時 および オプションボタン選択時に商品別リストを表示させます。
サブルーチンの実行位置は5箇所となります。

1. 初期値 = 出庫予定リスト

Private Sub UserForm_Initialize()

~~~~~~~~~~~~~

'出庫予定リストの表示
Call ShowOutputAllScheduleList

End Sub

2. 商品選択 = 商品別リスト

Private Sub cmbItemName_Change()

~~~~~~~~~~~~~

'商品別リストの表示
Call ShowOutputItemScheduleList

End Sub

3. 商品選択リセット = 出庫予定リスト

Sub Reset()

~~~~~~~~~~~~~

'出庫予定リストの表示
Call ShowOutputAllScheduleList

End Sub

4. 出庫予定リストオプションボタン = 出庫予定リスト

Private Sub optOutputAllScheduleList_Click()

'出庫予定リストの表示
Call ShowOutputAllScheduleList

End Sub

5. 商品別リストオプションボタン = 商品別リスト

Private Sub optOutputItemScheduleList_Click()

'商品別リストの表示
Call ShowOutputItemScheduleList

End Sub

6. 商品登録プロシージャー = 商品別リスト

Private Sub 登録()

~~~~~~~~~~~~~

'商品別リストの表示
Call ShowOutputItemScheduleList

End Sub

 

商品出庫画面の出庫リストの表示設定ができました。
次回は修正/削除ボタンを作成していきます。
www.minizaiko.com