無料VBA講座

[お助け]ボタン

[お助け]ボタンマクロ

※前述で説明した、[設定確認記入]マクロが実行される。このマクロは、列の
　入力値を変えることはできないが、設定シートの列位置記録を更新し残数の
　再計算ができる。列の削除や入力値の変更を、正規の方法を使用しないで
　行った時など、残数値に疑問が出た時などに修正を行ってくれる。

前のページに移動

[仕入先コードの検索]ボタン

[C]ボタンを押すと、[仕入先コード検索]フォームが表示される。

[仕入先コードの検索]ボタンマクロ

※[仕入先コード]をテキストボックスに入力し、[検索]ボタンを押すと会社名が
　表示される。[会社名]を入力すると、コード番号が表示される。

[検索]ボタンマクロ

strSh=Range("A1").Parent.Name　　' これは、どのシートから実行しても、
　　　　　　　　　　　　　　　　　　' そのシートに戻るために保存している。
strNam=TextBox1.Text　　　　　　　' 会社名入力
strNam2=TextBox2.Text　　　　　　 ' コード番号入力
If strNam<> "" Then　　　　　　　　' 会社名が空白でなかったら、会社名で検索する。
If strNam2 <> "" Then　　　　　　　' コード番号が空白でなかったら、コード番号検索
　※両方が書かれていても、会社名検索で抽出されて終わる。
　※実際の場合、会社名には(株)などがついているので、(株)や(有)付も検索することになる。
　　又、ここでは会社名とコード番号の、どちらでも検索できるように[コード]シートに
　　両方のデータが並んでいる。ボタンを押した時に右側に即コピーしている。

[コード]シート

[在庫標準部品入力.xlsm]の[標準部品計算1]シート

[在庫標準部品入力.xlsm]の[標準部品入力1]シート

※[在庫標準部品入力.xlsm]の[標準部品入力1]シートは、加工部品在庫表と購入部品在庫表が
　左右にあり、他に[標準部品計算1]シートがあり加工単価が記入されていて、加工費が関数で
　計算される。それを[在庫報告書.xlsm]と言うファイルがデータを取得して分類し、在庫報告書
　として作成する。在庫報告書は参考図を参照してください。

●[在庫報告記入]の操作

※[在庫報告記入]を押すと、Eドライブの[在庫標準部品入力.xlsm]ファイルに更新済在庫帳の
　初期在庫数が記入される。この数値が、6月末の仕掛部品在庫量だからである。
　6月末締め切りの場合、6月分の売上高に対する標準部品は、売上製番に使用されている。
　だから、その製番分の部品を除いた余りが6月の在庫になる。今日の時点では7月であり、
　在庫報告書の作成は6月分である。だから7月分の在庫初期値は、6月締め切りの在庫数
　になる。

[在庫報告記入]ボタンマクロ

[標準部品入力へ転記A10加工]マクロが実行されて、[在庫標準部品入力.xlsm]の加工部品
シートの数量列に在庫帳の数量が記入される。

[標準部品入力へ転記A10加工]マクロ

[標準部品入力へ転記AA10購入]マクロでは、下部の Save と Close が追加されている。
　ActiveWorkbook.Save
　ActiveWorkbook.Close
End Sub

7. 来月分の製番列と手配列を残して不要列が削除された。
　

　※手配列の数値がゼロになっている列もあるが、下方には数値がある行が存在する。

8. 列幅をそろえて、使用数列の次に残数列を挿入し、残数が記入された。
　

　※手配列に数値が見えないが、下方には数値が入っている行が存在する。

　Call 列幅修正と0値消去
　Call 月末残数列の作成
　Call 月末残数計算
　※上記マクロでここまでの処理を実行した。

　Call 手配判定と手配　　　　' 新しい表が作成された時点で、残数と最小在庫数が比較され
　　　　　　　　　　　　　　' 不足部品は手配される。
　Call 列非表示
　call 枠固定　　　　　　　　' 列を見やすくするために、不要部分を非表示にされる。
End Sub

[月末更新]マクロの概要
1. 在庫帳の列表示や枠固定を解除して全体を見られるようにし、セルの保護を解除する。
2. 表の最右列の空白部で、手配合計、入庫合計、使用合計を集計する。
　

