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

Creating a macro - Writing a Script - Using the API

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

Postby needsoundhelp » Wed May 06, 2020 5:27 pm

I adapted a macro code from Musikai on this page viewtopic.php?f=20&t=57699&p=254677&hilit=tetris#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?
Last edited by needsoundhelp on Sun May 10, 2020 8:12 am, edited 4 times in total.
OpenOffice 4.1.7, Windows 10
needsoundhelp
 
Posts: 14
Joined: Wed May 06, 2020 5:01 pm

Re: How can I change a cell to make a sound?

Postby Bidouille » Thu May 07, 2020 12:02 pm

You can use a script event:
  • Right-click on sheet tab > Sheet Events...
  • Event: Content Changed
Plug your macro to it
User avatar
Bidouille
Volunteer
 
Posts: 349
Joined: Mon Nov 19, 2007 10:58 am
Location: France

How can I change a cell to make a sound?

Postby needsoundhelp » Thu May 07, 2020 8:39 pm

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.
Last edited by needsoundhelp on Thu May 07, 2020 9:09 pm, edited 1 time in total.
OpenOffice 4.1.7, Windows 10
needsoundhelp
 
Posts: 14
Joined: Wed May 06, 2020 5:01 pm

Re: How can I change a cell to make a sound?

Postby needsoundhelp » Thu May 07, 2020 10:08 pm

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.
OpenOffice 4.1.7, Windows 10
needsoundhelp
 
Posts: 14
Joined: Wed May 06, 2020 5:01 pm

