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?

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

Another solution.
This can make you interrupt with the Escape key or with Ctrl + break


Sub PlaySound(Optional counter as Long)

On Error GoTo handleCancel

Application.EnableCancelKey = xlErrorHandler

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

Application.EnableCancelKey = xlInterrupt

Exit Sub
handleCancel:

If Err.Number = 18 Then
If MsgBox("Stop the sound?", vbYesNo) = vbNo Then
PlaySound counter
End If
End If

Application.EnableCancelKey = xlInterrupt

End Sub


RBS


"Mark Berry" <Mark wrote in message
...
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?

Thanks Paul. This is the way I'd like to do it, but the issue is that I can't
interact with the workbook while the playsound code is running, unless
there's a trick I've missed somewhere?

Mark

" wrote:


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

Cheers RB. I'll try that out, and it'll be a great solution if I can't get a
specific control or message box working to dismiss my sound.

Mark

"RB Smissaert" wrote:

Another solution.
This can make you interrupt with the Escape key or with Ctrl + break


Sub PlaySound(Optional counter as Long)

On Error GoTo handleCancel

Application.EnableCancelKey = xlErrorHandler

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

Application.EnableCancelKey = xlInterrupt

Exit Sub
handleCancel:

If Err.Number = 18 Then
If MsgBox("Stop the sound?", vbYesNo) = vbNo Then
PlaySound counter
End If
End If

Application.EnableCancelKey = xlInterrupt

End Sub


RBS


"Mark Berry" <Mark wrote in message
...
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?