Check the end of the song and run macro

Question

Hello, I am making a virtual sound table for an event and would like to give the effect of clicking the button. I am making mega simple, but I need the button to be pressed while the music plays and when this ends the button release.

It’s like this:

Private Sub CAMPAINHA_Click()

CAMPAINHA.Visible = False

WindowsMediaPlayer1.URL = ActiveWorkbook.Path & "\SONS\CAMPAINHA.mp3"

CAMPAINHA.Visible = True

End Sub

This is the regular table:

So I’d like her to stay while she plays the music and in the end return to normal.

Answer 1

One of the ways would be to take the duration time of the mp3 file that will be played and put Excel in pause during this time for when to finish, return the button to the default color.

1. To get the data from an mp3 file you can use the following macro:

   Function FileInfo(path, filename, item) As Variant
   Dim objShell As IShellDispatch4
   Dim objFolder As Folder3
   Dim objFolderItem As FolderItem2
   
       Set objShell = CreateObject("Shell.Application")
       Set objFolder = objShell.Namespace(path)
       Set objFolderItem = objFolder.ParseName(filename)
       FileInfo = objFolder.GetDetailsOf(objFolderItem, item)
   
       Set objShell = Nothing
       Set objFolder = Nothing
       Set objFolderItem = Nothing
   End Function
   

2. From there you can call this function searching the data, example:

   Range("A1").value = FileInfo(currdir, filename, 20) 'Artista
   Range("A2").value = FileInfo(currdir, filename, 14) 'Album
   Range("A3").value = FileInfo(currdir, filename, 21) 'Título
   Range("A4").value = FileInfo(currdir, filename, 26) 'Sequencia (Track#(
   Range("A5").value = FileInfo(currdir, filename, 16) 'Genero
   Range("A6").value = FileInfo(currdir, filename, 27) 'Duração
   

3. So I guess in the middle of your macro would look something like this:

   Sub aoApertarBotao()
   
   Dim mp3Duration as String
   
       'Altera a cor do botão para modo "tocando"
       '[...] seu código para tocar a música
   
       mp3Duration = FileInfo("C:\Caminho\do\Arquivo\mp3\", "nome da musica.mp3", 27)
       Application.Wait (Now + TimeValue(mp3Duration))
   
       'Altera a cor do botão para modo padrão
   
   End Sub
   

See if you can do it like this!

Sources/Credits:

1. http://spreadsheetpage.com/index.php/file/mp3_file_lister/

2. https://stackoverflow.com/questions/1544526/how-to-pause-for-specific-amount-of-time-excel-vba

Answer 2

I found a good answer too:

Private Sub WindowsMediaPlayer1_StatusChange()

    If WindowsMediaPlayer1.playState = wmppsMediaEnded Then

        CAMPAINHA.Visible = True
        VOZA1.Visible = True
        VOZALEA1.Visible = True
        VOZALEB1.Visible = True
        VOZB1.Visible = True
        VOZA2.Visible = True
        VOZALEB2.Visible = True
        VOZB2.Visible = True
        VOZA3.Visible = True
        VOZB3.Visible = True
        VOZB4.Visible = True
        SOM_AMBIENTE.Visible = True
        SOM_AMBIENTE2.Visible = True
        SOM_CINEMA.Visible = True
        SOM_FOTO.Visible = True

    End If

End Sub

When the audio comes at the end everyone is visible.