[Solved] Can I change a cell to make a sound?

[needsoundhelp]

I adapted a macro code from Musikai on this page viewtopic.php?f=20&t=57699&p=254677&hil ... is#p254677 which (with the addition of a button) lets me play a sound on my page by pressing the button. So far, so good.

But what I really need it to do is to have it play the sound when a number in a cell changes. For example, if you type “1” into cell A1, it will play the sound.

I’ve looked a lot but haven’t been able to find anything that does this. I’ve thought of and tried the idea of “using a macro as a function” at https://wiki.openoffice.org/wiki/Docume ... a_function . If that’s a good way of doing it (and I’m not sure it is), the page doesn’t seem to give me enough information for me to make it work.

Any suggestions?

[Bidouille]

You can use a script event:

• Right-click on sheet tab > Sheet Events...
• Event: Content Changed

Plug your macro to it

[needsoundhelp]

That works except apparently if you make a change anywhere on the sheet you get the sound. I must not have described my situation well enough.

What I need is that when one cell changes, it plays the sound. But not when anything on the sheet changes.

So if you have, for example, in cell A1 the equation: =A2, then you type 1 into cell A2, it will trigger the sound. But if you put a 1 into cell A3 it won't.

Based on your suggestion I looked under Customize>Events (the events tab) and didnt see any "contents changed" listing to cover a single cell. I don't know if one exists.

I also tried setting this up on another sheet which is otherwise blank, then having something from another sheet change the amount in cell A1. The contents changes, but the sound doesn't work without you being on the sheet.

Thanks very much for your suggestion, it really works well but it just doesn't quite work for what I need.

NOTE:
I'm checking out the "formulas calculated" option (just above "content changed), now, will report on that.

[needsoundhelp]

Well, the "Formulas Calculated" option just above it (at the "sheet" tab at the bottom left of the page, right click it then choose "sheet events..." option) works a bit better, but still doesn't quite do it.

Instead of making the sound whenever anything on the sheet changes, it just makes it whenever a formula is calculated. Better, yes, but if you have more than one formula on the sheet it's not as good. I need it to work whenever one particular formula is calculated, instead of any of them on a page that has many formulas.

So it almost works. Back to the drawing board.

[RoryOF]

At the end of this topic
viewtopic.php?f=20&t=30516
Villeroy gives code to identify the cell that caused the action; it should be possible to use that code and only perform the action when the target cell was changed.

[JeJe]

Code: Select all

Sub addListener()
oModifyListener = createUnoListener("cellListener_", "com.sun.star.util.XModifyListener")
ThisComponent.sheets(0).getCellByPosition(1,1).addModifyListener(oModifyListener)
End Sub

Sub cellListener_modified(ev)
   msgbox ev.source.value
'or play sound
End Sub

Sub cellListener_disposing(ev)
End Sub

[needsoundhelp]

Thanks for the help but I'm not quite advanced enough to really "get it."

What I have below are A) the code I use to play a sound when attached to a button and the button is pressed and B) Villeroy's code you suggested.

How can I put the two of them together to make it so that when a formula in cell A1 changes, the sound works but that's the ONLY time it works no matter what else happens on the sheet? (And would I use "Formulas Calculated" or "Content Changed" for this, or is there another way to do it?).

Here's my sound code:

Code: Select all

REM  *****  BASIC  *****
option explicit


Sub Main

End Sub



Sub Replaysound
   PlaySound("C:\Program Files (x86)\OpenOffice 4\share\gallery\sounds\kling.wav")
End Sub

Sub PlaySound2(i_soundpath as string)
     dim oPlayer1 as object
     dim sUrlSound as string
     dim oSounMgr as object
     
'     if  bInit then
     if not isnull(oSounMgr) then
         S_Start_New
         exit sub
     endif
     
'     sUrlSound = F_get_Sound_URL("MySound.mp3")
     sUrlSound = ConvertToUrl(i_soundpath)

     If not fileexists(sUrlSound) Then       
         msgbox sUrlSound & " does not exist",16
     else
        If GetGuiType() = 1 Then
            oSounMgr = CreateUnoService("com.sun.star.media.Manager_DirectX")
        Else
        '.comp. was not documented !!
            oSounMgr = CreateUnoService("com.sun.star.comp.media.Manager_GStreamer")
   '                                     com.sun.star.media.Manager_GStreamer
        End If
        If IsNull(oSounMgr) Then
           msgbox "Sound Mgr not set",16
        else
            oPlayer1 = oSounMgr.createPlayer(sUrlSound)
            oPlayer1.setMediaTime(0.0)
            oPlayer1.setVolumeDB(-10)
