Sleeper: Defining a range to make a Sound When a Cell is Changed to a Certain Value

[Peter_B]

I have a program that sends real time measurement data to excel (a row of data is added every 3 minutes, the data is in colums C to G). When the measured results are above a user defined value (the user can set this value in cell A12) then an alarm (playing a .wav file) should be triggered.

I found the following code on this VBA express site:

 
Option Explicit 
 
Private Declare Function sndPlaySound32 Lib "winmm.dll" _ 
Alias "sndPlaySoundA" (ByVal lpszSoundName _ 
As String, ByVal uFlags As Long) As Long 
 
Private Sub Worksheet_Change(ByVal Target As Range) 
Dim Cell As Range 
Dim CheckRange As Range 
Dim PlaySound As Boolean 
Set CheckRange = Range("C:G") 
For Each Cell In CheckRange 
If Cell.Value > Range("A12") Then 
PlaySound = True 
End If 
Next 
If PlaySound Then 
Call sndPlaySound32("C:\alarm_clock_1.wav", 1) 
End If 
End Sub

It works perfectly but my problem is that each time new data (a new row is added at the bottom) is added all the 'older' data in the range is also re-evaluated.
Example: The alarm level (value in the A12 cell) is set to 10. As data is streamed into my excel sheet an alarm will be triggered when, for example, cell C10 gets a value of 12. (so far so good) from this point on however the alarm will be triggered each time new data is received as cell C10 is still =12.

Is there a way to avoid getting an alarm from the same cell over and over?

The only option I could come up with was to set the range ( Set CheckRange = Range("C:G") ) to only look at the last row that contains data.
In my excel sheet the column A contains a time stamp each time data is added. Columns C to G do not receive data each time so only colum A can tell me which row is the last one that contains data.

I'm not sure on how to implement this however. Or are there better / easier ways to do this?

One last thing that one may need to know: all of my empty cells in colums A to G contain NA()

Thanks for all your help

Edit Lucas: VBA tags added to code

[lucas]

Maybe:

 
Dim checkrange As Range
Set checkrange = Cells(Rows.Count, 3).End(xlUp)

PS. If you select your code when posting and hit the vba button it will be formatted for the forum.

[Kenneth Hobs]

Try:
[VCode]Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 3 Then Exit Sub
sndPlaySound32 "C:\alarm_clock_1.wav", 1
End Sub
[/Code]

[GTO]

Greetings Peter,

If I read your question correctly (which is presuming a lot for me somedays), then this example presumes that any data entered into C:G, is entered first; that is before the time-hack.

If the time is entered first, I would think you'll need a slight delay, so that once the code determines a change was made in Col A, it waits a second to check the offset cells.

Option Explicit
Private Declare Function sndPlaySound32 Lib "winmm.dll" _
Alias "sndPlaySoundA" (ByVal lpszSoundName _
As String, ByVal uFlags As Long) As Long
'// Change to suit.                                                                     //
Const sPath As String = "C:\Documents and Settings\stumpm\Desktop\" & _
                         "Desktop Holder\New Folder\"
Const sWav As String = "ltdd_024.wav"

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rCell As Range
    Dim PlaySound As Boolean
    Dim bolSurpassed As Boolean
If Not Application.Intersect(Range("A:A"), Target) Is Nothing _
    And Not Target.Address = Range("A12").Address _
    And Not Selection.Cells.Count > 1 Then
'NOTE:  May need delay here, if time-stamp is entered first...
For Each rCell In Range(Cells(Target.Row, "C"), Cells(Target.Row, "G"))
            If rCell.Value > Range("A12") Then
                bolSurpassed = True
                Exit For
            End If
        Next
If bolSurpassed Then Call sndPlaySound32(sPath & sWav, 1)
        bolSurpassed = False
    End If
End Sub

The zip is just the wav, cuz I thought it funny...

Hope this helps,

Mark

[Peter_B]

I have been able to get the alarms working with the following code:

 
Option Explicit

Private Declare Function sndPlaySound32 Lib "winmm.dll" _
Alias "sndPlaySoundA" (ByVal lpszSoundName _
As String, ByVal uFlags As Long) As Long
 
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell            As Range
    Dim checkrange      As Range
    Dim PlaySound       As Boolean
    Dim LastRow         As Long
If WorksheetFunction.CountA(Cells) > 0 Then
LastRow = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
MsgBox LastRow
End If
Set checkrange = Range("C" & LastRow, "G" & LastRow)
    For Each Cell In checkrange
        If Cell.Value > Range("S1") Then
            PlaySound = True
        End If
    Next
    If PlaySound Then
        Call sndPlaySound32("C:\alarm_clock_1.wav", 1)
    End If
End Sub

So the alarm level that the user can define is set in cell S1 (not in A12 as I wrote in my question yesterday)
With the lastrow method I am able to limit the range to the most recent data.

