掲載ファイルは、プログラムでEドライブから開きますから
どのファイルも、Eドライブに左側の名前で保存してください。
プログラムを変更すれば、他のドライブに保存可能です。




掲載参考 EXCEL ファイルダウンロード
1. A-10標準部品在庫帳.xlsm
2. 基本使用数台帳.xlsm 　　
3. 製番別−標準部品使用数台帳A-10-1.xlsm
4. 手配部品一覧.xlsm　　　　 　　　　　　　

P1シートの最終列の次の列に罫線を引いて、7行目に「手配数」と書いて、
設定シートの「手配回数」を繰り上げて、E列の「手配列番号」に列番号を書いて、
B列の「次の列追加位置」を繰り上げる。


続きのページへ移動
JumpEnd:
　　Set myRange=Nothing　　　　　　　　　　　　　　' 変数の解除
　　Sheets("P1").Select
　　Range("H3").Selact
　　ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
　　ActiveSheet.EnableSelection=xlUnlockedCells　　　' シート保護

　　Application.ScreenUpdating=True
　　If Flag2=1 Then Call 手配一覧作成　　　　　　　　　' 手配一覧表の作成と印刷
End Sub

[再手配列追加]マクロ

[手配判定と手配]マクロ2


　If Sheets("P1").Range("P" & i)="" Then　　' P1のP列は手配ロット数の列で記入されて
　　　　　　　　　　　　　　　　　　　　　　 ' いればその数値で手配する。
　　TeHai = MaxZ − endZS − Mtot　　　　　' ロット数がない場合の新規手配数計算
　　　　　　　　　　　　　　　　　　　　　　 ' 最大在庫数−最右残数−未入荷数
　Else
　　TeHai=Sheets("P1").Range("P" & i)　　　' ロット数があればその数値で手配する。
　End If

[手配判定と手配]マクロ3
Call 手配判定と手配　　' 出庫処理を行った時の残数を、最小在庫数と比較して、手配が必要か
　　　　　　　　　　　' どうか確認し必要なら手配を行うサブマクロの呼出。

[手配判定と手配]マクロ1



※このマクロは、出荷や不良で在庫数が減少した時に、残数と最小在庫数設定値とを
　比較して、残数が下回ると自動手配を行うものである。
　下の様に、不良が発生して在庫数が減少した時に、残数再計算で手配判定を行っている。

(P1シート)


(設定シート)


If Sheets("設定").Range("B8") <> "" Then　　　　　' B8が空白でなかったら
　If Sheets("設定").Range("B9") <> "" Then　　　　' B9が空白でなかってら
　　intRowZ=Sheets("設定").Range("B8").End(xlDown).Row　　' B列の最下行番を取得
　Else
　　intRowZ=Sheets("設定").Range("B8")　　　　　' B8は一番左の残数位置は19列目
　End If
End If

endLT=Sheets("設定").Range("B" & intRowZ) 　　　' 残数位置が入る。

MinZ=Sheets("P1").Range("K" & i)　　　　' 最小在庫数
MaxZ=Sheets("P1").Range("L" & i) 　　　 ' 最大在庫数
endZS=Sheets("P1").Cells(i, endLT)　　　 ' 最右残数位置

' 入荷数合計------------
Ntot=0
If Sheets("設定").Range("F4") <> 0 Then　　　　　　' 入力回数が 0 で無かったら
　Nka=Sheets("設定").Range("F4")
　For i2=7 To 6 + Nka　　　　　　　　　　　　　　　 ' i2 を 7 から 6 + 入荷回数まで
　　SCol=Sheets("設定").Range("F" & i2)　　　　　　' F7の 20 をSColへ代入する。
　　Nyu=Sheets("P1").Cells(i, SCol)　　　　　　　　 ' P1の20列の8行目をNyuに代入
　　If Sheets("P1").Cells(i, SCol)="" Then Nyu=0　　' 空白ならNyuは 0
　　Ntot=Ntot + Nyu　　　　　　　　　　　　　　　　' 入荷数をNtotに加算する。
　Next
Else
　Ntot=0　　　　　　　　　　　　　　　　　　　　　　' 入荷数が 0 ならNtotは 0
End If

' 手配数合計-------------
TeTot=0
If Sheets("設定").Range("E4") <> 0 Then
　Tki=Sheets("設定").Range("E4")
　For i2=7 To 6+Tki
　　TCol=Sheets("設定").Range("E" & i2)
　　Teh=Sheets("P1").Cells(i, TCol)
　　If Sheets("P1").Cells(i, TCol)="" Then Teh=0
　　TeTot=TeTot + The　　　　　　　　　　　　　　　' 手配数の合計に手配数を加算
　Next
Else
　TeTot=0
End If

' 手配判定-------------
Mtot=Tetot − Ntot　　　　　　　　　　　　　　　　　' 手配数−入荷数＝未入荷数
If endZS + Mtot > MinZ Then GoTo JumpN1　　　　　' 最右残数 + 未入荷数が最小在庫数より
　　　　　　　　　　　　　　　　　　　　　　　　　　 ' 大きかったらJumpN1へ
If Mtot < 1 Then Mtot=0

If endZS + Mtot < MinZ Then　　　　　　　　　　　　' 最右残数 + 未入荷数が最小在庫数より
　　　　　　　　　　　　　　　　　　　　　　　　　　 ' 小さかったら手配が必要になる。
　ZanK=endZS + Mtot　　　　　　　　　　　　　　　 ' 最右残数 + 未入荷数 = ZanK