'            msgbox oPlayer1.getduration()
            oPlayer1.setPlayBackLoop( 0 )
            oPlayer1.start(0)
           while oPlayer1.isplaying()
              doevents
           wend
           oPlayer1 = nothing
           oSounMgr = nothing
           
        End If
     End If
End Sub

And here's Villeroy's code you suggested:

Code: Select all

Sub OnSheetContentChange(e)
print e.AbsoluteName
End SubSub OnSheetContentChange(e)
print e.AbsoluteName
End Sub

[JeJe]

I've already spoon fed you the answer.

change 'or play sound to your sound playing code and run addListener to start the listener.

[needsoundhelp]

Thanks Jeje.

Hope I got this right. After doing that I have the code posted below, but when I run addListener I get "BASIC syntax error. Sub not allowed within a procedure" that center around the 8th line, "Sub Replaysound" and then, after trying some things, "BASIC runtime error. Variable not defined" at the second line "oModifyListener = createUnoListener("cellListener_", "com.sun.star.util.XModifyListener")"
So I'm stuck.

Sorry I'm not picking this up right away. Obviously I'm not a spreadsheet expert, just trying to figure out how to do this one thing.

Code: Select all

REM  *****  BASIC  *****
option explicit


Sub Main

End Sub



Sub addListener()
oModifyListener = createUnoListener("cellListener_", "com.sun.star.util.XModifyListener")
ThisComponent.sheets(0).getCellByPosition(1,1).addModifyListener(oModifyListener)
End Sub

Sub cellListener_modified(ev)
msgbox ev.source.value
End Sub
Sub Replaysound
PlaySound("C:\Program Files (x86)\OpenOffice 4\share\gallery\sounds\kling.wav")
End Sub

    Sub PlaySound2(i_soundpath as string)
         dim oPlayer1 as object
         dim sUrlSound as string
         dim oSounMgr as object
         
    '     if  bInit then
         if not isnull(oSounMgr) then
             S_Start_New
             exit sub
         endif
         
    '     sUrlSound = F_get_Sound_URL("MySound.mp3")
         sUrlSound = ConvertToUrl(i_soundpath)

         If not fileexists(sUrlSound) Then       
             msgbox sUrlSound & " does not exist",16
         else
            If GetGuiType() = 1 Then
                oSounMgr = CreateUnoService("com.sun.star.media.Manager_DirectX")
            Else
            '.comp. was not documented !!
                oSounMgr = CreateUnoService("com.sun.star.comp.media.Manager_GStreamer")
       '                                     com.sun.star.media.Manager_GStreamer
            End If
            If IsNull(oSounMgr) Then
               msgbox "Sound Mgr not set",16
            else
                oPlayer1 = oSounMgr.createPlayer(sUrlSound)
                oPlayer1.setMediaTime(0.0)
                oPlayer1.setVolumeDB(-10)
    '            msgbox oPlayer1.getduration()
                oPlayer1.setPlayBackLoop( 0 )
                oPlayer1.start(0)
               while oPlayer1.isplaying()
                  doevents
               wend
               oPlayer1 = nothing
               oSounMgr = nothing
               
            End If
         End If
    End Sub


Sub cellListener_disposing(ev)
End Sub 

[JeJe]

The sub you mean to call... the PlaySound sub... you've called PlaySound2 not PlaySound.

[JeJe]

Code: Select all

Sub addListener()
	oModifyListener = createUnoListener("cellListener_", "com.sun.star.util.XModifyListener")
	ThisComponent.sheets(0).getCellByPosition(1,1).addModifyListener(oModifyListener)
End Sub

Sub cellListener_modified(ev)
	PlaySound("C:\Program Files (x86)\OpenOffice 4\share\gallery\sounds\kling.wav")
End Sub
Sub cellListener_disposing(ev)
End Sub

