Macro Running on each refresh

PhillB

New Member
Joined
Sep 15, 2008
Messages
6
HI. I have created a tracking sheet which takes data from another excel sheet by refreshing links every 30 seconds (would use share workbook but function not enabled by my work). I have created a macro to play every time a cell in a range is changed to a value above 0 but it reads every refresh as a change and plays every 30 seconds. Is there any way to prevent this?
Thanks
 
Last edited:

PhillB

New Member
Joined
Sep 15, 2008
Messages
6
Well create is an overstatement, copied is more accurate.
Code:
Private Declare Function PlaySound Lib "winmm.dll" _
  Alias "PlaySoundA" (ByVal lpszName As String, _
  ByVal hModule As Long, ByVal dwFlags As Long) As Long
    Const SND_SYNC = &H0
    Const SND_ASYNC = &H1
    Const SND_FILENAME = &H20000
    Function SoundMe() As String
    Call PlaySound("c:\windows\media\swtheme", _
      0, SND_ASYNC Or SND_FILENAME)
    SoundMe = ""
End Function
With a trigger in each cell of a column going down the table
Code:
=IF(K3>0,SoundMe(),"")
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
7,829
Office Version
365
Platform
Windows
The way that is set up then its going to play the sound each time the cell calculates should the IF be true. There is no way to prevent that unless calculation is off.
 

Forum statistics

Threads
1,086,126
Messages
5,387,989
Members
402,094
Latest member
Bloody69

Some videos you may like

This Week's Hot Topics

Top