　If Flag2=0 Then 　　　　　　　　　' Flug2は変数定義してあるが、初期値を定義してないので
　　　　　　　　　　　　　　　　　　 ' 1 にしない限り 0 である。0 の場合は、手配が必要でも
　　　　　　　　　　　　　 　　　　　' まだ手配列がないから、作成する必要がある。。

　　Flag2=1　　　　　　　　　　　※ 8行目から最下行まで見てゆく。最初の行で手配列が作成
　　　　　　　　　　　　　　　　　　 されるので、次の行では列が作成されないように、Flag2を
　　　　　　　　　　　　　　　　　　 見て、1 なら作成しない。

　　Call 再手配列追加　　　　　　　　' Flag2が 0 なら手配列が作成される。
●違う在庫帳を見てみよう21列は20列の入庫の残数で、中間にある入庫列である。

この場合、[8.中間列入庫残数再計算]マクロが使用される。



ZS=Sheets("P1").Cells(i2, DTC2)　　　　　　' 前回の19列の残数
Sheets("P1").Cells(i2, DTC).Value = ZS + Sheets("P1").Cells(i2, DTC)
　　　　　　　　' (19列の残数 5 ) + (20列の入庫数 2) = ( 7 ) が21列の残数に記入される。
　※出庫と入庫では、在庫の数量が変化する。その時に在庫残数が何個になるにかを計算する
　　のが残数計算である。その時に、表全体の残数が本当に正確なのかを確認し、修正する
　　のが残数再計算である。在庫帳では、最も重要な数値であるから、再計算は必要であろう。

----------------------------------------------------------------------------------------------------
ZS=Sheets("P1").Range("H" & i2)　　　　　　' 初期の在庫数
Sheets("P1").Cells(i2, 18).Value = ZS + Sheets("P1").Cells(i2, 17)
　　　　　　　　' (初期在庫数 6) + (17列の入庫数 1) = ( 7 ) が18列の残数に記入される。
　※最左の入庫列である限り、入庫列は17列目で、残数列は18列目と決まっているので、
　　入庫列も残数列も位置が固定されている。
-----------------------------------------------------------------------------------------------------
ZS=Sheets("P1").Range("H" & i2)　　　　　　' 初期の在庫数
Sheets("P1").Cells(i2, 20).Value = ZS + Sheets("P1").Cells(i2, 19)
　　　　　　　　' (初期在庫数 6) + (19列の入庫数 1) = ( 7 ) が20列の残数に記入される。
　※左に手配列二つがあり、最左の入庫列である限り、入庫列は19列目で、残数列は20列目と
　　決まっているので、入庫列も残数列も位置が固定されている。
-----------------------------------------------------------------------------------------------------


●違う在庫帳を見てみよう18列は17列の入庫の残数で、最も左にある入庫列である。

この場合、[7.最左入庫残数再計算]マクロが使用される。


ZS=Sheets("P1").Range("H" & i2)　　　　　　' 初期の在庫数
Sheets("P1").Cells(i2, 20).Value=ZS−Sheets("P1").Cells(i2, 19)
　　　　　　　　' (初期在庫数 6) − (19列の入庫数 1) = ( 5 ) が20列の残数に記入される。
　※左に手配列二つがあり、最左の出庫列である限り、出庫列は19列目で、残数列は20列目と
　　決まっているので、出庫列も残数列も位置が固定されている。
-----------------------------------------------------------------------------------------------------


●違う在庫帳を見てみよう。20列は19列の入庫の残数で、最も左にある入庫列である。
　又、左に手配列が二つある。

この場合、[6.左手配列2、最左入庫残数再計算]マクロが使用される。

ZS=Sheets("P1").Range("H" & i2)　　　　　　' 初期の在庫数
Sheets("P1").Cells(i2, 19).Value=ZS + Sheets("P1").Cells(i2, 18)
　　　　　　　　' (初期在庫数 6) + (18列の使用数 1) = ( 7 ) が19列の残数に記入される。
　※左に手配列があり、最左の入庫列である限り、入庫列は18列目で、残数列は19列目と
　　決まっているので、入庫列も残数列も位置が固定されている。
-----------------------------------------------------------------------------------------------------


●違う在庫帳を見てみよう。20列は19列の出庫の残数で、最も左にある出庫列である。
　又、左の手配列が二つある。

この場合、[5.左手配列2、最左出庫残数再計算]マクロが使用される。

※初期の在庫帳で、左に手配列が二つも来る理由は、更新時に前月の手配数が完納されずに
　繰り越した場合で、更新計算でその手配数では、最小在庫数に満たない場合に再手配される。

ZS=Sheets("P1").Range("H" & i2)　　　　　　' 初期の在庫数
Sheets("P1").Cells(i2, 19).Value=ZS−Sheets("P1").Cells(i2, 18)
　　　　　　　　' (初期在庫数 6) − (18列の使用数 1) = ( 5 ) が19列の残数に記入される。
　※左に手配列があり、最左の出庫列である限り、出庫列は18列目で、残数列は19列目と
　　決まっているので、出庫列も残数列も位置が固定されている。
-----------------------------------------------------------------------------------------------------

●違う在庫帳を見てみよう。19列は18列の入庫の残数で、最も左にある入庫列である。
　又、左の手配列がある。

この場合、[4.左手配列、最左入庫残数再計算]マクロが使用される。

※上の在庫帳では、[3. 左手配列、最左出庫残数再計算]の列配置にならないので、別の
　在庫帳で見てみよう。
●19列は18列の出庫の残数で、最も左にある出庫列である。又、左に手配列がある。