Sub PlaySound(i_soundpath as string)
	dim oPlayer1 as object
	dim sUrlSound as string
	dim oSounMgr as object

	'     if  bInit then
	if not isnull(oSounMgr) then
		S_Start_New
		exit sub
	endif

	'     sUrlSound = F_get_Sound_URL("MySound.mp3")
	sUrlSound = ConvertToUrl(i_soundpath)

	If not fileexists(sUrlSound) Then
		msgbox sUrlSound & " does not exist",16
	else
		If GetGuiType() = 1 Then
			oSounMgr = CreateUnoService("com.sun.star.media.Manager_DirectX")
		Else
			'.comp. was not documented !!
			oSounMgr = CreateUnoService("com.sun.star.comp.media.Manager_GStreamer")
			'                                     com.sun.star.media.Manager_GStreamer
		End If
		If IsNull(oSounMgr) Then
			msgbox "Sound Mgr not set",16
		else
			oPlayer1 = oSounMgr.createPlayer(sUrlSound)
			oPlayer1.setMediaTime(0.0)
			oPlayer1.setVolumeDB(-10)
			'            msgbox oPlayer1.getduration()
			oPlayer1.setPlayBackLoop( 0 )
			oPlayer1.start(0)
			while oPlayer1.isplaying()
				doevents
				wend
				oPlayer1 = nothing
				oSounMgr = nothing

			End If
		End If
End Sub

[needsoundhelp]

It's fabulous that your helping me like this and I wouldn't blame you if you just threw your hands up and said "forget it!"

But here's what I did:
1) Copied and pasted the code you gave me as (partially) shown below
2) Hit the sheet tab>sheet events>content changed>and added the macro "addlistener"
3) Ran the addListener
4) And this is what I got:

I also tried setting this up from the sheet events as "Formulas Calculated" (since I have a formula in cell A1) but had he same result.

Oh well, thanks for trying to help me out.

(Here's what the whole thing looks like right now):

Code: Select all

REM  *****  BASIC  *****
option explicit


Sub Main

End Sub

    Sub addListener()
       oModifyListener = createUnoListener("cellListener_", "com.sun.star.util.XModifyListener")
       ThisComponent.sheets(0).getCellByPosition(1,1).addModifyListener(oModifyListener)
    End Sub

    Sub cellListener_modified(ev)
       PlaySound("C:\Program Files (x86)\OpenOffice 4\share\gallery\sounds\kling.wav")
    End Sub
    Sub cellListener_disposing(ev)
    End Sub

    Sub PlaySound(i_soundpath as string)
       dim oPlayer1 as object
       dim sUrlSound as string
       dim oSounMgr as object

       '     if  bInit then
       if not isnull(oSounMgr) then
          S_Start_New
          exit sub
       endif

       '     sUrlSound = F_get_Sound_URL("MySound.mp3")
       sUrlSound = ConvertToUrl(i_soundpath)

       If not fileexists(sUrlSound) Then
          msgbox sUrlSound & " does not exist",16
       else
          If GetGuiType() = 1 Then
             oSounMgr = CreateUnoService("com.sun.star.media.Manager_DirectX")
          Else
             '.comp. was not documented !!
             oSounMgr = CreateUnoService("com.sun.star.comp.media.Manager_GStreamer")
             '                                     com.sun.star.media.Manager_GStreamer
          End If
          If IsNull(oSounMgr) Then
             msgbox "Sound Mgr not set",16
          else
             oPlayer1 = oSounMgr.createPlayer(sUrlSound)
             oPlayer1.setMediaTime(0.0)
             oPlayer1.setVolumeDB(-10)
             '            msgbox oPlayer1.getduration()
             oPlayer1.setPlayBackLoop( 0 )
             oPlayer1.start(0)
             while oPlayer1.isplaying()
                doevents
                wend
                oPlayer1 = nothing
                oSounMgr = nothing

             End If
          End If
    End Sub

[robleyd]

I believe that if you declare "option explicit" then you must define variables before they can be used; you haven't defined oModifyListener prior to using it.

[JeJe]

As robleyd said.

Just put the code above in a blank module.... get rid of the sub... main... you don't need it. Don't add Option Explicit back in again... or if you do go through every variable and make sure its defined.

[JeJe]

2) Hit the sheet tab>sheet events>content changed>and added the macro "addlistener"

And don't do that.

