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

Excel
スポンサーリンク

今回はExcelマクロを使った仕入管理システムについて、そのコードとプロシージャについて解説していきたいと思います。
仕入管理システムの作成ですが、顧客管理、売上管理、在庫管理への転用など汎用性の高いコードになっておりますので、ご自身が業務等に使えそうな部分だけでも是非参考にしていただけたらと思います。
(画面設定やブックの保護・解除などシステムとして動作制限をかけるコードについては省略しています。)
VBAコード全体は基本的なものが中心ですので、ご安心ください。それではまいります。

スポンサーリンク
スポンサーリンク

概要(完成形)

完成図①
完成図②

シートは3つあります。

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

ユーザーフォームの解説「マスタ登録フォーム」

ユーザーフォームを使用して「商品マスタ」シート上に商品マスタデータを作成していきます。

ユーザーフォーム

こういったシステムを作成する際に、ユーザーフォームは非常に効果的です。

理由としてはユーザーがワークシートに自由にデータ入力することを制限できるからです。つまり、エラーが起こる確率を減らせます。

「挿入」タブ→「ユーザーフォーム」から作成します。

ユーザーフォーム

ユーザーフォームのわかりやすい説明

これから説明するユーザーフォームの中のコマンドボタン、テキストボックス、コンボボックスなどのオブジェクト(コントロール)にVBAコードを仕込んで動かしていくイメージです。         (例)コマンドボタンをクリックしたら、文字が転記される。                     (例)テキストボックス内の数値を変更したら、自動的に計算される。

ユーザーフォームのオブジェクト名

ユーザーフォームを使用する際に、まず行ってほしいことはオブジェクト名の変更です。必要であればCaption(キャプション)の変更です。オブジェクト名を変更することで、このユーザーフォームがどんな役割をするのかわかりやすくなります。
今回は「マスタ登録フォーム」という名前にしています。キャプションは商品マスタ登録です。キャプションはユーザーフォームの左上の名前です。

オブジェクト名変更

フォームコントロール

フォームコントロール

ユーザーフォームを作成したら、ツールボックスを使って各コントロールを配置していきます。

コントロールとはわかりやすく言えば、ボタンとかテキストボックス(入力ボックス)などです。

①②④テキストボックス

①②④はテキストボックスです。四角の中にデータを入力できます。一般的な入力欄です。

コントロールを配置したら、各コントロールのオブジェクト名を変更します。その理由はユーザーフォームのそれと同じです。

オブジェクト名変更

③コンボボックス

③はコンボボックスです。図のような使い方ができます。

コンボボックス

コンボボックスへの値の追加方法についてもっと詳しく知りたい方はこちらの記事で↓

VBAユーザーフォームとコンボボックス
ExcelVBAのユーザーフォームにおけるコンボボックスの値の追加方法と初期値の設定方法の基本について紹介しています。

コンボボックスの値を取得する方法についてもっと詳しく知りたい方はこちらの記事で↓

ユーザーフォームとコンボボックスの値の取得
コンボボックスの値を取得するためのTextプロパティ、Valueプロパティ、Listプロパティ/ListIndexプロパティを紹介します。

⑤⑥コマンドボタン

ボタンにVBAコードを入力しておいて、クリックすると実行します。
各コントロールにどんな動作を与えたら、プロシージャが実行するかは図のドロップダウンリストで選びます。
左がオブジェクト名(コントロール名)、右が動作(イベント)です。

Clickを選択しているので、ボタンをクリックすればプロシージャが動くという仕組みです。

登録ボタン
スポンサーリンク

コードの解説「マスタ登録フォーム」

'===登録ボタンをクリックした時作動===
Private Sub cb登録_Click()
    Dim max As Long
    If txtバーコード.Text <> "" And txt商品名.Text <> "" And cb取引先.Text <> "" And txt単価.Text <> "" And cb取引先.Text <> "" Then '入力欄に空欄がなければ
        If MsgBox("商品登録しますか?", vbYesNo + vbQuestion) <> vbYes Then Exit Sub '確認メッセージボックス
    Else
        If MsgBox("空欄があります。登録しますか?", vbYesNo + vbQuestion) <> vbYes Then Exit Sub '空欄ありの場合の確認メッセージボックス
    End If
    max = Sheets("商品マスタ").Cells(Sheets("商品マスタ").Rows.Count, 2).End(xlUp).Row + 1 '最終行
    With Sheets("商品マスタ")
        .Cells(max, 1).Value = txtバーコード.Text 'バーコード
        .Cells(max, 2).Value = txt商品名.Text '商品名
        .Cells(max, 3).Value = txt単価.Text '単価
        .Cells(max, 4).Value = cb取引先.Text '取引先名
    End With
End Sub
'===閉じるボタンをクリックした時作動===
Private Sub cb閉じる_Click()
    Unload マスタ登録フォーム
End Sub
'===ユーザーフォームの初期設定===
Private Sub UserForm_Initialize()
    cb取引先.RowSource = "商品マスタ!R2:R7"
End Sub

各コントロールのプロパティ

With Sheets("商品マスタ")
        .Cells(max, 1).Value = txtバーコード.Text 'バーコード入力
        .Cells(max, 2).Value = txt商品名.Text '商品名入力
        .Cells(max, 3).Value = txt単価.Text '単価入力
        .Cells(max, 4).Value = cb取引先.Text '取引先名入力
    End With

上記の部分がユーザーフォームのコントロールの中のデータをワークシートへ入力するコードです。

Textプロパティ

テキストボックスに入っている値を取得します。

つまり取得したテキストをワークシートへ転記しています。

RowSourceプロパティ

指定したセル範囲のデータをリスト表示します。

Private Sub UserForm_Initialize()
    cb取引先.RowSource = "商品マスタ!R2:R7"'商品マスタシートのR2:R7を参照
End Sub
「商品マスタ」シート

Initializeイベント

イニシャライズとは、ユーザーフォームを立ち上げた時に実行されるプロシージャです。

つまり、今回の場合は、立ち上げた時点でコンボボックスにリストが設定されます。

スポンサーリンク

イベントプロシージャ(商品マスタデータ削除)

イベント
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    Dim max As Long
    max = ActiveSheet.Cells(ActiveSheet.Rows.Count, 2).End(xlUp).Row
    If Target.Column = 2 And Target.Row <= max And Target.Value <> "" Then'2列最終行までの範囲を右クリックすると
        If MsgBox("この商品データを削除します。", vbOKCancel) = vbOK Then
            Target.Offset(, -1).Resize(1, 4).Delete xlShiftUp
        End If
        Cancel = True
    End If
End Sub

こちらはユーザーフォームではなくイベントプロシージャです。

イベントプロシージャとは、選択されたワークシートやセルで特定の操作が行われた時に実行されます。

今回は、「商品マスタ」シートの特定の範囲を右クリックするとデータ削除が実行されます。

BeforeRightClickイベント・・・ワークシートを右クリックした場合に実行

参考書籍】

ExcelVBA学習のおすすめの本5選
筆者オススメの「これは秀逸!」「これは1冊持っとくべき」と思えるExcelVBAの本を紹介しています。ExcelVBAの書籍選びで、どれを選んでよいか迷っておられる方に参考にしていただきたいです。
スポンサーリンク

まとめ

今回は「マクロで作る管理システム」の商品マスタの登録について紹介しました。

次回は商品マスタデータを参照して実際に仕入が発生した時のデータベースへの入力についてせつめいします。お楽しみに!!

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

コメント

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