How to disable and enable sound wave files in Excel using VBA

Hi, this is still about disabling and enabling the sound files.

Using Forum Contributor, Squeaky's idea, I came up with some VBA codes.
The idea is this:

I'm using a toggle button to simulate a "Turn Sound ON/OFF" button.
Press once, a cell will display the word TRUE and if press a second time,
that cell will display the word FALSE.

This cell that displays the TRUE & FALSE word is linked to a module which contains
the coding for playing the sound wave files.
If the cell displays FALSE, the sound file will not be played, else (if TRUE)
the sound will play.

To test if the disabling and enabling the sound works, I've a cell where the
user types in the letter A or B and click the "Answer" button (to run the macro).
Typing either A or B will cause a message box to display, accompanied by a sound.

So if by toggling the "Turn Sound ON/OFF" button, one should expect to hear the sound
or hear no sound.

However, the coding ran into a problem:
"Compile error: User-defined type not defined" in Module 2 on "Dim BM As ActiveWorkSheets".

I've attached a sample of the Excel file so that what I mentioned above made sense.
.Module 2 contains the coding for playing the sound files from C:\Windows\Media.
.Module 1 contains the coding for A & B message boxes & "Call Playwav".
.Codes in Sheet1 is for the toggle button.

I hope someone can help solve that error or improve on the coding to make it
more efficient.

Thank you.
ChanKS

I don't think ActiveWorkSheets exists.

Maybe something like

Dim BM As Worksheet
Set BM = ActiveSheet

Thank you ByteMarks.
I made the changes but the Compile error "User-defined type not defined" is still there.

Which line is it this time?

Hi ByteMarks.
It's still the same line in Module 2: Dim BM As ActiveSheet.
Thanks for looking into it.

You need to declare BM as a WorkSheet (not ActiveSheet) and then Set it to the ActiveSheet

Hi ByteMarks, I made the change as you mentioned.

I changed Dim BM As ActiveSheet to Dim BM As Worksheet, and
left SET BM = ActiveSheet("Sheet1").Select as it is.

I got an error: Run-time error '438': Object doesn't support this property or method. The whole line SET BM=ActiveSheet("Sheet1").Select is highlighted as error.

So I made another change. I changed SET BM=ActiveSheet("Sheet1").Select to SET BM=Worksheet("Sheet1").Select.

I got a new error message: Compile error: Sub or Function not defined. Only the word Worksheet in SET BM=Worksheet("Sheet1").Select is highlighted as error.

Thanks for looking into these problems.

Hi Marc L.
Thank you for your input. I put in the missing 's' but still the same error messages.
I have many permutations - changing ActiveSheet to Worksheet, put 's' and without 's', leaving out the word "Select", etc. and still ended up with
either "Sub or Function not defined' or 'Object doesn't support this property or method'.

Rather than a guessing challenge post your actual code using the # icon, in order some helper could see the light ...

Hi to all experts. Hoping some people can help to find what's wrong with the coding.
This code is in Module 2.

Private Declare PtrSafe Function PlaySound Lib "winmm.dll" Alias "PlaySoundA" (ByVal lpszSoundName As String, ByVal hModule As Long, ByVal dwFlags As Long) As Long
Const SND_SYNC = &H0
Const SND_ASYNC = &H1
Const SND_FILENAME = &H20000
---------------------------------------------------------------------------------

Sub Playwav()
'Play this sound when the answer is wrong (B).
Dim BM As Worksheets
SET BM = ActiveSheet("Sheet1")
If BM.Range("P4") = "OFF" Then

Exit Sub
Else
    
    If Application.CanPlaySounds Then
    Call PlaySound("C:\Windows\Media\Windows Error", 0&, SND_ASYNC Or SND_FILENAME)
    End If
End If
End Sub

-----------------------------------------------------------------------

Sub Playwav2()
'Play this sound when the answer is correct (A).
Dim BM As Worksheets
Set BM = ActiveSheet("Sheet1")
If BM.Range("P4") = "OFF" Then

Exit Sub
Else
    
    If Application.CanPlaySounds Then
    Call PlaySound("C:\Windows\Media\tada", 0&, SND_ASYNC Or SND_FILENAME)
    End If
End If
End Sub

The error "Run-time error '438': Object doesn't support this property or method." is on the line
SET BM = ActiveSheet("Sheet1").

Thank you.
Chan KS

Hi Marc L.
Changed to your code, but still it's not working.

Error: Run-time error '13': Type mismatch.
Highlighted in yellow on these words only: If Sheet1.[P4] Then.

ChanKS

As it well works on my side the bad is on yours ...

Originally Posted by KSChan

However, with PlaySoundA "C:\Windows\Media\Windows Error.wav", 0&, &H20000, the sound comes first then followed by the message.

Your bad as this is not my original codeline !!

Hi Marc L,
You're right! I made the mistake by typing your original ...&H20001... as &H20000.
Now with your original codeline, the sound and message came together!
Thank you.

ChanKS.

Yes according to Arithmetic basics as you can easily check &h1 (so 1) or &h20000 = &h20001 !