I still have a small problem however: all my cells in columns C to G are filled with a formula that I use to force excel to see the input measurement data as a mumber: IF((ISBLANK(Blad1!D9));NA();Blad1!D9*1)

The .Find method also sees these 'empty' NA() cells and considers them to be 'used'.

Is there a way to use the .Find method to only look at numbers or to tell it not to look at my cells containing NA()?

Thanks for your help

[GTO]

Greetings Peter,

First, a correction to my suggestion at #4, as indeed, the new data being added to the cells in Col's C:G must enter before the time hack gets put in whatever row of Col A. Sorry about that. Presuming that you tried this code, you must have the time-hack being entered first. Was it difficult to change the order of things so that the values are entered first and then the time-hack?

Reference your last code, I believe you have more than "one small problem" with it. Let's take a step back and look:
[vba]If WorksheetFunction.CountA(Cells) > 0 Then[/vba] Sorry, but given that the unqualified Cells returns all the cells on the sheet(!), this literally translates to: "If ANY cell (even just one) on the entire sheet has anything in it, then go ahead and... (find the last row)". Now of course this test will always be passed, but if it ever did return a FALSE, then 'LastRow' would not get a value assigned and a fatal error would occur at the line that Sets 'checkrange'.

Now before looking at your .Find, let's look at the formula you have plastered in all the cells in Columns A:G. Does the version of Excel you are using (presumably Dutch) use semi-colons as seperators? If so, then I'm pretty sure that this would be the equivalent of:

IF((ISBLANK(Sheet1!D9)),NA(),Sheet1!D9*1)
Or
IF(ISBLANK(Sheet1!D9),NA(),Sheet1!D9*1)
Or
IF(ISBLANK(Sheet1!D9),NA(),Sheet1!D9)

Why would you would multiply D9 by 1.00? At least as far as I can see, this leaves three possibilities: (1) A number in D9 results in the same number in all the cells, (2) nothing (blank) in D9 results in the Not Available error in all the cells, or (3) a text entry in D9 results in the Value error in all the cells. Now since you stated that all the cells in Columns A:G contain NA(), I presume you leave Sheet1 (Blad1) cell D9 blank. If the NA() is really necessary, why not just enter it?

Now with all the NA()s... In regards to your question as to .Find, I do not know a way for it to directly do this. You could of course use FindNext in a do...loop until it runs into a number, but this would seem both slow (Your .Find starts at Cell IV65536) and unnecessary. Hows about we try skipping the .Find part alltogether:

Option Explicit

Private Declare Function sndPlaySound32 Lib "winmm.dll" _
Alias "sndPlaySoundA" (ByVal lpszSoundName _
As String, ByVal uFlags As Long) As Long
'// Change to suit. //
Const sPath As String = "C:\Program Files\NetMeeting\"
Const sWav As String = "TestSnd.wav" '"ltdd_024.wav"
'

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rCell As Range
Dim bolSurpassed As Boolean
'// See if the cell(s) that got changed are in the range we care about, and that //
'// we're not just clearing a bunch of contents... //
If Not Application.Intersect(Range("C:G"), Target) Is Nothing _
And Not Selection.Cells.Count > 1 Then
'// This should work regardless of whether the data is being entered a cell at //
'// time, or as an array (all at once). //
For Each rCell In Target
If rCell.Value > Cells(1, 19) Then
'// If we find a value greater than that which is in S1, flag as true //
'// and exit the loop. //
bolSurpassed = True
Exit For
End If
Next
'// If we flagged true, play the sound. //
If bolSurpassed Then Call sndPlaySound32(sPath & sWav, 1)
End If
End Sub

To test, you could paste the below in a standard module. From your description thus far, I believe these cover the possibilities; those being: Data is entered a cell at a time before the time hack is entered, or time-hack first then data, or data is entered (before or after) all at once (array).

Sub Test_1()
Dim r As Range
Set r = Cells(Rows.Count, 1).End(xlUp).Offset(1)
r = Now()
Set r = Range(Cells(r.Row, 3), Cells(r.Row, 7))
r = Array(12, 13, 14, 9, 7)
End Sub

Sub Test_2()
Dim r As Range, r2 As Range
Set r = Cells(Rows.Count, 1).End(xlUp).Offset(1)
Set r2 = Range(Cells(r.Row, 3), Cells(r.Row, 7))
r2 = Array(12, 13, 14, 9, 7)
r = Now()
End Sub

Sub Test_3()
Dim r As Range
Set r = Cells(Rows.Count, 1).End(xlUp).Offset(1)
r = Now()
r.Offset(, 2).Value = 22
End Sub

Sub Test_4()
Dim r As Range
Set r = Cells(Rows.Count, 1).End(xlUp).Offset(1)
r.Offset(, 3).Value = 22
r = Now()

End Sub

Hope this helps,

Mark