この場合、[3.左手配列、最左出庫残数再計算]マクロが使用される。

●26列は25列の出庫の残数である。中間にある列である。

この場合も、[2.中間列出庫残数再計算]マクロが使用される。

ZS=Sheets("P1").Cells(i2, DTC2)　　　　　　' 前回の残数列23の残数 = 4
Sheets("P1").Cells(i2, DTC).Value=ZS−Sheets("P1").Cells(i2, DTC1)
　　　　　　　　' (23列の残数4) − (25列の使用数 0) = ( 4 ) を26列の残数に記入される。
-----------------------------------------------------------------------------------------------------
　　　　　　　　
●23列は22列の出庫の残数である。中間にある列である。

この場合も、[2. 中間列出庫残数再計算]マクロが使用される。

ZS=Sheets("P1").Cells(i2, DTC2)　　　　　　' 前回の21列の残数 = 5
Sheets("P1").Cells(i2, DTC).Value = ZS−Sheets("P1").Cells(i2, DTC1)
　　　　　　　　' (21列の残数5) − (22列の使用数 1) = ( 4 ) を23列の残数に記入される。
-----------------------------------------------------------------------------------------------------

[2. 中間列出庫残数再計算]マクロ


ZS=Sheets("P1").Cells(i2, DTC2)　　　　　　' 前回の18列の残数 = 6
Sheets("P1").Cells(i2, DTC).Value = ZS−Sheets("P1").Cells(i2, DTC1)
　　　　　　　　' (18列の残数6) − (20列の使用数 1) = ( 5 ) を21列の残数に記入される。
-----------------------------------------------------------------------------------------------------

8行目から順に下へ、空白行、青色行、赤色行を除外して、残数を計算して記入して行く。

ZS=Sheets("P1").Range("H" & i2)　　　　　　' H列は初期の残個数である。
Sheets("P1").Cells(i2, 18).Value = ZS−Sheets("P1").Cells(i2, 17)
　　　　　　　　' (初期在庫数 6)− (17列の使用数0) ＝ ( 6 )を 18列の残数に記入される。

Call 標準部品使用数台帳に転記　　　　　　　　　' 使用数台帳に記入する。
-----------------------------------------------------------------------------------------------------

●21列は20列の出庫の残数である。中間にある列である。

この場合、[2.中間列出庫残数再計算]マクロが使用される。


[1. 最左出庫残数再計算]マクロ
※現在の在庫帳P1シートの現状に沿って、残数計算を見てゆこう。
●18列は17列の出庫の残数である。一番左にあることが分かる。

この場合、[1.最左出庫残数再計算]マクロが使用される。
※残数は、在庫帳の中でも一番重要な数値である。担当者が出庫数や入庫数を何気なく
　変更した場合でも、それに対応した、正確な残数値でなくてはならない。
　だから、ここでは入出庫時には、必ず一番左の列から残数の再計算を行い書き直して
　いる。
　残数の再計算は、現在計算する列の前に、入庫、出庫、手配の列が存在する可能性が
　ある。そのために現在計算する残数列位置の、前の列に何が存在するのかが分かって
　いなければならない。そのために設定シートに、出庫、手配、入庫の旅に列情報を記入
　している。残数の計算を行うのは、出庫と入庫だけである。

　残数の位置によって、計算式は8種類に分類できる。だから8種類のマクロを作成して分岐
　させる事が考えられる。
　1. 最左出庫残数再計算　　　　　　　(一番左の出庫列)
　2. 中間列出庫残数再計算　　　　　　(中間にある出庫列)
　3. 左手配列、最左出庫残数再計算 　　(左に手配列があり、一番左の出庫列)
　4. 左手配列、最左入庫残数再計算　 　(左に手配列があり、一番左の入庫列)
　5. 左手配列2、最左出庫残数再計算 　 ( 左に手配列が2列以上あり、一番左の出庫列)
　6. 左手配列2、最左入庫残数再計算 　 ( 左に手配列が2列以上あり、一番左の入庫列)
　7. 最左入庫残数再計算　　　　　　　 ( 一番左の入庫列)
　8. 中間列入庫残数再計算　　　　　　 ( 中間にある入庫列)
Sheets("P1").Cells(i, intCol).Select　　' 黄色になっているセルを選択する。
With Selection.Interior
　.Pattern = xlNone　　　　　　　　　' セルの色を無くしている。
End With

[残数再計算]マクロ



[手動出庫の色変更]マクロ
[残数計算]ボタンマクロ
製番と納期、出庫数を入力して[残数計算]ボタンを押すと
残数列に残数が記入される。

前回の残数から今回の出庫数を減算して、残数が割り出されている。
(P1シートに入力後残数計算フォームが表示された状態)
入力後残数計算.Show vbModoless
　　' 入力後残数計算フォームを移動可能状態で表示する。
[文字右寄せ]マクロ


[文字中心]マクロ


[手動入力 個別出庫]ボタンマクロ


製番列と残数列が25と26列目に作成された。

Columns(T1).Select　　　　　　　　' T1=25 で列全体を選択する。
Selection.ColumnWith = 12　　　　' 文字の大きさを 12 に設定する。
Columns(T1 + 1).Select　　　　　　' 25+1 の列を選択する。
Selection.ColumnWith = 8　　　　　' 列幅を少し狭く 8 にしている。

