Multiple Instances of DataChangeEventListener

[JessieJames]

Hi All,

Plain English Goal: I want to enter a value into A2, when a value is entered it triggers an input box, the value of the inputted data goes into B2, depending on B2s value, another input box comes up.

Achieved: I’ve gotten A2 change to trigger input box which then goes into B2. See code below +++

Fail: Cannot get a second instance going, everything I try triggers loops, crashes, or other fuggly things. I’m guessing that I have to remove the one instance of the listener and then get another one established for B2.

Thoughts appreciated.

+++++

Sub addlistener

   dim document as object
   oDoc = StarDesktop.CurrentComponent
   oSheets = oDoc.getSheets()
   oSheet = oSheets.getByName("Sheet1") ' Of cause you can specify any other sheet
   oCell = oSheet.getCellRangeByName("A2") 'Of cause you can specify any other cell

   oListener = CreateUnoListener( "OOO_", "com.sun.star.chart.XChartDataChangeEventListener" )
   oCell.addChartDataChangeEventListener(oListener)

End Sub

Sub OOO_chartDataChanged

rem This is the body/code of the Macro called "Testcell"

   oDoc = StarDesktop.CurrentComponent
   oSheets = oDoc.getSheets()
   oSheet = oSheets.getByName("Sheet1")
   oCell = oSheet.getCellRangeByName("A2")
   rem oCell = oView.getSelection() ' "un-rem" this line to test the cell selected by ' cursor rather then cell E28 - "rem" previous line
   nValue = oCell.getValue()
   nString = oCell.getString()
   nFormula = oCell.getFormula()
      
   Dim sFM as String
      sFM = InputBox ("Is this a felony or misdo? (F/M)")
      sFMCell = oSheet.getCellRangeByName("B2")
      sFMCell.string = sFM   
end sub

[JohnSUN]

1. You don’t need separate event handlers for individual cells - analyze in the code of the procedure which of the cells called the event and perform the corresponding actions

2. Pass the event information to the procedure using a parameter (you do not use parameters in your code - why?)

3. Now you do not need to create a handler for this event with the help of the CreateUnoListener - write the required code, right-click on the sheet’s tab and assign your procedure to the desired event

Sub dataWasChanged(oEvent As Variant)
	Select Case oEvent.AbsoluteName
		Case "$Sheet1.$A$2"
			Do anything  
		Case "$Sheet1.$B$2"
			Do anything other
	Case Else 
		Do nothing
	End Select 
End Sub

[JessieJames]

Thanks!

Did this:

Sub dataWasChanged(oEvent As Variant)
Select Case oEvent.AbsoluteName
    Case "$Sheet1.$A$2"
        MsgBox ("Test A2")  
End Select 

End Sub

Weird thing is that when I change A2 in Sheet1, the msgbox appears but I get an error:

Thoughts appreciated.

[JohnSUN]

It seems to me that you are trying to run the macro manually and Basic does not see the value of the oEvent parameter, which should have been generated automatically. Did you set the macro in the sheet event?