VBA to Identify Negative Balances

[golf4]

Hi, everyone -

Been too long since I've been back, but between work and other stuff going on, hard to get on....... SORRY!!!!!

I did have a quick question: our finance manager requested that I help him "jazz" up some of his spreadsheets by adding some wav files. I've inserted one that did work for a time, but no longer works. It's supposed to identify negative financial balances in the cells in Column C:

Private Sub Worksheet_Change(ByVal Target As Range) 
If Intersect(Target, Range("c2:c200")) Is Nothing Then 
    Exit Sub 
Else 
    If Intersect(Target, Range("c2:c200")) < 0 Then 
        Application.ScreenUpdating = True 
        DoEvents 
        ActiveSheet.Shapes("object 1.wav").Select 'dan msg' 
        Selection.Verb Verb:=xlPrimary 
     End If 
End If 
End Sub

Can anyone see a problem within the code?


Thanks for the help.

Frank

[Paleo]

Hi Frank,

as its VBA and not VB Script I have changed your title, but VB Script can bring less people to help you.

Remembering, VBA is a subset out of VB Script which in turn is a subset out of Visual Basic.

[geekgirlau]

Any possibility that the WAV file has either changed location or no longer exists?

[golf4]

Thanks for the clarification, Carlos. I appreciate it.

Frank

[golf4]

Hi, Geekgirlau -
Thanks for the response. I believe the wav file is still there. For example, I have the formula =A1-B1 in Cell C1. Cell C1 is the cell that I would be lookin at to see if "negative balance". If I enter "10" in Cell A1 and "50" in Cell B1, "-40" appears in Cell C1 but no wav. However, if I manually enter "-40" in Cell C1, the wav will kick off. This is driving me nuts!!!

[geekgirlau]

The change event is not triggered when a formula recalculates. Your code needs to check for changes in the range that will be updated (ie column A and B) rather than column C.

[golf4]

Thanks again. Can you give me an idea of how i can modify my existing code to accomplish what you're referring to?

[geekgirlau]

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("a2:b200")) Is Nothing Then
    Exit Sub
    Else
    If Range("c" & Target.Row).Value < 0 Then
        Application.ScreenUpdating = True
        DoEvents
        ActiveSheet.Shapes("object 1.wav").Select 'dan msg'
       Selection.Verb Verb:=xlPrimary
   End If
    End If
End Sub

[golf4]

THANKS SO MUCH!!!!

It works like a charm now. Really appreciate the help!!!

Hope things are going great for ya "down under".

Take care,

Frank

[geekgirlau]

Golf4, my pleasure (and things are always great "down under"!)

Don't forget to mark your thread as Solved.