Page 1 of 1
Listener help
Posted: Thu Nov 12, 2009 7:29 am
by shardalule
Hi,
This Macro is running fine for the B2 Cell as its hidng the cells, but now i stuck with the next cell B3 .Even for the B3 cell i wanted the condition should hide the cells.I am attaching the code sample also.
Code: Select all
Sub EventListenerOn
'Dim oSheet As Object
oDoc=ThisComponent
oSheet = oDoc.Sheets(0)
oGroup=oSheet.getCellRangeByName("B1:B7")
'--- installation of an event listener
'oListener = createUnoListener("OOO_","com.sun.star.lang.XEventListener")
oListener = createUnoListener("OOO_","com.sun.star.chart.XChartDataChangeEventListener")
oGroup.addChartDataChangeEventListener(oListener)
End Sub
Sub EventListenerOff
oGroup.removeChartDataChangeEventListener(oListener)
End Sub
Sub OOO_chartDataChanged
dim flt as Object
oDoc = ThisComponent
oSheets = oDoc.getSheets()
oSheet = oSheets.getByName("Sheet1")
oGroup = oSheet.getCellRangeByName("B2")
If oGroup.String="Yes" Then
For i = 2 to 11
oSheet.Rows(i).isVisible = False
Next i
End if
If oGroup.String="No" Then
For i = 2 to 14
oSheet.Rows(i).isVisible = False
Next i
End If
If oGroup.String="Not in ODC palce" Then
For i = 2 to 14
oSheet.Rows(i).isVisible = True
Next i
End If
End Sub
Thanks for the help.
Re: Listener help
Posted: Sat Nov 14, 2009 5:20 am
by FJCC
I copied your code into a file and it works as expected. When any cell in the range B1:B7 is changed the sub OOO_chartDataChanged is called and rows are hidden depending on the value of B2. However, the logic of the row hiding is such that once you start using the Listener only changes to B2 have any visible effect because it is the value of that cell the determines which rows are visible.
shardalule wrote:Even for the B3 cell i wanted the condition should hide the cells.
I'm not sure what you mean by this. If you want changes in the value of B3 to affect which rows are visible, you need to put something in the code of OOO_chartDataChanged that changes which rows are hidden depending based on the value of B3. Could you explain what you want to happen when B3 changes?
Re: Listener help
Posted: Tue Nov 17, 2009 9:28 am
by shardalule
Yes, ofcourse as you can see the value of B2 cell changes the rows get hided but if i change the value of cell B3 and hide the certain rows like example 2 to 12 rows the macro goes into infinite loop and doesnt allow to select any text provided by data validity as yes and no.
I wanted in this macro the celladdress and content of that cell so depend on the cell address and the content of the celll we can hide the rows and run the macro but how to take the address of the cell in the if condition can u suggest on that please.
Thanks in advance!!!!
Re: Listener help
Posted: Thu Nov 19, 2009 6:21 am
by FJCC
I'm not sure I understand what you want to do. If you want to know which cell was actually modified and use that information in the code, you can use the Event object that is passed by the Listener when it is triggered. I have modified your code to show an example of this. I attached a ModifyListener to each individual cell in the range B1:B7 when those cells are modified the OOO_modified is called and receives the Event object as a parameter. The Event has a Source property which is the Cell that triggered the Listener. I then use the AbsoluteName property of the Cell to assign the cell to the oGroup variable. This particular code doesn't do anything different from you original code. It is just an example of using the Event passed by the Listener. Does that help you do what you want?
Code: Select all
Sub EventListenerOn
'Dim oSheet As Object
oDoc=ThisComponent
oSheet = oDoc.Sheets(0)
oGroup=oSheet.getCellRangeByName("B1:B7")
'--- installation of an event listener
'oListener = createUnoListener("OOO_","com.sun.star.lang.XEventListener")
oListener = createUnoListener("OOO_","com.sun.star.util.XModifyListener")
CellB1 = oSheet.getCellRangeByName("B1")
CellB1.addModifyListener(oListener)
CellB2 = oSheet.getCellRangeByName("B2")
CellB2.addModifyListener(oListener)
CellB3 = oSheet.getCellRangeByName("B3")
CellB3.addModifyListener(oListener)
CellB4 = oSheet.getCellRangeByName("B4")
CellB4.addModifyListener(oListener)
CellB5 = oSheet.getCellRangeByName("B5")
CellB5.addModifyListener(oListener)
CellB6 = oSheet.getCellRangeByName("B6")
CellB6.addModifyListener(oListener)
CellB7 = oSheet.getCellRangeByName("B7")
CellB7.addModifyListener(oListener)
'xray oGroup
'oGroup.addModifyListener(oListener)
End Sub
Sub EventListenerOff
oGroup.removeModifyListener(oListener)
End Sub
Sub OOO_modified(oEvent)
dim flt as Object
oDoc = ThisComponent
oSheets = oDoc.getSheets()
oSheet = oSheets.getByName("Sheet1")
CellName = oEvent.Source.AbsoluteName
Print CellName
oGroup = oSheet.getCellRangeByName(CellName)
If oGroup.String="Yes" Then
For i = 2 to 11
oSheet.Rows(i).isVisible = False
Next i
End if
If oGroup.String="No" Then
For i = 2 to 14
oSheet.Rows(i).isVisible = False
Next i
End If
If oGroup.String="Not in ODC palce" Then
For i = 2 to 14
oSheet.Rows(i).isVisible = True
Next i
End If
Re: Listener help
Posted: Tue Dec 22, 2009 8:59 am
by shardalule
As you have taken the range in oGroup from B1 to B7 can we take the one of the address from the range like B1 to compare in the if loop as i wanted the celladdress and the content of the cell to do the task...
Re: Listener help
Posted: Tue Dec 22, 2009 9:22 am
by shardalule
Whats is the problem in this code as i have i wanted the code to be work like this but its hiding for the cell b1 properly.Can u suggest me the where m wrong...
Code pasted below
Please
Code: Select all
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
oCell3= oEvent.Source
oCell5= oEvent.Source
oCell6= oEvent.Source
oCell7= oEvent.Source
oCell8= oEvent.Source
oCell9= oEvent.Source
oCell10= oEvent.Source
oCell11= 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
End sub
Thanks
Re: Listener help
Posted: Wed Dec 23, 2009 1:45 pm
by shardalule
Above code works fine for me but when the value of the other are null or blank if the rows have the value then its not runnning properly and going in infinite loop so how to make the value null of other rows.Please help....
Re: Listener help
Posted: Thu Dec 24, 2009 6:54 am
by FJCC
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
Re: Listener help
Posted: Thu Dec 24, 2009 8:39 am
by shardalule
Macro is very slow its not running properly ......... please help
Re: Listener help
Posted: Fri Dec 25, 2009 6:21 am
by FJCC
I made the Cell variables Global and the code works better. I also removed a mistake I made using the variable oCell in two different ways in the chartDataChanged sub routine.
Code: Select all
GLOBAL oListener as Object
GLOBAL oCell_1
GLOBAL oCell2
GLOBAL oCell3
'oCell4
GLOBAL oCell5
GLOBAL oCell6
GLOBAL oCell7
GLOBAL oCell8
GLOBAL oCell9
GLOBAL oCell10
GLOBAL oCell11
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_1 = 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_1.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_1.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_1.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
Re: Listener help
Posted: Mon Dec 28, 2009 12:17 pm
by shardalule
No i dont think the code is working still the other row having the yes condition if i change the first B2 cell its not hiding the 2 to 14 rows still all the cell should be the null.Is there anything like this in OO eg given below..
Private Sub Worksheet_Change(ByVal Target As Range)
'COLOUMN B STARTS HERE
If Intersect(Target, Range("B1:B56")) Is Nothing Then
Re: Listener help
Posted: Mon Dec 28, 2009 10:28 pm
by FJCC
I don't see anything that makes me think the code is not executing as expected. There is nothing in the code that would cause the hiding of rows with indexes 2 to 14 when B2 is changed. There are only two IF statements that deal with cell B2 and both set the isVisible value to True.
I'm not sure what you mean by
shardalule wrote:still all the cell should be the null
Nothing in the code changes the value of any cell; only the visibility and the background colors change.
It may be that the code is not doing what you want it to do, but I believe it is doing what one one expect it to do.
There may be an OOo method similar to the INTERSECT. The queryIntersection method returns all the cells that are both in the range that calls the method and in the range that is passed as a parameter. The API documentation is
here
Re: Listener help
Posted: Tue Dec 29, 2009 7:05 am
by shardalule
Ya code is working fine but you run by all the cells and then again run the cell B2 it doesnt hide the cell 2 to 14.It should hide the cell so the value should be false there not the true.You just run the coding by putting false for the cell B2.at the first time it run properly but if other cells are yes it doesnt hide the appropriate rows.
Thanks for helping