Playing A Sound Based On A Cell’s Value

Excel Play A Sound Based On Cell’s Value              

             The created macro plays a sound according to value of changed cell in Column C.

New value can be entered to cells in Column C with combobox. In this macro, we have used 3 different sound from Windows sound library. (chimes.wav ,chord.wav ,tada.wav)


Worksheet_Change procedure of Sheet1 :
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim mh As Range, by As Range
 Application.EnableEvents = False
    Set mh = Range("C:C")
 
    For Each by In Range(Target.Address)
        If Not Intersect(by, mh) Is Nothing Then
            Select Case by.Value
        Case "Completed"
            PlayTheSound "chimes.wav"
        Case "Not Completed"
            PlayTheSound "chord.wav"
        Case "Pending"
            PlayTheSound "tada.wav"
    End Select
        End If
    Next by
    Set mh = Nothing
    Application.EnableEvents = True
End Sub

Our procedure that we used to play sound :
Sub PlayTheSound(ByVal WhatSound As String)
    If Dir(WhatSound, vbNormal) = "" Then
        ' WhatSound is not a file. Get the file named by
        ' WhatSound from the Windows\Media directory.
        WhatSound = Environ("SystemRoot") & "\Media\" & WhatSound
        If InStr(1, WhatSound, ".") = 0 Then
            ' if WhatSound does not have a .wav extension,
            ' add one.
            WhatSound = WhatSound & ".wav"
        End If
        If Dir(WhatSound, vbNormal) = vbNullString Then
            ' Can't find the file. Do a simple Beep.
            Beep
            Exit Sub
        End If
    Else
        ' WhatSound is a file. Use it.
    End If
    ' Finally, play the sound.
    #If Win64 Then
sndPlaySound WhatSound, 0&
#Else
sndPlaySound32 WhatSound, 0&
#End If
  End Sub



Read more ...

Sound Alert In Excel

Playing A Sound File If A Condition Is Met

        The created macro plays a .wav file (C:\Windows\Media\chord.wav) when a duplicate value is entered in Column A. 
Later a msgbox is opened and the msgbox  shows repeated values' row numbers.
Our code to play .wav file :

#If Win64 Then
'Code is running in 64-bit Office
Private Declare PtrSafe Function sndPlaySound Lib "winmm.dll" Alias "sndPlaySoundA" ( _
ByVal lpszSoundName As String, _
ByVal uFlags As Long) As Long
#Else
'Code is running in 32-bit Office
Private Declare Function sndPlaySound32 Lib "winmm.dll" Alias "sndPlaySoundA" ( _
ByVal lpszSoundName As String, _
ByVal uFlags As Long) As Long
#End If

....

 #If Win64 Then
sndPlaySound "C:\Windows\Media\chord.wav", 0&
#Else
sndPlaySound32 "C:\Windows\Media\chord.wav", 0&
#End If



Read more ...

Playing The Sounds In Excel


          In this example ,we have played the sounds in directory with macros that we assigned to shapes .For this process we have used ExecuteExcel4Macro Method. For example :

Application.ExecuteExcel4Macro "SOUND.PLAY(,""" & ActiveWorkbook.Path & "\mi.wav"")"


In the example on second page, we have used Function sndPlaySound32 to play the sounds.  For example :

Declare Function sndPlaySound32 Lib "winmm.dll" Alias "sndPlaySoundA" ( _
ByVal lpszSoundName As String, ByVal uFlags As Long) As Long

Sub Do_2()
    Call sndPlaySound32(ThisWorkbook.Path & "\do.wav", 0)
End Sub



Read more ...

Access Database Management With Excel Userform

Access Database Example          

        We have two files in a folder named Personel.xls (Excel workbook) and Personel.mdb (Access database file).
The Access file contains PERSONEL table with personnel information.


       
         There is a userform in the Excel workbook with many VBA controls on it. Through userform, we will do operations onto the Access database ,such as adding data , deleting data, changing data  and querying the database.

With this userform ;