Range("Cells(8, T1). Cells(intRow, T1 + 1)).Select　　' 25列の8行目から、26列の最下行までを
　　　　　　　　　　　　　 　　　　 　　　　　　　　　' 選択する。
Call 文字右寄せ
intRow=Sheets("P1").Range("D1000").End(xlUp).Row
Sheets("P1").Range(Cells(5, T1), Cells(intRow, T1+1)).Select　　' 以下のコードで罫線が引かれる。
Selection.Borders(xlDiagonalDown).LineStyle=xlNone
Selection.Borders(xlDiagonalUp).LineStyle=xlNone
(P1シート)　　　　　　　　　　　　　' P1シートの25列目は空白になっている。
T1=Sheets("設定").Range("B4")　　' 設定シートのB4に、次の列追加位置が
(設定シート)　　　　　　　　　　　　' 書いてあので、それを取得している。
[出庫と入荷列追加]マクロ
　Worksheets("P1").Cells(6, N).Select 　　　　　　' 使用列を選択する。
　Application.MoveAfterReturnDirection=xlDown
　　　　　　　　　' エンターを押した時に下のセルが選択されるようにする。
End Sub
(設定シート)
K2=Worksheets("設定").Range("D4")　　　　　　　　' 現在の使用回数を記憶する。
Worksheets("設定").Range("D4").Value= K+1　　　　' 使用回数を繰り上げる。
Worksheets("設定").Range("D" & K2+7).Value=N　　' 新規の使用列番号25を書く。
Worksheets("設定").Range("B4").Value=Worksheets("設定").Range("B4")+2
　　　　　　　　　　　　　　　　' B4に次回の最右列挿入位置を書く
intRow5=Sheets("設定").Range("B50").End(xlUp).Row+1
　　　　　　　　　　　　　　　　' B列の最下行番号 +1を書く。
Worksheets("設定").Range("B" & ntRow5).Value=N+1
　　　　　　　　　　　　　　　　' B列の最下行に残数列位置を書く。
(色の付いた青色行と文字)
intRow=Sheets("設定").Range("R6").End(xlDown).Row　　　' 最下行番号を取得
intRow2=Sheets("設定").Range("S6").End(xlDown).Row
intRow3=Sheets("設定").Range("Q6").End(xlDown).Row

For i=7 To intRow　　　　　　　　　' 表内の青色行位置を順に選択して、色を付ける。
　RG=Sheets("設定").Range("R" & i)
　Sheets("P1").Range(Cells(RG, N), Cells(RG, N+1)).Select　　' 範囲選択
　Call 青色行
　Sheets("P1").Cells(RG, N).Value="使用数"　　　' 使用数と書く。
　Sheets("P1").Cells(RG, N).Select
　With Selection
　　.HorizontalAlignment=xlCenter
　End With
　Sheets("P1").Cells(RG, N+1).Value="残数"　　　' 残数と書く。
　Sheets("P1").Cells(RG, N+1).Select
　With Selection
　　.HorizontalAlignment=xlCenter　　　　　　　　' 文字位置をセンターにする。
　End With
Next
For i=7 To intRow2 　　　　　　　　' 表内の赤色行位置を順に選択して、色を付ける。
　SG=Sheets("設定").Range("S" & i)
　Sheets("P1").Range("Cells(SG, N), Cells(SG, N+1)).Selsct
　Call 赤色行
Next
在庫表内の青色行、赤色行、空白行の位置が
記録されている。
(設定シート)

Sheets("P1").Cells(4, N).Value = N　　　　　　' 25列目の4行目に行番号25を記入する。
Sheets("P1").Cells(5, N).Value = "製番"　　　　' 25列目の5行目に製番と、記入する。
Sheets("P1").Cells(5, N).Select
Call 文字中心　　　　　　　　　　　　　　　　　' 製番文字を中心に配置する、サブマクロ
Sheets("P1").Range("Cells(8, N),Cells(intRow, N)).Select
　With Selection.Interior　　　　　　　　　　　' 25列目の8行目から最下行までのセルを
　　.Color=13434879　　　　　　　　　　　　 ' 朱色にする。
　End With

Sheets("P1").Cells(6, N+1).Select　　　　　　 ' 25+1=26列目の6行目選択する。
Selection.NumberFormatLocal="m/d;@"　　　' 日付セルなので、11/6 の様に表示される
　　　　　　　　　　　　　　　　　　　　　　　' ように設定している。
Worksheets("設定").Range("C4").Value=N2+2　　' 設定シートの次回の残数列番号を加算しておく。
(設定シート)


(P1シート)

Active Windows.WindowState = xlNormal　　　　　' ウインドウの表示を標準にする。
　　　　　　　　　　　　　　　　　　　　　　　　　 ' 表示を手動で拡大や縮小しているかもしれない。
Unload 入出庫操作　　　　　　　　　　　　　　　　 ' フォームの非表示
Sheets("P1").Select
intRow = Sheets("P1").Range("E1000").End(xlUp).Row　　　' 最新の行数を取得する。
ActiveSheet.Unprotect　　　　　　　　　　　　　　　　　　　 ' 保護の解除
Application.ScreenUpdating = False　　　　　　　　　　　　　' マクロ実行時に画面を固定する。

Call 出庫と入庫列追加　　　　　　　　　　　' サブマクロの実行

N = Worksheets("設定").Range("B4")　　　' 設定シートの B4 = 25 を取得する。
N2 = Worksheets("設定").Range("C4")　　 ' 設定シートの C4 = 26 を取得する。
K = Worksheets("設定").Range("D4")　　　' 設定シートの D4 = 3 を取得する。
[出庫入力]マクロ

[一台分出庫]フォームには、個別出庫と一式出庫がある。
[個別出庫]は、出庫する部品を自分で選択して、出庫列セルに出庫数を記入する方法である。
　　　　　　　修理部品の出庫など、必要な部品を選択して必要な数量を出庫するときに向く。
[一式出庫]は、製番の商品を一台分まとめて出荷するものである。この時に先に設定した
　　　　　　　オプションが付く場合には、どのオプションを付けるかの選択が表示される。
　　　　　　　すべての部品が、一台に必要な数量を計算されて入力される。

[手動入力 個別出庫]ボタンマクロ
Unload 入出庫操作　　　　　　 　' 入出庫操作フォームを非表示にする。
一台分出庫.Show vbModeless　　' 一台分出庫フォームを移動可能状態で表示する。


(設定シートB7)
If Sheets("設定").Range("B7") = 1 Then
　' 設定のB7に1が入っている時には、[手配確定]
　' ボタンを押さなくてはならない、理由は手配処理
　' の時に説明します。
[出庫入力]ボタンマクロ
■使用開始前設定の[操作フォーム]と[入出庫操作フォーム]
■入出力操作フォームの説明
　●出庫入力の操作
' MsgBox の myAns には [OK] と [キャンセル]で返り値が
' 変わります。　OK=1 　キャンセル=2 です。
' If myAns = 2 Then Exit Sub は、返り値が2の場合は、
' メッセージを表示しないで、マクロを終了します。
※ & vbCrLf _ の下線は文字数が長くて折り返す場合に使用し、
　半角英数で、シフトを押しながら [ろ] ボタンを押します。
myAns=MsgBox("使用開始点に戻ります。" & vbCrLf _
　　　　"OKを押せば入出庫データは消去されます。", vbOKCancel)
If myAns = 2 Then Exit Sub
※[使用開始]マクロは、
　1. [設定]シートの列に関するデータを初期に戻し、P1シートをP2シートにコピーする。
　　これは、P1シートに問題が発生した場合の予備保存である。
　2. 品名すべての初期在庫数を、最小在庫数と比較して不足している場合には手配する。

[使用開始]マクロ2


[使用開始]マクロ1
CB使用開始.Visible = False　　　　　　' [CB使用開始]ボタンが非表示になる。
CommandButton.Visible = True 　　　' [CommandButton1]が表示される。
　　※使用開始後、間違って[使用開始]を押せないように非表示にしている。
　　　そのために、[CommandButton1]で再度表示できるようにしている。
[CB使用開始]マクロ
警告が表示されるが、[OK]を押せば初期化せれます。[キャンセル]では中止します。
[使用開始]とは、在庫帳の初期設定が完了した状態から、実際に使用する状態に表と設定を
更新します。初期設定後、使用数や手配などの入力テストを行っていて、表に数値が記入され
ていても、初めて使用する状態に戻します。

[使用開始]ボタンを押した状態。
●使用開始
※イニシャライズは、フォームが開かれた時に、何をするのかを設定する。
　テキストボックスは、開いたときに空白にしておかないと、前回入力されたままで
　保存された場合には、値が入っていることになる。
' TextBox1="" はフォームを表示した時に、
' テキストボックスの値を空白にする。

' J17に値があれば、既に設定されている。
[イニシャライズ]マクロ
[OP設定]フォームを非表示にする。
Cancel]ボタンマクロ
[設定内容が見たい]ボタンマクロ
※メッセージボックスの文字の折り返しは、vbCrLfを書く。
　vbOKCancelと書けば、[OK]と[キャンセル]ボタンが表示される。
　そのどちらのボタンが押されたかの判定をする。

If 警告 = vbCancel Then Exit Sub　　　' [キャンセル]ボタンが押されたらプログラム終了。

Sheets("設定").Range("J17:K20").ClearContents　　' 設定範囲の値だけを消す。

If TextBox1 <> "" Then Sheets("設定").Range("J17").Value = TextBox1.Text
If TextBox2 <> "" Then Sheets("設定").Range("K17").Value = TextBox2.Text
If TextBox3 <> "" Then Sheets("設定").Range("J18").Value = TextBox3.Text
If TextBox4 <> "" Then Sheets("設定").Range("K18").Value = TextBox4.Text
　　　　　　　　　' テキストボックスの値を設定シートに記入する。
intTB =0
If TextBox1.Text <> "" Then intTB = intTB + 1 　　　　' テキストボックスが空白でなかったら
If TextBox2.Text <> "" Then intTB = intTB + 1　　　　' intTBに 1 を加える。
If TextBox3.Text <> "" Then intTB = intTB + 1
If TextBox4.Text <> "" Then intTB = intTB + 1

If intTB = 0 And Sheets("設定").Range("K27") = 1 Then　　' intTB = 0 で K27が 1 ならメッセージ
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　 ' ボックスが表示される。
　※intTB変数は、テキストボックスに値が入っているのかを調べるためにあり、それによって
　　メッセージボックスを表示するか、しないかを決めるものである。
Sheets("設定").Range("K27")=1　　　' 品番設定を行ったので Flag2 のK27に 1 を記入する。
　　　　　　　　　　　　　　　　　　　※ここを見れば、設定が終わっているのが確認できる。
[変更して実行]ボタンマクロ
(OP設定フォーム) テキストボックスの名称
[オプション　部品設定]ボタンマクロ


OP設定.Show vbModeless　　　　' OP設定フォームを移動可能状態で表示する。
[設定シート]
[オプション出庫警告]フォーム
※オプション部品とは、商品に特定の機能を付加する場合に必要になる部品で、すべての製番には
　使用しないので、出庫の操作をする時に、オプション出荷警告が表示される。
　その時に、オプション名にチェックを付けると、自動で出庫してくれる。
　部品名は、初期設定で設定シートに記載されている。
●オプション部品の設定
※UserForm_Initializeはスライダーで表示し、リストから選択する。
　左側に UserForm 、右側に Initialize を選択すると、
　Private Sub UserForm_Initialize( ) が自動でできる。
(フォームの UserForm と Initialize の表示方法)
Private Sub CMcan_Click( )
　Unload 最大、最小在庫数計算　　　　　　　　' 最大、最小在庫数計算フォームを非表示にする。
　Range("K3").Select
　画面.Show vbModeless　　　　　 　　　　　 ' 画面フォームを移動可能状態で表示する。
End Sub

Private Sub UserForm_Initialize( )
　If Sheets("P1").Range("M3") <> "" Then 　 ' M3が空白でなかったら
　　TBmsg.Text="設定済み"　　　　　　　　　' テキストボックスに「設定済み」を書く
　Else
　　TBmsg.Text="設定してください"　　　　　' M3が空白なら
　End If
End Sub
[Cancel]ボタンと[Initialize]マクロ
If Len(Sheets("P1").Range("M3"))=3 Then　　　　　　' M3の文字(4〜7)の文字数が3なら
　T1.Text=Left(Sheets("P1").Range("M3"), 1)　　　　 ' 左から1番目文字4をT1に書く
　T2.Text=Right(Sheets("P1").Range("M3"), 1)　　 　 ' 右から1番目の文字をT2に書く
End If　　　　　　　　　　　　　　　　　　　　　　　　' T1.Text=4,　T2.Text=7になる。

If Len(Sheets("P1").Range("M3"))=4 Then　　　　　　' M3の文字数が4なら
　T1.Text=Left(Sheets("P1").Range("M3"), 1)
　T2.Text=Mid(Sheets("P1").Range("M3"), 3, 4)　　　' 左から3番目と4番目をT2に書く
End If　　　　　　　　　　　　　　　　　' 5文字の場合は、左が1桁で右が2桁のはずである。
(P1シート)
[設定値を見たい]ボタンマクロ
　　If Cells(i, 13)="" And Cells(i, 14)="" And Cells(i, 15)="" Then GoTo Jump
　　　　　　' どの機種にも数値が無いとGoTo Jump へ移動する。
　　　　　　' 複数の機種で同じ部品を使用している場合もある。


　　If Cells(i, 9)="" Then　　　　　　　　　' 9列目は単価
　　　MsgBox i & "行目が単価未入力"　　　 ' i 行目が単価未入力と表示する。
　　　Unload 最大、最小在庫数計算　　　　 ' フォームを非表示にする。
　　　Exit Sub　　　　　　　　　　　　　　' 単価が書いていないと終わる。
　　End If

　　intMin=(A10Min * Cells(i, 13))　　　　' A10Minは入力値で、一台当たりの数値と
　　Cells(i, 11).Value=intMin　　　　　 　 ' 積算して最小在庫数に記入する。
　　intMax=(A10Max * Calls(i, 13))
　　Cells(i, 12).Value=intMax　　　　　　 ' 積算して最大在庫数を記入する。
Jump:
　　Next
　　Cells(3, 13)=A10Min & "〜" & A10Max　　' 13列の3行目に見やすく書いている。
　　Range("K3").Select
　　ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
　　ActiveSheet.EnableSelection=xlUnlockedCells
　　　　　' 間違って消せないように、セルの書式設定でセルのロックを設定している。

　　ActiveWorkbook.Save　　　　　　　　' 上書き保存
　　Application.ScreenUpdating=True　　' プログラム実行中の画面移動制限を解除
End Sub
TBmsg.Text=""　　　　　　　　　　　　　　' テキストボックスを空白にしておく。
application.ScreenUpdating=False　　　　　' マクロ実行中に画面移動を無くして置く。

A10Max=T2.Text　　　　　　　　　　　　　' 設定が済の場合は、テキストボックスに値が
A10Min=T1.Text　　　　　　　　　　　　　 ' あるので、それに備えて変数に保存しておく。

Sheets("P1").Select　　　　　　　　　　　　' 在庫帳シート
ActiveSheet.Unprotect　　　　　　　　　 　 ' 保護の解除
intRow=Range("D500").End(xlUp).Row　　' 品番列の最終行番号を取得する。

For i=8 To intRow　　　　　　　　　　　　　　' P1シートの8行目から下へintRowまで
　For i21=7 To intRow2　' 無色行　　　　　　　' i=8 の時 i21=7
　　intQ=Sheets(設定").Range("Q" & i21)　　　' 設定のQ7は23だから intQ=23
　　If intQ=i Then GoTo Jump　　　　　 　　 　' i が23になった時に intQ と同じに
　Next　　　　　　　　　　　　　　　　　　　　' なるので、GoTo Jumpになる。
　For i22=7 To intRow3　' 青色行
　　intR=Sheets(設定").Range("R" & i22)　　　' i22=7 の時 R7 は26である。intR=26
　　If intR=i Then GoTo Jump　　　　　 　　　 ' i が26になった時は青色行である。
　Next
　For i23=7 To intRow4　' 赤色行
　　intS=Sheets(設定").Range("S" & i23)　　　' i23=7 の時 S7 は84である。intS=84
　　If intS=i Then GoTo Jump　　　　 　　　　 ' i が84になった時は赤色行である。
　Next
　Range(Cells(i, 11),Cells(i, 12)).ClearContents　' 最小と最大の数値を消している。

※P1シートの11列目は最小在庫数の列で、12列目は最大在庫数の列である。
　13列目に一台に使用する部品の数量が書かれている。
　だから、各行の最大数も最小数も、入力値に一台に必要な数量を積算して算出している。

(P1シート)

If T1.Text="" Then
　TBmsg.Text="台数がに入力"
End If
　' T1内のテキストは、T1.Text と指定する。
　' 空白の場合は、TBmsg に警告を書く。
　' T1が空白なら、プログラムを終了する。
(フォーム内のテキストボックスの名称)
' 設定シートのQ列とR列、S列には在庫帳の
　タイトル行や空白行の位置が書かれている。
　[初期設定]で取得される。
※このプログラムは、入力された台数分の個数を
　Pシートの各行に記入して行くもので、8行目
　から下へ順に記入して行くが、空白行や青色行
　や赤色行には数値を記入してはいけないから、
　その行を飛ばして下に移動している。
(設定シート)
IntRow2=Sheets("設定").Range("Q6").End(xlDown).Row
IntRow3=Sheets("設定").Range("R6").End(xlDown).Row
IntRow4=Sheets("設定").Range("S6").End(xlDown).Row
　　　' 設定シートのQ,R,S 各行の最下行番号を調べて intRow に代入する。
「一台に必要な部品数量」が記入されていると、最小数と最大数をテキストボックスに入力し、
[計算実行]ボタンを押せば、各部品行に数値が記入される。
又、[設定値を見たい]ボタンを押せば、現在の設定数がテキストボックスに表示される。

[計算実行]ボタンマクロ
最大、最小在庫数計算.Show vbModeless 　　' vbModeless は表示位置を移動可能になる。

(最大、最小在庫数計算)フォームが表示された状態
[最小、最大在庫数設定]ボタン押せば、在庫数計算フォームが表示される。

●最小、最大在庫数の設定
　[最小、最大在庫数]を設定して置くと、在庫残数が最小になると最大数まで手配処理される。
　
※実際に使用されると、使用数欄に製番ごとの使用数が記入されて保存される。
　出庫されるたびに、G7から右に製番と使用数が記入されて保存される。
※基本使用数台帳に品名などの項目が転記されて、製番別−標準部品使用数台帳になる。
　使用数欄には、出荷がまだないので記入されていない。

(製番別−標準部品使用数台帳A−10−1.xlsm のシート)
(製番別−標準部品使用数台帳A−10−1.xlsmのシート)
(基本使用数台帳のシート)
※ここでは基本使用数台帳.xlsmと言うファイルに、在庫帳のP1の項目をコピーして
　新しい[製番別−標準部品使用数台帳]ファイルとして保存している。

Workbooks.Open Filename:="E:\基本使用数台帳.xlsm"
Windows(myName).Activate
Range("D8:E" & intRow).Copy
Windows("基本使用数台帳.xlsm").Activate
Sheets("P1").Range("B8").Select
ActiveSheet.Paste

Windows(myName).Activate
Sheets("P1").Select
Range("I8:I" & intRow).Copy
Windows(基本使用数台帳.xlsm).Activate
Sheets("P1").Range("D8").Select
ActiveSheet.Paste
Application.CutCopyMode=False

Windows("基本使用数台帳.xlsm").Activate
ActiveWorkbook.SaveAs Filename:="E:\" & NFname, FileFormat:= _
　xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
※E:\製番別−標準部品使用数台帳A−10−1.xlsmとして保存している。
Sheets("設定").Range("M4")=Sheets("設定").Range("M4") + 1
NFname = Sheets("設定").Range("M6")

[設定]のM4には初期値で、0が入っている。それに1を加えてM4に記入する。
[設定]のM6には関数が入っていて、製番別−標準部品使用数台帳A−10−1.xlsmと
表示される。
そのファイル名が NFnameに代入される。
※ファイルを作成するたびに、連番でファイル名が作成される。

(設定シート)

[中間タイトル行の自動取得]マクロ3
If Sheets("P1").Range("D" & OPBT1 + i2)="" Or Sheets("P1") _
　　　　　　　　.Range("D" OPBT1 +i2) =" " Or Sheets("P1") _
　　　　　　　　.Range("D" & OPBT1 + i2) = "　" Then
※このコードの 「="" Or 」の部分は、78行の空白セルに当たった時に、それが空白なのか、
　半角スペースなのか、全角スペースなのか、分からないのでそれを調べている。
(設定シート)
(P1シート)
If Sheets("P1").Range("D" & i)= "オプション1" Then
　　OPBT1= i + 1
　　Sheets("設定").Range("J17").Value=OPBT1
　　If OPBT1 <> "" Then
　　　　For i2 = 1 To 10
　　　　　　If Sheets("P1").Range("D" & OPBT1 + i2)="" Or Sheets("P1") _
　　　　　　　　.Range("D" OPBT1 +i2) =" " Or Sheets("P1") _
　　　　　　　　.Range("D" & OPBT1 + i2) = "　" Then
　　　　　　　　Sheets("設定").Range("K17").Value= OPBT1 + i2 − 1
　　　　　　　　Exit For
　　　　　　End If
　　　　Next
　　End If
End If
※For で上から順に行を調べてきて、i が75になった時に、D75が「オプション1」なら、
　部品名の書いた行は次なので、i + 1 の76 を設定シートの J17 に書く。
　「オプション1」の部品が何行あるのかを調べるために、順に下へ調べて空白行に当たったら、
　その行の一つ上の行が最終行だから、その行番号を設定シートの K17 に書く。'
myName＝ActiveWorkbook.Name 　　' 開いているファイルネームを myName へ保存し
　　　　　　　　　　　　　　　　　 　 ' 他のファイルを開いて操作しても、元のファイルへ
　　　　　　　　　　　　　　　　　 　 ' 戻れるように保存している。
Windows(myName).Activate　　　 　 ' 二つのファイルを同時に開いている場合、後で開いた
　　　　　　　　　　　　　　　　　 　 ' ファイル名は分かるが、元になるファイル名は分から
　　　　　　　　　　　　　　　　　 　 ' なくなるので、変数名でアクティブできる。

Col=Cells(7,16).End(xlToRight).Column　　' 7行目の16列目から最右端データセルの列番号を
　　　　　　　　　　　　　　　　　　　　　 ' 取得する。

[中間タイトル行の自動取得]マクロ2
[中間タイトル行の自動取得]マクロ1
※[初期設定]は在庫帳の、タイトル行の位置や空白行の位置、
　オプションの位置を把握して、設定シートに記入する。
　又、Eドライブにある基本使用数台帳を基に当月分の
　使用数台帳を作成する。　
[初期設定]マクロ
●初期設定
　
　
[文字修正]ボタンは、シート保護の解除を行って修正を可能にして、[終了]ボタンで
保護を行っている。不注意で変更できないようにしている。
[文字修正]ボタンマクロ
[文字修正]を押せば、文字の修正や行の追加と削除ができます。
[終了]で終わります。


●表内修正
　
[列表示][枠固定]ボタンマクロ


[枠固定]を押すと、水平スライダーで画面を左に移動しても、8列目から左には動かない。
[枠固定解除]で解除できる。
●列表示、非表示
　　　　
　　[列非表示]を押すと、F,G,I,J,K,L の6列が非表示になる。
　 [列表示]で解除できる。
使用開始前の設定フォーム
　1.列表示や枠固定は、普段使う入出力状態の時には、見る必要のない列を非表示や固定して見易すく
　　することができる。
　2.在庫帳の行数や品種ごとの区切り行、空白行、加工部品と市販購入部品の区切り行などの、位置を
　　調べて設定シートに記録する。
　3.最小と最大の在庫数は、最小の個数になると自動で手配を促すための設定値である。
　4.オプション部品は、注文の状態によっては、その製番の商品には使用しない場合があるので、使用
　　する場合に指定すると、登録してある部品を一式出庫入力してくれる。

入出庫操作フォーム
　1.出庫処理、手配処理、入庫処理、部品の価格変更処理などを行うための、操作フォームが表示され
　　る。
　2.月末に完成、未完成などの製番の処理をして、来月用の新しい在庫帳を作成してくれる。
　3.月末の在庫数を集計して、在庫部品明細表に出力し印刷できるように処理をしてくれる。
　
※在庫帳の構造は、左の固定部分に品番や品名が記入されている。
　部品行の追加と削除は自由にできる。また空白行を挿入しても良い。
●記入内容で重要な項目は、製品一台に使用する部品個数、最小在庫数、最大在庫数、部品単価、仕入先コード
　番号で、部品単価は変更すると在庫集計表の単価も連動して変更される。
　
(在庫帳 P1 シート)
●このシステムは、日々入出庫を記録する在庫帳と、手配部品の一覧が出力されることによって、注文書発行
　システムとの連携ができる。
　在庫帳には2種類ある。
　1.実際に出庫した時に、出庫した数量を記入する。
　　※この場合、在庫帳の残数が実際の在庫数である。だから、出庫数が急に増えると残数が不足する場合がある。
　2.注文が来た時に、その製造番号(以後製番と表記する)と使用数を記入する。
　　※このやり方は、生産するときに部品がそろっているようにする為で、納期を確実に守るためである。
　　　だから、在庫帳の残数は実際の在庫数ではなく、使用するよりも先に引かれている。
　　　だから、月末に実際の在庫数を算出するには、未生産の製番の部品を戻さなくてはならない。
　この在庫帳は、2.のやり方を採用している。出庫、手配、入庫、出庫後の残数が表示される。
　又、最小在庫数、最大在庫数を設定して置くと、在庫残数が最小在庫数になると手配列が作成されて、最大在庫数
　になるように自動で手配数が入力される。
　月末処理は、受注残ファイルを参照して、生産した製番と未生産の製番を判別して、実際の在庫残数を計算する。
　そして、在庫数集計表と在庫金額集計表が作成できる。
　更新後は、未生産の製番と、未入荷の手配数が記入された来月用の在庫帳になる。
　在庫帳ファイルは、P1、P2、設定、集計、手配表、日付、コードの各シートから成り立っている。
　出庫は使用数台帳に保存し、受注残や発注、仕入処理とも連携している。
　※ 参照Excelファイルは、[A-10標準部品在庫帳.xlsm]で、その他関連するファイル[受注残]ファイル
　　 [在庫集計]ファイル、[使用数台帳]ファイルなど、掲載ファイルは、プログラムでEドライブから開きますから
　　 どのファイルも、Eドライブに保存してください。プログラムを変更すれば、他のドライブに保存可能です。
　　 参照ファイルは、最下部よりダウンロードしてください。
　
実践コース
在庫帳の作成と運用システムを学ぶ 1


実際に使っているシステムの内容で解説
　　EXCEL VBA 講座