Condition reached : Make sound and show msgbox with cell value

[incobart]

Hi I have a aheet with products, givings and prices, 1 cell let the price data always updating from the web.

I put a cell formula that when the online price becomes higher than my price it gives a beep signal.

Please Login or Register  to view this content.

Private Declare PtrSafe Function PlaySound Lib "winmm.dll" _
        Alias "PlaySoundA" (ByVal lpszName As String, _
        ByVal hModule As LongPtr, ByVal dwFlags As Long) As Boolean

Const SND_SYNC = &H0
Const SND_ASYNC = &H1
Const SND_FILENAME = &H20000
Function SoundMe() As String
'Updateby Extendoffice 20161223
    Call PlaySound("c:\windows\media\Alarm03.wav", _
      0, SND_ASYNC Or SND_FILENAME)
    SoundMe = ""
End Function 

The problem is that when it makes a sound I have to search the whole sheet ( a lot) wich one is change from "not" to empty cell.
It would make me happy if it also open a msgbox that tells me what cell gave the beep

Thanks to solve this ...

(ps the checkings happen in colum E and F, but the colum has other values also, so not only the prices ...)

[kev_]

Perhaps this will work:


Add this as the last line in your function - it identifies which cell called the procedure

Please Login or Register  to view this content.

[incobart]

Hi Ken,

like this ?

Please Login or Register  to view this content.

#value appear if I evaluate , it goes till msgbox and then says #NAME? and end up with #value ...

sorry if I did wrong ..


Chris

[kev_]

Like this

Please Login or Register  to view this content.

[incobart]

Hi
one problem occured, when the msgbox appears, then all macros stop, so no good if I ambusy, away, or asleep ..
I found the following solution, to let the macro run on ...

    Private Declare Function MessageBox _
        Lib "User32" Alias "MessageBoxA" _
           (ByVal hWnd As Long, _
            ByVal lpText As String, _
            ByVal lpCaption As String, _
            ByVal wType As Long) _
        As Long 

put it in the same module above the soundme/msg function, and also in the sheet code.

in the function I putted the line for the messagebox =

Please Login or Register  to view this content.

but it doesn't work.. it still stop my macro running ...
What is wrong please ?

Thank you
Chris

[kev_]

This is one way to make a message box close after a set time
Replacing your entire code with the code below and see if it works for you.

- timed message box added - it should automatically close after 3 seconds
- you can change the time it takes the message box to close

Please Login or Register  to view this content.