PDA

View Full Version : [SOLVED] VBA to Identify Negative Balances



golf4
08-01-2005, 07:51 PM
Hi, everyone -

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

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
08-01-2005, 08:51 PM
Hi Frank,

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

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

geekgirlau
08-01-2005, 09:22 PM
Any possibility that the WAV file has either changed location or no longer exists?

golf4
08-01-2005, 09:22 PM
Thanks for the clarification, Carlos. I appreciate it.

Frank

golf4
08-01-2005, 09:29 PM
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
08-01-2005, 10:03 PM
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
08-01-2005, 10:17 PM
Thanks again. Can you give me an idea of how i can modify my existing code to accomplish what you're referring to?

geekgirlau
08-01-2005, 10:31 PM
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
08-01-2005, 10:38 PM
THANKS SO MUCH!!!! :bow::bow::bow::bow::bow:

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

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

Take care,

Frank

geekgirlau
08-01-2005, 11:04 PM
Golf4, my pleasure (and things are always great "down under"!) :content:

Don't forget to mark your thread as Solved.