バーコードで在庫調べする方法。エクセルVBAのシートイベントを使う

codeeyecatcha

こんにちは、じゅんぱ店長(@junpa33)です。

今回からしばらく棚卸しに使える「雑誌と書籍の在庫リスト」作成のためのVBAコードの説明をしていきたいと思います。

 

今回は「コード入力表」についてです。

基本的には、バーコードリーダーを使って書誌のデータを読み取ることを前提とした設定です。

(この後、別のシート設定の記事で、他から持ってきた場合のバーコードデータもうまく使える様な仕様にしています。)

 

目標として、Enterキー以外は出来るだけキーボードを使わなくて良い様にしていきます。

 

「雑誌と書籍の在庫リスト」の完成形はこの記事をご覧ください。

 

【棚卸し】無料入手!在庫管理の「雑誌と書籍の在庫リスト」DLと取説

 

雑誌と書籍の在庫リスト作成に関連した記事はこちらになります。

「棚卸表作成」の記事一覧を開く

 

バーコードを読み込む「コード入力表」の設計

cel001

バーコードリーダーは基本の設定でコードの読み取り時には、”数値”+”Enter”をPCに送信します。これで自動的にアクティブセルに数値が入力され、次のセルがアクティブになります。

この様にデフォルトでは、読み取るごとに(セルに数値が入るごとに)行が下がっていきます。

けれども

ISBNの書籍コードの場合は、バーコードが、書籍の固有のコードと価格を記述したコードの2段となっています。

なので

コードをエクセルVBAで利用するには、書誌の固有のコードと価格を表すコードを分けて表示させますが、紐づけできるように表示させる必要があります。

(デフォルトの設定のままですと同じ列に2種のコードが混ざった状態になってしまいます。)

 

ですので、このシートの設定では

ISBNの書籍コードの場合、バーコードの読み取り後のセルの移動を

デフォルトの「常に下の行」から「条件が合えば横に移動」に変化させるようにしています。

 

バーコード読み込み時のシートイベントVBAコード

cel002

それでは説明していきます。

「書誌在庫表.xlsm」の作成

 

まずは、「書誌在庫表.xlsm」という名前のBOOKを作成します。

新規のエクセルBOOKを立ち上げて、名前を「書誌在庫表.xlsm」で保存してください。

「書誌在庫表.xlsm」を開きます。

最初に設定しておくSheet名は、Sheet1を「コード入力表」、Sheet2を「データコピー」、Sheet3を「作業シート」としてください。

 

シートイベントVBAコードをつくる

項目表示(シートを開いたとき作動)

最初に列のタイトル(項目)を記入されるようにしましょう。

いつものようにVBE(ビジュアルベーシックエディタ)を開いてください。

MEMO

念のため開き方はこちら↓で確認してください。

「VBE(ビジュアルベーシックエディター)を起動する」

こちらも参考になります。

エクセルVBA 始めての起動。VBEの立ち上げ、保存と終了

 

今回は「Sheet1(コード入力表)」のシートモジュールに記述していきます。

(プロジェクトエクスプローラーの「Sheet1(コード入力表)」をダブルクリックしてください。)

それぞれの1行目で、A列に「書誌コードNO」、B列に「価格コードNO」、E列に「項目修正スイッチ」と表示しますシートを開いたときに作動します。

コードはこのようになります。

コード①

Private Sub Worksheet_Activate()

    Range("A1") = "書誌コードNO"
    Range("B1") = "価格コードNO"
    Range("E1") = "項目修正スイッチ"
    With Range("A:E")
        .EntireColumn.AutoFit
    End With
    Range("C1").Select

End Sub

 

アクティブセルの動きをコントロール(シートに変化があった時起動)

 

codenyu001a

 

シートを開いたときにはセルはC1の位置にあります。また、データの入力を一時的にでも終了すれば、セル位置はC1に戻ります。

A列、B列以外のセルをアクティブにした時には「A列のセルをアクティブにしてください。」のメッセージを表示します。A列のセルからのデータ入力のスタートとなります。

