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.
Values to be defined

Step 3: Enable Developer Tab
Excel 2007 : Office button -> Excel Options -> Popular
Enable developer tab in Excel 2007
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
Enable developer tab

Step 4: Create one Macro : Developer Tab -> Visual Basic

Step 5: Go to Project - VBA Project bar -> Open ThisWorkbook and write the below code

Private Sub Workbook_Open()
Application.OnKey "~", "MyEnterEvent"
End Sub


Step 6: Go to Insert menu -> Click on Module and write the below code in module
Create 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


Comments

No responses found. Be the first to comment...


  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: