ous">

小さな在庫管理

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

トップ > システムVer01 > 商品入庫画面の入庫リスト作成　[VBA在庫管理システムVer01#13]

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

システムVer01 VBA

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

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


VBA在庫管理システムVer01

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

入出庫抽出シートの追加

データブック（在庫管理DATA.xlsx）に入出庫抽出用のシートを追加します。

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

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


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

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

　

入庫予定リストの表示

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

Sub ShowInputAllScheduleList()

        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 aryInputDetail() As Variant
    Dim i As Long
    
    ReDim aryInputDetail(wsInOutExtractRow - 2, 5) As Variant
    For i = 2 To wsInOutExtractRow
        aryInputDetail(i - 2, 0) = wsInOutExtract.Cells(i, 7)
        aryInputDetail(i - 2, 1) = Format(wsInOutExtract.Cells(i, 8), "yy/mm/dd")
        aryInputDetail(i - 2, 2) = wsInOutExtract.Cells(i, 9)
        aryInputDetail(i - 2, 3) = wsInOutExtract.Cells(i, 10)
        aryInputDetail(i - 2, 4) = wsInOutExtract.Cells(i, 11)
        aryInputDetail(i - 2, 5) = wsInOutExtract.Cells(i, 12)
    Next
        
    '入庫リストの表示
    With lstInput
        .Clear
        .ColumnCount = 6
        .ColumnWidths = "0;100;80;380;100;90"
    
        .List = aryInputDetail()
    End With

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

End If

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

        Application.ScreenUpdating = True

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

End Sub

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

@2. フィルターオプションの設定
検索条件を書き込みます。入出庫日は今日以降とし、入庫予定表を抽出します。

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

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

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

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

商品別リストの表示

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

Sub ShowInputItemScheduleList()

'異常値の回避
If cmbItemID.Text = "" Then
    optInputAllScheduleList.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 aryInputDetail() As Variant
    Dim i As Long
    
    ReDim aryInputDetail(wsInOutExtractRow - 2, 5) As Variant
    For i = 2 To wsInOutExtractRow
        aryInputDetail(i - 2, 0) = wsInOutExtract.Cells(i, 7)
        aryInputDetail(i - 2, 1) = Format(wsInOutExtract.Cells(i, 8), "yy/mm/dd")
        aryInputDetail(i - 2, 2) = wsInOutExtract.Cells(i, 9)
        aryInputDetail(i - 2, 3) = wsInOutExtract.Cells(i, 10)
        aryInputDetail(i - 2, 4) = wsInOutExtract.Cells(i, 11)
        aryInputDetail(i - 2, 5) = wsInOutExtract.Cells(i, 12)
    Next
        
    '入庫リストの表示
    With lstInput
        .Clear
        .ColumnCount = 6
        .ColumnWidths = "0;100;80;380;100;90"
    
        .List = aryInputDetail()
    End With

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

End If

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

        Application.ScreenUpdating = True

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

End Sub

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

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

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

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

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

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

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

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

入庫リストの表示設定

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

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

Private Sub UserForm_Initialize()

～～～～～～～～～～～～～

'入庫予定リストの表示
Call ShowInputAllScheduleList

End Sub

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

Private Sub cmbItemName_Change()

～～～～～～～～～～～～～

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

End Sub

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

Sub Reset()

～～～～～～～～～～～～～

'入庫予定リストの表示
Call ShowInputAllScheduleList

End Sub

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

Private Sub optInputAllScheduleList_Click()

'入庫予定リストの表示
Call ShowInputAllScheduleList

End Sub

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

Private Sub optInputItemScheduleList_Click()

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

End Sub

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

Private Sub 登録()

～～～～～～～～～～～～～

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

End Sub

　

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

MIFDUP

コメントを書く