On Oct 31, 10:00 am, Mark Berry <Mark
wrote:
I have the following code to play a sound 20 times:

Sub PlaySound()
Do While Counter < 20
Counter = Counter + 1
Call sndPlaySound32("c:\windows\media\Ding.wav", 0)
Loop
End Sub

However, I want to replace the counter with a message box, asking the user
to click OK to stop the sound. The loop is to continue until OK is clicked,
so;

Do While Counter < 20

Becomes;

Do Until ' the user responds to the message box.

I can create the message box no problem, and can even get the response to
exit the code correctly, but can't keep the sound looping while waiting for a
response, as the code gets as far as the message box then stops and waits.

Alternatively I thought of running the loop until a user enters a given
value in a cell (something like "Do Until B1 = 15". Syntax is wrong, but you
see what I mean!), but of course the user can't interact with the workbook
while the code is running.

Having the user hit ESC to stop the code (and hence sound) works, but isn't
exactly elegant as it brings up the VBA error box, offering to debug etc.

Any ideas?

Hi
You could put a form button on the worksheet with text "Stop the
Sound!"

This wouls call the macro

Sub StopIt()
StopTheSound = True
End sub

The Boolean StopTheSound is declared as a Public variable at the top
of your code module

Public StopTheSound as Boolean

Now your code is

Sub PlaySound()
StopTheSound = False
Do While Counter < 20
Counter = Counter + 1
Call sndPlaySound32("c:\windows\media\Ding.wav", 0)
If StopTheSound = True then Exit Do
DoEvents 'may not be required
Loop
End Sub

regards
Paul