3. Call 使用製番抽出
　 Call 受注残製番抽出
　 Call 使用製番から来月分を抽出
　※これらのマクロを使用して、出庫製番を受注残に記載されている製番と比較して、
　　受注残に記載されている製番を来月分と分類する。

(受注残ファイル)
　

4. 当月使用数を求めて記入する。
　

5. 当月の初期在庫数、来月の初期在庫数、未入庫数を記入する。
　

　※この時点で、右部の計算領域は消去されて、初期在庫数が数量に記入される。
　

6. 集計シートに、来月分の製番の列番号、当月分の製番の列番号、不良で削除する列番号を
　記入する。
　

●[月末更新]の操作

※月末更新とは、締切月の替わり目で、昨日が当月の売上と仕入の最終日で、今日から月が
　替わる日である。締め切り日は、その会社によって20日であったり、月末日であったりする。
　締め切り日が20日でも月末日でも、月更新を実行する日は必ずその日以後である。
　だから、締め切り日を過ぎると「当月」は停止する。
　仮に、締め切り日が末日の場合で、今日が10月だと月末は31日である。
　この場合、「当月」と呼ぶのは10月で、11月は「来月」と呼ぶことになる。
　だから、11月1日になると暦の月は替わるが、締め切り日はではまだ10月である。
　売上と仕入の当月分の処理が完了していないので、処理が完了して月末更新が可能になる。
　だから、更新可能日は11月1日以後の、11月3日になっているかもしれない。
　この在庫帳は、更新前でも来月分の製番を出庫することが可能である。なぜなら、製番には
　納期が決まっているから、当月と来月の区別がつくからである。
　入庫処理は仕入金額が発生するので、当月分仕入部品はすべて入庫しなければならない。
　仕入処理で来月分仕入と決定されたものは、入庫処理をしてはいけない。
　なぜなら、当月分の仕入金額は、当月の売上に対する外部費用で、仕入をして残ると在庫に
　なり、その金額を外部費用全体から減額して外部費用総計とするからである。
　[月末更新]とは、在庫帳の出庫製番を売上高表と比較して、当月分と来月分を選別する。
　各部品の手配数合計、入庫数合計、当月出庫数合計と来月分出庫数合計を求めて、月初めの
　在庫数を計算し表に記入する。又、当月分の出庫製番列と入庫済の手配列を削除する。
　未入庫の手配列、来月分の出庫製番列と残数列を残して残数再計算を行って、残数を最小在庫
　数判定して、不足なら手配処理を実行する。

[月末更新]ボタンマクロ

※[月末更新]ボタンを押すとMsgBoxで警告が出る。
　[OK]ボタンを押すと[月末更新]マクロが実行される。

[在庫標準部品入力.xlsm]の標準部品入力1のシート

※ [単価変更]フォームの[確定]ボタンを押すと[標準部品入力へ転記A10加工]マクロで
　標準部品入力シートの、加工単価が変更される。

●補足　ダブルクリックした時に動作をする方法
　ActiveSheet.OnDoubleClick="変更前保存"　　　' ダブルクリックした時に、[変更前保存]
　　　　　　　　　　　　　　　　　　　　　　　　' マクロを実行する。
　Call ダブルクリック取消　　　　　　　　　　　　' サブマクロの呼出。
　ActiveSheet.OnDoubleClick=""　　　　　　　　' ダブルクリックの使用取消

●フォーム右上の [X] で、フォームを閉じられないようにする方法。
　※これは、終了ボタンには protect が設定してあるので、押すように規定している。
　　[X] を押すと警告が表示される。

　[不可プログラム]

●[単価変更]の操作

[価格変更]ボタンマクロ

単価変更.Show vbModeless
※vbModeless が無いと、フォームが表示された時に、ダブルクリックが
　出来ない。

[単価変更]フォーム

※単価を変更したいセルをダブルクリックすると、変更前のアドレスと単価が
　保存される。セルの単価を変更して、[単価変更]ボタンを押すと、変更後の
　アドレスと単価が保存される。これは、[元に戻す]ボタンで変更前に戻す為で
　ある。[確定]ボタンを押すまでは、何度でも元に戻せるが、[確定]ボタンを
　押すと戻せない。[確定]すると、Eドライブの[在庫標準部品入力.xlsm]の
　単価が変更されるからである。