E列は”入力したデータの修正”のためのスイッチとなる列になっています。メッセージに従って修正データを入力していきます。データ修正したいセルと同じ行のE列のセルを指定する必要があります。

 

 

コード②

MEMO

With ~ End With の使い方はこちらを参考にしてください。

With~End Withの使い方。VBAコードを簡潔に記述する

メッセージボックスの使い方はこちらを参考にしてください。

メッセージボックス MsgBox実際の使い方を最速に理解

インプットボックスの使い方はこちらを参考にしてください。

2つのインプットボックス。関数とメソッド、特徴を生かした使い分け

Gotoステートメントとの使い方こちらを参考にしてください。

Gotoステートメントの利用法を最速に理解。毒コードと薬コード

最終行の取得方法についてはこちらを参考にしてください。

データ入力済セルの最終行番号を取得するVBAコード

 

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim R As Long
Dim LARow As Long
Dim LARowb As Long
Dim p As Variant, q As Variant, w As Variant
Dim AD As Variant
Dim x As Integer, y As Integer

With Range("A:B")
    .NumberFormatLocal = "@"
    .EntireColumn.AutoFit
End With

If Target.Column <> 2 And Target.Column <> 1 Then
    R = MsgBox("A列のセルをアクティブにしてください。", vbOKOnly + vbExclamation)
    If Target.Column = 5 Then
        q = Application.inputbox(Prompt:="A列の修正コードを入力。" & vbCrLf & "B列修正または間違いの場合はキャンセルを" & vbCrLf & "空欄入力は9999を", Type:=2)
            If q = False Or q = "" Then
                w = Application.inputbox(Prompt:="B列の修正コードを入力。" & vbCrLf & "間違いの場合はキャンセルを" & vbCrLf & "空欄入力は9999を", Type:=2)
                    If w = False Then
                        GoTo STEP_E
                    ElseIf w = "" Then
                        GoTo STEP_E
                    ElseIf w = 9999 Then
                        Cells(Target.Row, 2) = vbNullString
                    Else
                        Cells(Target.Row, 2) = w
                    End If
            ElseIf q = 9999 Then
                        Cells(Target.Row, 1) = vbNullString
            Else
                Cells(Target.Row, 1) = q
            End If
    End If
                
    If R = vbOK Then
        GoTo STEP_E
    End If
ElseIf Target.Column = 2 Then
        If Target.Value <> "" Then
            Target.Offset(1, -1).Select
        End If
Else
    LARow = Range("A1").CurrentRegion.Rows.Count
    LARowb = Cells(1, 1).End(xlDown).Row

    Range(Cells(1, 5), Cells(LARow, 5)).Interior.Color = RGB(230, 230, 230)
    
    If LARow = 1 Then
        Range("A2").Select
    Else
        If LARowb < LARow Then
            Range("A" & LARowb + 1).Select
        Else
            Range("A" & LARow + 1).Select
        End If
    End If
End If

 

A列のいずれかのセルやB列で既にデータが入力されているセルをアクティブにした時には、次にデータ入力可能なA列のセルが自動的にアクティブになります。

ISBNコードであった場合は、価格コードを入力するために自動的に同行のB列にセルが移動します。

 

コード③

MEMO

インプットボックスの使い方はこちらを参考にしてください。

2つのインプットボックス。関数とメソッド、特徴を生かした使い分け

Gotoステートメントとの使い方こちらを参考にしてください。

Gotoステートメントの利用法を最速に理解。毒コードと薬コード

LTrim関数についてはこちらを参考にしてください。

Trim関数・RTrim関数・LTrim関数 余分なスペースを取り除く関数の利用法

Left関数の使い方についてはこちらを参考にしてください。

Mid関数・Right関数・Left関数は文字列操作の基本。使用例で解説します

 

AD = Target.Address(False, False)
  x = Target.Row
  y = Target.Column
  
p = Application.inputbox(Prompt:="コードを入力。" & vbCrLf & "終了の場合はキャンセルを", Type:=2)
If p = False Then
    GoTo STEP_E
ElseIf p = "" Then
    GoTo STEP_E