Re: Can I change a cell to make a sound? (HELP! NEARLY SOLVE

Postby RoryOF » Thu May 07, 2020 10:52 pm

At the end of this topic
https://forum.openoffice.org/en/forum/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.
Apache OpenOffice 4.1.7 on Xubuntu 18.04.4 (mostly 64 bit version) and very infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 31232
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Can I change a cell to make a sound? (HELP! NEARLY SOLVE

Postby JeJe » Thu May 07, 2020 11:04 pm

Code: Select all   Expand viewCollapse view

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


Openoffice 4.1.2
Windows 8
JeJe
Volunteer
 
Posts: 1088
Joined: Wed Mar 09, 2016 2:40 pm

Re: Can I change cell to make a sound? (HELP! NEARLY SOLVED!

Postby needsoundhelp » Thu May 07, 2020 11:56 pm

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   Expand viewCollapse view
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   Expand viewCollapse view
Sub OnSheetContentChange(e)
print e.AbsoluteName
End SubSub OnSheetContentChange(e)
print e.AbsoluteName
End Sub
OpenOffice 4.1.7, Windows 10
needsoundhelp
 
Posts: 14
Joined: Wed May 06, 2020 5:01 pm

Re: Can I change a cell to make a sound? (HELP! NEARLY SOLVE

Postby JeJe » Fri May 08, 2020 12:50 am

I've already spoon fed you the answer.

change 'or play sound to your sound playing code and run addListener to start the listener.
Openoffice 4.1.2
Windows 8
JeJe
Volunteer
 
Posts: 1088
Joined: Wed Mar 09, 2016 2:40 pm

Re: Can I change a cell to make a sound? (HELP! NEARLY SOLVE

Postby needsoundhelp » Fri May 08, 2020 2:16 am

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   Expand viewCollapse view
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
OpenOffice 4.1.7, Windows 10
needsoundhelp
 
Posts: 14
Joined: Wed May 06, 2020 5:01 pm

Re: Can I change a cell to make a sound? (HELP! NEARLY SOLVE

Postby JeJe » Fri May 08, 2020 6:11 am

The sub you mean to call... the PlaySound sub... you've called PlaySound2 not PlaySound.
Openoffice 4.1.2
Windows 8
JeJe
Volunteer
 
Posts: 1088
Joined: Wed Mar 09, 2016 2:40 pm

Re: Can I change a cell to make a sound? (HELP! NEARLY SOLVE

Postby JeJe » Fri May 08, 2020 6:14 am

Code: Select all   Expand viewCollapse view

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




Openoffice 4.1.2
Windows 8
JeJe
Volunteer
 
Posts: 1088
Joined: Wed Mar 09, 2016 2:40 pm

Re: Can I change a cell to make a sound? (HELP! NEARLY SOLVE

Postby needsoundhelp » Fri May 08, 2020 8:11 am

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:



sound.gif


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   Expand viewCollapse view
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
OpenOffice 4.1.7, Windows 10
needsoundhelp
 
Posts: 14
Joined: Wed May 06, 2020 5:01 pm

Re: Can I change a cell to make a sound?

Postby robleyd » Fri May 08, 2020 8:48 am

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.
Cheers
David
Apache OpenOffice 420m2(Build:9821) - Slackware 14.2 - 64 bit
LibreOffice 6.0.7.3 - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine
User avatar
robleyd
Moderator
 
Posts: 3393
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Can I change a cell to make a sound? (HELP! NEARLY SOLVE

Postby JeJe » Fri May 08, 2020 9:14 am

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.
Openoffice 4.1.2
Windows 8
JeJe
Volunteer
 
Posts: 1088
Joined: Wed Mar 09, 2016 2:40 pm

Re: Can I change a cell to make a sound? (HELP! NEARLY SOLVE

Postby JeJe » Fri May 08, 2020 10:22 am

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


And don't do that.
Openoffice 4.1.2
Windows 8
JeJe
Volunteer
 
Posts: 1088
Joined: Wed Mar 09, 2016 2:40 pm

Re: Can I change a cell to make a sound? (HELP! NEARLY SOLVE

Postby needsoundhelp » Fri May 08, 2020 6:24 pm

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!
OpenOffice 4.1.7, Windows 10
needsoundhelp
 
Posts: 14
Joined: Wed May 06, 2020 5:01 pm

Re: Can I change a cell to make a sound? (HELP! NEARLY SOLVE

Postby JeJe » Fri May 08, 2020 6:48 pm

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)
Openoffice 4.1.2
Windows 8
JeJe
Volunteer
 
Posts: 1088
Joined: Wed Mar 09, 2016 2:40 pm

Re: Can I change a cell to make a sound? (Help! Nearly solve

Postby needsoundhelp » Fri May 08, 2020 10:13 pm

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.
Last edited by needsoundhelp on Fri May 08, 2020 10:33 pm, edited 1 time in total.
OpenOffice 4.1.7, Windows 10
needsoundhelp
 
Posts: 14
Joined: Wed May 06, 2020 5:01 pm

Re: Can I change a cell to make a sound? (Help! Nearly solve

Postby JeJe » Fri May 08, 2020 10:27 pm

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.
Openoffice 4.1.2
Windows 8
JeJe
Volunteer
 
Posts: 1088
Joined: Wed Mar 09, 2016 2:40 pm

Re: Can I change a cell to make a sound? (Help! Nearly solve

Postby needsoundhelp » Fri May 08, 2020 10:49 pm

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!
OpenOffice 4.1.7, Windows 10
needsoundhelp
 
Posts: 14
Joined: Wed May 06, 2020 5:01 pm

Re: Can I change a cell to make a sound? (Help! Nearly solve

Postby needsoundhelp » Fri May 08, 2020 11:23 pm

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.
OpenOffice 4.1.7, Windows 10
needsoundhelp
 
Posts: 14
Joined: Wed May 06, 2020 5:01 pm

Re: Can I change a cell to make a sound? (Help! Nearly solve

Postby Villeroy » Sat May 09, 2020 11:25 am

Code: Select all   Expand viewCollapse view
ThisComponent.sheets(0).getCellByPosition(1,1).addModifyListener(oModifyListener)

Cell B2 is the broadcaster.

Code: Select all   Expand viewCollapse view
Sub cellListener_modified(ev)
oCell = ev.Source ' reference to cell B2
addr = oCell.getCellAddress() 'cell address struct of B2
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28431
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Can I change a cell to make a sound? (Help! Nearly solve

Postby needsoundhelp » Sat May 09, 2020 5:51 pm

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?
OpenOffice 4.1.7, Windows 10
needsoundhelp
 
Posts: 14
Joined: Wed May 06, 2020 5:01 pm

Re: Can I change a cell to make a sound? (Help! Nearly solve

Postby JeJe » Sat May 09, 2020 6:05 pm

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.
Openoffice 4.1.2
Windows 8
JeJe
Volunteer
 
Posts: 1088
Joined: Wed Mar 09, 2016 2:40 pm

Re: Can I change a cell to make a sound? (Help! Nearly solve

Postby Villeroy » Sat May 09, 2020 6:47 pm

needsoundhelp wrote: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).
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28431
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 5 guests