[needsoundhelp]

Ok, I've begun testing, in the best way I know how.

To begin with, even though this is not the way we've talked about nor the way I mean it to be, to start off simple I've done this. I assigned the code to a button to see if it would work.

I copied the code in Jeje's post at 6:41 am and pasted it to a COMPLETELY blank module.
I then tried assigning each module to the button and tried it (although, correct me if I'm wrong, I believe its the Addlistener one you have to assign, right?). Here are the results:

addlistener: silence
cellListener_disposing: ERROR. Message: Wrong number of paramaters
cellListener_modified: ERROR. Message: Wrong number of paramaters
playsound: ERROR. Message: Wrong number of paramaters

So for whatever reason that's not working for me.

I'm now, as I have time, going to test it in the way it's meant to run, with a formula in cell A1, that, when it changes, will hopefully play the sound.

I'm going to begin testing by first assigning to the macro "addlistener", as I believe that's what your supposed to do, but if thats not right I hope you'll let me know.

Thanks for putting up with me on this. I hope if we can make it work it'll make a reference that someone else who needs it can come by, slap it into their computer and get 'er done!

[JeJe]

Create a new document
Put the code in a module
press F5 in the IDE to run the Addlistener sub

Whenever you change the contents of the cell the code in cellListener_modified will run.

Note the cell is (1,1) in the code you can change it to (0,0) if you want or whatever in the line below.

ThisComponent.sheets(0).getCellByPosition(1,1).addModifyListener(oModifyListener)

[needsoundhelp]

I had no idea the Fkeys were involved in this.

I'm clicking the F5 key while in the IDE but it's having no effect. When I look up the Fkeys in Tools>Customize>Keyboard the F5 key is set to "Navigator"(see change below*) Is that right or should it be setup to something else? Is there anything else I ned to know?

About the cell in the code (1,1), I assume that means cell A1, right? So (2,2) would be cell B2 and (26,26) would be cell Z26?

If I can get this going would I have to press the f5 key in the IDE every time I use the spreadsheet or would it be already setup every time its used?
***

*OH WAIT! I just noticed there was a customize in the IDE which is different then the one on the spreadsheet. F5 has run BASIC on it, but I've clicked it but it too has no effect. Its not working.

[JeJe]

F5 pressed while in the IDE runs the first macro in the module. You can run it other ways - but that's the easiest.

Nothing will appear to happen because what that sub does is register the listener. You must only do that once. Don't worry - it works even though you don't see anything.

Cells indexes are zero based, not 1 based. 1 based indexes are unusual in computing - most often counting starts at 0. So A1 is (0,0)

You can run addlistener from an event such as the Tools menu/customize/events Open Document event. Make sure you set it for that document only in the combo box at the bottom - not for all documents.

[needsoundhelp]

OK Finally I've gotten somewhere.

I looks like its sort of running, but whats happening is, even though that number is set at (0,0), the noise being made is when something is changing in cell B2 instead of A1. And for some reason the sound is working twice instead of once.

I'm starting to feel better about this.

***

Scratch this. I just saved this and closed it then opened it again, setting the number at (0,0), and clicked the f5 again. Now, after trying it a few times it appears to be working!

Thanks for hanging on with me!

[needsoundhelp]

Apparently you can make 2 different cells work too, just by pasting the code and changing the names (I did it by adding a "1" behind each name) although it doesn't seem to be working so well yet.

[Villeroy]

Code: Select all

ThisComponent.sheets(0).getCellByPosition(1,1).addModifyListener(oModifyListener)

Cell B2 is the broadcaster.

Code: Select all

Sub cellListener_modified(ev)
oCell = ev.Source ' reference to cell B2
addr = oCell.getCellAddress() 'cell address struct of B2

[needsoundhelp]

To a person who really doesn't know this stuff, it all seems to work pretty, not sure of the word, maybe "wonky?"

If you want to "turn this thing off" how would you do that?

[JeJe]

Close the document and don't run it next time. If you've set the document open or other event to run any macro remove that association.

[Villeroy]

To a person who really doesn't know this stuff, it all seems to work pretty, not sure of the word, maybe "wonky?"

If you want to "turn this thing off" how would you do that?

Event driven programming is the opposite of "wonky" (unless the only thing you know is VBA).