Excel：列中に特定の文字列を表示するセルが出たらBeep音を鳴らす

Question

商品の品番の確認作業にエクセルを使っています。
作業の効率化にエクセルVBAを使いたいのですが、うまくいきません。

具体的な使用目的は以下の通りです。
　納品された商品には製造メーカーのバーコードがついています。それをエクセルに読み込んで予定通りの商品であるか確認作業を行います。
　まず、エクセルシートのA列にメーカーコードを入力しておきます、B列のセルをアクティブにした状態でメーカーのバーコードを読み取ります。するとセルにメーカーコードが自動入力されます。この文字列がA列の文字列と一致した場合にはC列のセルにOKの文字が出るように設定しています。コードが一致しない場合はNGが出ます。
　現在はOKかNGかを目で確認しているのですが、商品数が多いので、NGの場合にBeep音が出るようになっていれば作業が早くなります。

　過去の質問例を調べて、VBAのAMI関数を使うとBeep音を鳴らせることはわかりました。たとえば、セルC1にNGと表示された場合にBeep音を鳴らす、というところまではできたのですが、列CのいずれかのセルにNGと表示された場合にBeep音を鳴らす、という設定ができません。

　わかる方アドバイスお願いします。

　ちなみに、セルC1にNGと表示された場合にBeep音を鳴らすには以下のように設定しました。

標準モジュール
Public Declare Function Beep Lib "kernel32" (ByVal dwFreq As Long, ByVal dwDuration As Long) As Long
シートのモジュール
Private Sub Worksheet_Change(ByVal Target As Range)
If Range("C1") = "NG" Then
Call Beep(2000, 500)
End If
End Sub

Wendy02 · Accepted Answer

こんにちは。

>VBAのAMI関数を使うとBeep音を鳴らせる　
Win32API関数です。

別に、Win32API関数を使わなくても、単に、組み込み関数のBeep だけでも良いと思います。

本来は、NGと出てくるということは、データがインポートされるのですから、それを監視したほうが早いのです。Excelには、専用コマンドもあります。しかし、それは、古いメソッドなので、監視できるかどうかは分かりません。私の知っている範囲では、半分ぐらいは成功していません。

> Worksheet_Change(ByVal Target As Range)

今のところ、バーコードの出力で、Change イベントが、発生するか分かりません。Microsoft の古いサポートでは、この方法が書いてありましたが、DDEやOLEでは、反応しなかったような気がします。Change イベントで反応するのでしたら、以下のように、の入力列の Target.Value を判定に使えばよいです。

私のコンセプトは、NGは一つとは限らない、という前提で作られています。NGという表示を置いたまま、次に移って検査しても、可能なようにできています。

もし、ひとつだけを探すなら、Match関数や、CountIf 関数自体を、マクロの中に使用しても良いと思います。


注意：以下の二つのマクロは、二者択一ですから、必ず、片方だけにしてください。
'-------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 3 Then Exit Sub
If Target.Count > 1 Then Exit Sub
If Target.Value = "" Then Exit Sub
　　If StrConv(Target.Value, vbUpperCase) Like "NG*" Then
　　　　Call Beep(2000, 500)
　　End If
End Sub

'=========================================

上記のイベント・ドリブン型のマクロでは、反応がない場合には、このようにします。

'-------------------------------------------

'標準モジュール
'Win32API関数の下に、

Public gCnt As Long　'と入れて、

'-------------------------------------------

#1さんの回答のように、該当するシートの　E1 のセルに、=COUNTIF(C:C,"NG") と入れます。
そしてシートモジュールに以下のように貼りつけます。
'-------------------------------------------

Private Sub Worksheet_Calculate()
　　'セル"E1" に　=COUNTIF(C:C,"NG")　という数式が入っていること
　　Dim iCnt As Long
　　iCnt = Range("E1").Value
　　If iCnt > gCnt Then
　　　　Call Beep(2000, 500)
　　End If
　　gCnt = iCnt
End Sub

'----------------------------------------

なお、二番目の場合は、ブックを開いて、機器を使用した時に、最初の一回目に抜けが発生するかもしれません。

Wendy02 · Answer

