It is easy to get an infinite loop with a Listener if your code acts on a cell that contains a Listener. The action calls the Listener again, which then acts on the cell a second time, which calls the Listener again and this cycles forever. The way to avoid this is to remove the Listener before acting on any cells and then restoring the Listener when you are done. To accomplish this, oListener has to be available in the Sub OOO_chartDataChanged, so I declared it as a Global variable. I tried a few things and the code does seem to run with out crashing. It is slow, however, perhaps because of all the adding and removing of Listeners. The only other change I made was removing the multiple assignment of oEvent.Source. You should only need to assign it once.
Code: Select all
GLOBAL oListener as Object
Sub Hiding
dim document as object
Dim oCell as Object
Dim destCellAddr as object
oDoc = ThisComponent
oSheets = oDoc.getSheets()
oSheet = oSheets.getByName("Sheet1")
oSheet2 = oSheets.getByName("Sheet2")
'Get cells on Sheet1
oCell = oSheet.getCellRangeByName("B2")
oCell2 = oSheet.getCellRangeByName("B3")
oCell3 = oSheet.getCellRangeByName("B4") ' this the cell 3
'oCell4 = oSheet.getCellRangeByName("B5") ' this the cell 4
oCell5 = oSheet.getCellRangeByName("B6") 'this is the cell 5
oCell6 = oSheet.getCellRangeByName("B7") 'this the cell 6
oCell7 = oSheet.getCellRangeByName("B8") 'this the cell 7
oCell8 = oSheet.getCellRangeByName("B9") 'this the cell 8
oCell9 = oSheet.getCellRangeByName("B10") 'this the cell 9
oCell10 = oSheet.getCellRangeByName("B11") 'this the cell 10
oCell11 = oSheet.getCellRangeByName("B12") 'this the cell 11
'this the cell 6
'Get cells on Sheet2
'oCell4 = oSheet2.getCellRangeByName("B2")
'oCell5 = oSheet2.getCellRangeByName("B3")
oListener = CreateUnoListener( "OOO_", "com.sun.star.chart.XChartDataChangeEventListener" )
'Assign Listener to cell on Sheet1
oCell.addChartDataChangeEventListener(oListener)
oCell2.addChartDataChangeEventListener(oListener)
oCell3.addChartDataChangeEventListener(oListener)
'oCell4.addChartDataChangeEventListener(oListener)
oCell5.addChartDataChangeEventListener(oListener)
oCell6.addChartDataChangeEventListener(oListener)
oCell7.addChartDataChangeEventListener(oListener)
oCell8.addChartDataChangeEventListener(oListener)
oCell9.addChartDataChangeEventListener(oListener)
oCell10.addChartDataChangeEventListener(oListener)
oCell11.addChartDataChangeEventListener(oListener)
'Assign Listener to cell on Sheet2
'oCell3.addChartDataChangeEventListener(oListener)
'oCell4.addChartDataChangeEventListener(oListener)
msgbox "Listener Runing"
End Sub
Sub OOO_chartDataChanged(oEvent)
oCell = oEvent.Source
oCell2= oEvent.Source
oDoc = ThisComponent
oSheets = oDoc.getSheets()
oSheet = oSheets.getByName("Sheet1")
oSheet2 = oSheets.getByName("Sheet2")
'Get cells on Sheet1
oCell = oSheet.getCellRangeByName("B2")
oCell2 = oSheet.getCellRangeByName("B3")
oCell3 = oSheet.getCellRangeByName("B4") ' this the cell 3
'oCell4 = oSheet.getCellRangeByName("B5") ' this the cell 4
oCell5 = oSheet.getCellRangeByName("B6") 'this is the cell 5
oCell6 = oSheet.getCellRangeByName("B7") 'this the cell 6
oCell7 = oSheet.getCellRangeByName("B8") 'this the cell 7
oCell8 = oSheet.getCellRangeByName("B9") 'this the cell 8
oCell9 = oSheet.getCellRangeByName("B10") 'this the cell 9
oCell10 = oSheet.getCellRangeByName("B11") 'this the cell 10
oCell11 = oSheet.getCellRangeByName("B12") 'this the cell 11
oCell.removeChartDataChangeEventListener(oListener)
oCell2.removeChartDataChangeEventListener(oListener)
oCell3.removeChartDataChangeEventListener(oListener)
'oCell4.removeChartDataChangeEventListener(oListener)
oCell5.removeChartDataChangeEventListener(oListener)
oCell6.removeChartDataChangeEventListener(oListener)
oCell7.removeChartDataChangeEventListener(oListener)
oCell8.removeChartDataChangeEventListener(oListener)
oCell9.removeChartDataChangeEventListener(oListener)
oCell10.removeChartDataChangeEventListener(oListener)
oCell11.removeChartDataChangeEventListener(oListener)
oCell = oEvent.Source
'oCell2= oEvent.Source
CellRow = oCell.CellAddress.Row
CellSheet = oCell.CellAddress.Sheet
oSheet = oCell.SpreadSheet 'This will be either Sheet1 or Sheet2 depending on which cell called the Listener
IF CellSheet=0 and CellRow = 1 then 'Cell B2 has CellRow value 1
If oCell.String="Yes" Then
For i = 2 to 14
oSheet.Rows(i).isVisible = True
Next i
End if
End if
If CellSheet = 0 and CellRow = 1 then 'Cell B2 has CellRow value 1
If oCell.String="Not in ODC palce" Then
For i = 2 to 14
oSheet.Rows(i).isVisible = True
Next i
End If
End if
If CellSheet = 0 and CellRow = 2 then 'Cell B2 has CellRow value 1
If oCell2.String="No" Then
For i = 3 to 12
oSheet.Rows(i).isVisible = False
Next i
End If
End if
If CellSheet = 0 and CellRow = 2 then 'Cell B2 has CellRow value 1
If oCell2.String="Yes" Then
For i = 3 to 14
oSheet.Rows(i).isVisible = True
Next i
End If
End if
If CellSheet = 0 and CellRow = 3 then 'Cell B2 has CellRow value 1
If oCell3.String="Yes" Then
oCell3.CellBackColor =RGB(35,255,35)
For i = 4 to 14
oSheet.Rows(i).isVisible = True
Next i
End If
End if
If CellSheet = 0 and CellRow = 3 then 'Cell B2 has CellRow value 1
If oCell3.String="No" Then
oSheet.Rows(4).isVisible = false
oCell3.CellBackColor =RGB(255,0,0)
End If
End if
If CellSheet = 0 and CellRow = 5 then 'Cell B2 has CellRow value 1
If oCell5.String="Yes" Then
For i = 6 to 14
oSheet.Rows(i).isVisible = True
Next i
oCell5.CellBackColor =RGB(35,255,35)
ElseIf oCell5.String="No" Then
oSheet.Rows(6).isVisible = false
oCell5.CellBackColor =RGB(255,0,0)
End If
End if
If CellSheet = 0 and CellRow = 6 then 'Cell B2 has CellRow value 1
If oCell6.String="Yes" Then
For i = 7 to 14
oSheet.Rows(i).isVisible = True
Next i
oCell6.CellBackColor =RGB(255,255,255)
ElseIf oCell6.String="No" Then
oSheet.Rows(7).isVisible = false
oCell6.CellBackColor =RGB(255,0,0)
End If
End if
If CellSheet = 0 and CellRow = 7 then 'Cell B2 has CellRow value 1
If oCell7.String="Yes" Then
For i = 8 to 14
oSheet.Rows(i).isVisible = True
Next i
oCell7.CellBackColor =RGB(35,255,35)
ElseIf oCell7.String="No" Then
For i = 8 to 14
oSheet.Rows(i).isVisible = True
Next i
oCell7.CellBackColor =RGB(255,0,0)
End If
End if
If CellSheet = 0 and CellRow = 8 then 'Cell B2 has CellRow value 1
If oCell8.String="Yes" Then
For i = 9 to 14
oSheet.Rows(i).isVisible = True
Next i
oCell8.CellBackColor =RGB(255,255,255)
ElseIf oCell8.String="No" Then
oSheet.Rows(9).isVisible = false
oSheet.Rows(10).isVisible = false
oCell8.CellBackColor =RGB(35,255,35)
End If
End if
If CellSheet = 0 and CellRow = 9 then 'Cell B2 has CellRow value 1
If oCell9.String="Yes" Then
For i = 10 to 14
oSheet.Rows(i).isVisible = True
Next i
oCell9.CellBackColor =RGB(35,255,35)
ElseIf oCell9.String="No" Then
For i = 10 to 14
oSheet.Rows(i).isVisible = True
Next i
oCell9.CellBackColor =RGB(255,0,0)
End If
End if
If CellSheet = 0 and CellRow = 10 then 'Cell B2 has CellRow value 1
If oCell10.String="Yes" Then
For i = 11 to 14
oSheet.Rows(i).isVisible = True
Next i
oCell10.CellBackColor =RGB(35,255,35)
ElseIf oCell10.String="No" Then
For i = 11 to 14
oSheet.Rows(i).isVisible = True
Next i
oCell10.CellBackColor =RGB(255,0,0)
End If
End if
If CellSheet = 0 and CellRow = 11 then 'Cell B2 has CellRow value 1
If oCell11.String="Yes" Then
For i = 12 to 14
oSheet.Rows(i).isVisible = True
Next i
oCell11.CellBackColor =RGB(35,255,35)
ElseIf oCell11.String="No" Then
For i = 11 to 14
oSheet.Rows(i).isVisible = True
Next i
oCell11.CellBackColor =RGB(255,0,0)
End If
End if
If CellSheet = 0 and CellRow = 12 then 'Cell B2 has CellRow value 1
If oCell12.String="Yes" Then
For i = 12 to 14
oSheet.Rows(i).isVisible = True
Next i
oCell12.CellBackColor =RGB(35,255,35)
ElseIf oCell12.String="No" Then
For i = 12 to 14
oSheet.Rows(i).isVisible = True
Next i
oCell12.CellBackColor =RGB(255,0,0)
End If
End if
oCell.addChartDataChangeEventListener(oListener)
oCell2.addChartDataChangeEventListener(oListener)
oCell3.addChartDataChangeEventListener(oListener)
'oCell4.addChartDataChangeEventListener(oListener)
oCell5.addChartDataChangeEventListener(oListener)
oCell6.addChartDataChangeEventListener(oListener)
oCell7.addChartDataChangeEventListener(oListener)
oCell8.addChartDataChangeEventListener(oListener)
oCell9.addChartDataChangeEventListener(oListener)
oCell10.addChartDataChangeEventListener(oListener)
oCell11.addChartDataChangeEventListener(oListener)
End sub