ActiveSheet.OnDoubleClick="変更前保存"
　※ダブルクリックした時に、[変更前保存]マクロが実行される。

[変更前保存]マクロ

GR=ActiveCell.Address　　　　' ダブルクリックしたセルの、アドレスをGRに代入
DT=Range(GR)　　　　　　　　' Range(GR)でセルの値を取得できる。
単価変更.前行番.Text=GR　　　' 変更を戻せるように、変更前のアドレスを保存する。
単価変更.前値.Text=DT　　　　' 単価を保存する。

●手配取消の操作

[手配取消]ボタンマクロ

If Sheets("P1").Cells(7, endHL) <> "手配数" Then
　MsgBox "列が右端でないので消去できません", vbOKOnly
　Exit Sub
End If
Sheets("P1").Columns(endHL).Delete

※手配数は、最右列で無ければ消去出来ない。それは手配して、何日も経過
　してから手配を取り消しても、すでに部品製作が着手されているかもしれない
　ので、取り消すことはできない。だから、最右列にある場合だけ取消ができる。

[設定]シート

endHL=Sheets("設定").Range("B4")－2　　　　　　　　　　' 最終入庫列
endZL=Sheets("設定").Range("B4")－1　　　　　　　　　　' 最終残数列
If Sheets("P1").Cells(7, endHL) <> "入庫数" Then
　MsgBox "列が右端でないので消去できません", vbOKOnly
　Exit Sub
End If

※設定シートのB4は、表の最右データ列の一つ右側の列位置を示しており、その30
　からマイナス2の位置、28列目の7行目が「入庫数」になっているかを確認して、
　継続するのかを決めている。「入庫数」でないとMsgBoxで「消去できない」と
　警告し、ExitSubでプログラムを終了している。
　最右列なので、列を削除しても全体の数値には影響しない。

Sheets("P1").Columns(endZL).Delete　　　' 右端列なら、入庫列を削除する。
Sheets("P1").Columns(endHL).Delete　　　' 右端列なら、残数列を削除する。

Worksheets("P1").Columns(N+1).Delete　　　' このコードで列 N+1 が消去される。
Worksheets("P1").Columns(N).Delete 　　　　' このコードで列 N が消去される。

※Private Sub CB2_Click( ) 確認コードには、N値の入力はありませんが、
　実際には N には数値があります。これは、Private Sub CB1_Click( )の列消去コードの
　上部で Dim N As Integer と定義していたから、[列消去]で入力した値が入ったのです。

※このようにモジュールの宣言セクションで Dim を使用して変数宣言をすると、その
　モジュール番号内のすべてのマクロで、N に入力した値が使用できます。

(中間列にある製番の取消)

中間列にある製番列も、同じように消去できます。

Call 設定値確認記入　　　' 列データを修正して[設定]シートに記入する。
※列データはすべて[設定]シートに記入されている。列の追加や削除による変化を
　最左列から再確認して、[設定]シートに記入するサブマクロである。

[設定]シート

●出庫取消の操作

※出庫取消は、すでに出庫してしまった製番出庫を、取り消す作業である。
　取り消す列は、どこにあっても取り消すことが出来る。

[出庫取消]ボタンマクロ

[列の消去]フォーム

使用列番号と製番を入力する。
[列消去]ボタンを押すと、残数列番号が自動で入ります。

N=TextBox1.Text　　　　' 製番入力値 = N
TextBox2.Text=N+1 　　 ' 残数列番号 = N+1

Call 出庫入力　　　' [出庫入力]マクロを実行する。マクロは、前記述の個別出庫を参照。

※このマクロを実行することによって、使用列の部品各行に一台分の数量が入力される。
　又、オプションのチェックボックスに、チェックが付いているとそのオプション部品にも
　数値が記入される。

出庫製番入力.Show vbModeless　　　' 出庫製番入力フォームが移動可能状態で表示される。

(出庫製番入力フォームの表示)

製番と納期を入力して[入力完了]を押す。

[入力完了]マクロ

(実行マクロは、前述参照)