#5で、うっかり書き忘れましたが、
Microsoft 側が提示している (Excel 97時代）
>Sub Auto_Open()
>　Worksheets("Sheet1").OnData = "SoundMessage"

の方法は、実際のデータが入ってこないと調べることが出来ません。DDEとOLEのデータが入る時に、イベントが発生すると言っていますが、私がAccess からDDEでエクスポートするマクロでは、イベントが発生していませんでした。

Wendy02 · Answer

こんばんは。

>手元に今バーコードリーダーがないので、試験できませんが、今度確認してみます
できたら、お願いします。英語版掲示板でも、間違った内容があったりして正確なものがないのです。それに、機種によって違うような気がします。昔は、種類がなくて、DDEのみだったので、問題はなかったのです。

以下のコードは、バーコードリーダーで、"NG"が出て反応するかどうかの問題があります。

Change イベントというのは、リターンコードなどで反応するのではないかと思うからです。そうすると、それを出力しないものは、Change イベントでは反応しません。

'-------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)

>イベント・ドリブン型のマクロをコピペして試してみたのですが、うまく動作しません。原因不明です。

イベントが二重になって登録されているとか、特に、問題は発生しないように思います。

これらは、除外条件です。
If Target.Column <> 3 Then Exit Sub　'C列以外
If Target.Count > 1 Then Exit Sub　'選択したセルが複数の場合
If Target.Value = "" Then Exit Sub　'セルが空の場合

チェックポイントとしては、

Private Sub Worksheet_Change(ByVal Target As Range)
この行の所にカーソルを置いて、F9 を押しますと、ブレークポイントと言って、行に色が付き、色が反転します。そこで、マクロが通ればマクロは止まり、その後に、F8を押して、ステップインで、その先に進んで行きます。そうして、マクロの反応や If分岐の状態が確認できます。

もし、まったく動かない場合は、Application.EnableEvents = False とかで抜け出たりしているは働きませんので、
Application.EnableEvents = True で、イベント可動状態にすると元に戻り動くようになります。

'-------------------------------------------

こちらのものは、バーコードリーダーで、仮に、リーターンコードが発生していなくても、反応します。DDE インポートでは、確認しています。

Private Sub Worksheet_Calculate()

>#2さんのやり方のようにカウントセルをシート上におかないように修正することはできるのでしょうか。

できるのですが、100分の1秒とかの範囲で、ワークシートの変化を監視する特殊なマクロが必要になります。人為的に無限ループを発生されるものです。今まで、このイベント・スタイルでは作ったことがありません。言い換えると、新しいイベントを作ることになるのですが、単なるワザだけで、Excelのアプリケーションとして負担が大きすぎるような気がしているのです。
'-------------------------------------------

これは、Microsoft 側が提示している方法です。しかし、これが上手く言ったという話は、今までで1件しかありません。

'標準モジュール
Sub Auto_Open()
　'シートを設定する（起動時には自動的に設定）
　Worksheets("Sheet1").OnData = "SoundMessage"
End Sub
'
Sub SoundMessage()
　　If ActiveCell.Column = 3 Then 'C列に限定
　　　　If StrConv(ActiveCell.Value, vbUpperCase) Like "NG*" Then
　　　　　　Call Beep(2000, 500)
　　　　End If
　　End If
End Sub

saneppie · Answer

VBAを今後も使うのであればと思い、下記の方法も紹介します。

Private Sub Worksheet_Change(ByVal Target As Range)
    For i = 1 To Range("C1").End(xlDown).Row
        If Cells(i, 3).Value = "NG" Then
            Call Beep(2000, 500)
            Exit For
        End If
    Next i
End Sub

ようは、C列の1行目から最終行までを一つずつチェックして、
「NG」の行が最初に見つかった時点で音を鳴らして、チェック終了…ということです。

ただこれは、「NG」が無いときはムダに最終行までチェックします。
つまりExcel2003までなら65536行、
Excel2007なら104万8576行を1行ずつ確認する…という時間がかかってしまいます。
「NG」があればループしている途中でチェックを切り上げてくれるので時間がかからないのですが。

なので、他のご回答のように列単位でNGがあるのかどうかを確認するのが今回は良いと思います。

KURUMITO · Answer

If Range("C1") = "NG" Then
を次のようにしてはどうでしょう。
If Target.Column = 3 And WorksheetFunction.CountIf(Range("C:C"), "NG") > 0 Then

DreamyCat · Answer

"どこかのセル"でC列のNGの有無をカウントし
=COUNTIF(C1:C200,"NG")

ご提示の部分を少し変えてみたらいかがでしょう。
If Range("どこかのセル").value => 1 Then
Call Beep(2000, 500)

Excel：列中に特定の文字列を表示するセルが出たらBeep音を鳴らす

こんにちは。

#5で、うっかり書き忘れましたが、

こんばんは。

VBAを今後も使うのであればと思い、下記の方法も紹介します。

If Range("C1") = "NG" Then

"どこかのセル"でC列のNGの有無をカウントし

似たような質問が見つかりました

このQ&Aを見た人はこんなQ&Aも見ています

関連するカテゴリからQ&Aを探す

このQ&Aを見た人がよく見るQ&A

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング