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

質問

商品の品番の確認作業にエクセルを使っています。
作業の効率化にエクセル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

回答No.4

Wendy02 · ベストアンサー

こんにちは。

>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

'----------------------------------------

なお、二番目の場合は、ブックを開いて、機器を使用した時に、最初の一回目に抜けが発生するかもしれません。

お礼

>バーコードの出力で、Change イベントが、発生するか分かりません
手元に今バーコードリーダーがないので、試験できませんが、今度確認してみます

イベント・ドリブン型のマクロをコピペして試してみたのですが、うまく動作しません。原因不明です。

二つ目のマクロはうまくいきました。
Wendy02さんが言うように、#1さんと#2さんの方法だと、NGを保留して次に進んだ場合、OKの場合でもBeep音がなってしまいますが、このマクロだと鳴らない点がすごく優れていると思います。
ここまでくると私には複雑すぎてネットで調べても文章の意味がが理解できないのですが、#2さんのやり方のようにカウントセルをシート上におかないように修正することはできるのでしょうか。

回答No.6

Wendy02

#5で、うっかり書き忘れましたが、
Microsoft 側が提示している (Excel 97時代）
>Sub Auto_Open()
>　Worksheets("Sheet1").OnData = "SoundMessage"

の方法は、実際のデータが入ってこないと調べることが出来ません。DDEとOLEのデータが入る時に、イベントが発生すると言っていますが、私がAccess からDDEでエクスポートするマクロでは、イベントが発生していませんでした。

回答No.5

Wendy02

こんばんは。

>手元に今バーコードリーダーがないので、試験できませんが、今度確認してみます
できたら、お願いします。英語版掲示板でも、間違った内容があったりして正確なものがないのです。それに、機種によって違うような気がします。昔は、種類がなくて、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

お礼

返信が遅くなって申し訳ありませんでした。
自宅で手入力による作動を確認し、職場に持ち込んで試そうとしたのですが、引数の数が不正でとまってしまいました。オフィスのバージョンが違うからでしょうか。理由はわかりません。
今回、色々な方にご回答いただいたのですが、私の力不足で仕事に生かすことはできませんでした。
VBAはなかなか難しいですね。
いつか基本から学んでみたいと思います。

皆様ありがとうございました。

回答No.3

saneppie

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があるのかどうかを確認するのが今回は良いと思います。

お礼

ありがとうございます。
今後の参考にさせていただきます。

回答No.2

KURUMITO

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

お礼

ありがとうございます。
ワークシートにカウント用のセルをつくる必要がないので他の人が誤ってシートを変更してしまう危険がない点が良いです。
初めに、手入力でテストしたところ音が鳴りませんでした。
バーコードが読み込まれるのはB列で(自宅でテストしているときはB列に手入力します)C列には、=IF(A1=B1,"OK","NG")という関数が入っているので、
Target. Column=3をTarget. Column=2に変更したところうまくいくようになりました。

回答No.1

DreamyCat

"どこかのセル"でC列のNGの有無をカウントし
=COUNTIF(C1:C200,"NG")

ご提示の部分を少し変えてみたらいかがでしょう。
If Range("どこかのセル").value => 1 Then
Call Beep(2000, 500)

お礼

大変わかりやすい回答をありがとうございます。
自宅にバーコードリーダーがないので最終確認はできませんが、手入力だと目的の動作が起きることを確認できました。
#4さんの回答を参考にC列のNGの有無は=COUNTIF(C:C,"NG")でカウントすることにしました。