Else
    p = LTrim(p)
    Range(AD).Value = p
    If Left(p, 3) = 978 Then
        If Target.Column = 1 Then
            Cells(x, y + 1).Select
        Else
            Cells(x + 1, y - 1).Select
        End If
    ElseIf Left(p, 3) = "" Then
        GoTo STEP_E
    Else
        If Target.Column = 1 Then
            Cells(x + 1, y).Select
        Else
            Cells(x + 1, y - 1).Select
        End If
    End If
End If
STEP_E:
    Cells(1, 3).Select
End Sub

 

データ入力終了後の操作(シートから移動した時に起動)

データ入力が終わって次の作業に移る(違うシートへ移動)時に、

入力したデータを「データコピー」シートに移します。(「コード入力表」のVBAは引き継がれません。)

この後、ユーザーフォームの「コントロールパネル」で「バーコードモード」ボタンを設置します。

そして入力したデータを、「データコピー」シートへ引き渡しする・しないを、ボタンのON・OFFでコントロールします。

そのためのコードのトリガーのVBAをここで埋め込みます。本体はModule1に記述します。

 

コード④

Private Sub Worksheet_Deactivate()
Module1.バーコード
    
End Sub

 

  バーコードで在庫調べする方法のまとめ

 

バーコードリーダを利用するこのシートでのVBAの起動については、トリガーとなるボタンのクリックなどでは非常に使いづらくなります。

シートの変化に対してそれがVBA起動のトリガーとなることで、連続してバーコードレーダーでの操作が可能になります。

シートの変化(イベント)をトリガーとすると、結構下手をすると無限ループに嵌ってしまうことがありますのでコードミスなどしないよう十分注意が必要です。

次回は、「データコピー」シートの作成について書いていきたいと思います。

 

次の記事を読む

雑誌と書籍 在庫表作成のエクセルVBAを解説。ISBNコード加工編

 

エクセルVBAの勉強を始めましょう

 

エクセルVBAは、プログラムコードです。

第一印象、「プログラムコード!」 非常に取っ付き難いでしょう。

でも、初めての参考書で概念が変わります。(変わらせない参考書もあります。)

学習素材に何を選ぶかが大切です。

 

vbastudyeyecatch002 エクセルVBAを独学で習得する!ために大切な7つのポイントを解説します

 

エクセルVBAを独習するのに参考書は欠かせません。 参考書選びは自分に合った「相棒」にできるものを選んでいきたいです。
エクセルVBAの独習でおすすめ参考書を7冊選ぶ。良書との出会いは大切です

 

エクセルVBAの独習には動画学習という方法もあります。 目と耳両方を使って学習することでさらに勉強効率を上げることもできると思います。
エクセルVBA初級者がUdemyで動画学習する講座おすすめ5選と無料講座の上手な使い方。

 

Excelマクロ&VBA超入門 今すぐ使えるかんたんぜったいデキます!
エクセルVBAを初めて勉強するときに効く
vbastudy016a
vbastudy017a

電子書籍版「Excelマクロ&VBA超入門」をamazonで見てみる

(著者)井上香緒里
(出版社)技術評論社
(税込価格)1,738円(本体1,580円+税)

マクロとVBAについて学んでみたいけれど,いまひとつ自信がないという方のために,ひとつひとつの操作を丁寧に解説する1冊目の教科書です。
エクセルVBAの「い・ろ・は の い」から説明していますので、安心して取り組める参考書です。
例題として、
  • 「データ消去」「シートのコピー」「メッセージボックス」の機能を持つお小遣い帳
  • 「入力フォーム」の機能を持った歩数表
2つの題材ファイルを作成しながら,マクロとVBAの基本を学習していきます。

 

エクセルVBAを使いだして、始めのうちに知っておきたい内容を纏めています。

「VBA最速理解」の記事一覧を開く

 

今回の記事はここまでです。   最後までご覧いただき有難うございました。

<記事内容についての告知>VBAコードの記述記事においては、その記述には細心の注意をしたつもりですが、掲載のVBAコードは動作を保証するものではりません。 あくまでVBAの情報の一例として掲載しています。 掲載のVBAコードのご使用は、自己責任でご判断ください。 万一データ破損等の損害が発生しても当方では責任は負いません。