☑️ New data can be added to Access Database
☑️ Data can be changed
☑️ Data can be deleted
☑️ Data can be searched in Access Database
☑️ Data can be transferred from the Access Database to Excel.

We used the Adodb Connection Object for connection to Access database. Codes to connect :
Sub baglanti()
Set baglan = CreateObject("adodb.connection")
#If VBA7 And Win64 Then
baglan.Open "provider=microsoft.ace.oledb.12.0;data source=" & ThisWorkbook.Path & "\PERSONEL.mdb"
#Else
baglan.Open "provider=microsoft.jet.oledb.4.0;data source=" & ThisWorkbook.Path & "\PERSONEL.mdb"
#End If
End Sub


From access database to excel

         We will do all the operations related (data adding,deleting,changing etc.) to the database with the Adodb Connection Object that we created. 
Set baglan = CreateObject("adodb.connection")

For example , let's add a new record to Access database .
Private Sub CommandButton3_Click()
Dim i As Byte
On Error GoTo hata
For i = 0 To 51
If Mid(UserForm1.Controls(i).Name, 1, 3) = "txt" Then
If UserForm1.Controls(i).Value = "" Then
MsgBox "Required field is missing.", vbCritical + vbOKOnly
Exit Sub
End If
End If
If Mid(UserForm1.Controls(i).Name, 1, 3) = "cmb" Then
If UserForm1.Controls(i).Value = "" Then
MsgBox "Required field is missing.", vbCritical + vbOKOnly
Exit Sub
End If
End If
Next i

kod = "'" & txtCalisanKod & "'"
ad = "'" & txtAdi & "'"
dtarih = "'" & txtDtarih & "'"
soyad = "'" & txtSoyadi & "'"
tc = "'" & txtTC & "'"
sgk = "'" & txtSGK & "'"
dyeri = "'" & txtDyeri & "'"
baba = "'" & txtBabaAdi & "'"
anne = "'" & txtAnneAdi & "'"
gtarih = "'" & txtGirisTarih & "'"
ucret = "'" & txtUcret & "'"
vno = "'" & txtVergiNo & "'"
adres = "'" & txtAdres & "'"
Email = "'" & txtEmail & "'"
il = "'" & txtIl & "'"
ilce = "'" & txtIlce & "'"
semt = "'" & txtSemt & "'"
evtel = "'" & txtEvtel & "'"
ceptel = "'" & txtCeptel & "'"
cinsiyet = "'" & cmbCinsiyet & "'"
departman = "'" & cmbDepartman & "'"
gorev = "'" & cmbGorev & "'"
uturu = "'" & cmbUcretturu & "'"

Call baglanti
Set rs = baglan.Execute("INSERT INTO PERSONEL (CALISAN_KODU,ADI,SOYADI,TC_KIMLIK,DOGUM_TARIHI,CINSIYET,DOGUM_YERI,BABA_ADI,ANNE_ADI,GIRIS_TARIH,SGK,VERGI_NO,ADRES,DEPARTMAN,GOREVI,UCRET_TURU,UCRET,IL,ILCE,SEMT,EV_TEL,CEP_TEL,EMAIL) Values _
(" & kod & "," & ad & "," & soyad & "," & tc & "," & dtarih & "," & cinsiyet & "," & dyeri & "," & baba & "," & anne & "," & gtarih & "," & sgk & "," & vno & "," & adres & "," & departman & "," & gorev & "," & uturu & "," & ucret & "," & il & "," & ilce & "," & semt & "," & evtel & "," & ceptel & "," & Email & ")")
Set baglan = Nothing: Set rs = Nothing:

temizle
ListBox1.Clear
MsgBox "New record was added.", vbInformation + vbOKOnly
Label25.Caption = "Total Records = " & ListBox1.ListCount

hata:
If Err = -2147217913 Then
MsgBox "The salary must be numeric values and The dates must be valid date (e.g.: 20.12.2009)", vbCritical + vbOKOnly
End If
End Sub


Read more ...