Beep sound if cell found empty in excel
In this article i will explain you that how you can beep sound if you found empty cell or if you found cell which has minimum value than that you have defined or if you found which has maximum value than that you have defined in excel. So in all these different situations you can beep different different sounds as per your requirement.
Overview :
You can beep different different sound when you found Empty Cell or Minimum value or Maximum value in a cell in particular range that you have defined.
Let's start doing it.
Step 1: Create one excel sheet
Step 2: Define values in any Sheet. Rename the any Sheet to Control. See the below image.
Step 3: Enable Developer Tab
Excel 2007 : Office button -> Excel Options -> Popular
Excel 2010 : File -> Options -> Customize Ribbon -> Check Developer Tab checkbox in Right side list view of Main Tabs
Excel 2013 : File -> Options -> Customize Ribbon -> Check Developer Tab checkbox in Right side list view of Main Tabs
Step 4: Create one Macro : Developer Tab -> Visual Basic
Step 5: Go to Project - VBA Project bar -> Open ThisWorkbook and write the below codePrivate Sub Workbook_Open()
Application.OnKey "~", "MyEnterEvent"
End Sub
Step 6: Go to Insert menu -> Click on Module and write the below code in moduleCreate two functions
Private Declare Function PlaySoundA _
Lib "winmm.dll" _
(ByVal lpszSound As String, _
ByRef hModule As Long, _
ByVal fdwSound As Long) As Long
Function InRange(Range1 As Range, Range2 As Range) As Boolean
' returns True if Range1 is within Range2
Dim InterSectRange As Range
Set InterSectRange = Application.Intersect(Range1, Range2)
InRange = Not InterSectRange Is Nothing
Set InterSectRange = Nothing
End Function
Create an event
Sub MyEnterEvent()
Dim MinVal, MaxVal As Integer
Dim FromRange, ToRange, CheckRange As String
MinVal = Val(ThisWorkbook.Sheets("Control").Range("B1"))
MaxVal = Val(ThisWorkbook.Sheets("Control").Range("B2"))
FromRange = ThisWorkbook.Sheets("Control").Range("B4")
ToRange = ThisWorkbook.Sheets("Control").Range("B5")
CheckRange = FromRange + ":" + ToRange
If InRange(ActiveCell, Range(CheckRange)) Then
If ActiveCell.Value <> vbNullString Then
'Call Application.Speech.Speak("You left empty cell")
Beep
If ActiveCell < MinVal Then
PlaySoundFile "C:\WINDOWS\Media\notify.wav"
ElseIf ActiveCell > MaxVal Then
PlaySoundFile "C:\Windows\Media\Windows Critical Stop.wav"
End If
End If
End If
Selection.Offset(1, 0).Select
End Sub
Sub PlaySoundFile(SoundFilePath As String)
Dim Ret As Long
Const SND_SYNC = &H0
Const SND_NODEFAULT = &H2
Const SND_FILENAME = &H20000
Ret = PlaySoundA(SoundFilePath, 0&, SND_SYNC Or SND_NODEFAULT Or SND_FILENAME)
End Sub
Step 7: Save your macro and excel file
Now close your excel file and macro and Reopen your excel file and test it and check whether it is working or not.
Enter values in Selected Range only which you have defined in Sheet named "Control".
Click here to download sample file with source code
Follow