Listener help

[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   Expand viewCollapse view

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.

[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.

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?

[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!!!!

[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   Expand viewCollapse view

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


[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...

[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   Expand viewCollapse view

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

[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....

[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   Expand viewCollapse view

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

[shardalule]

Macro is very slow its not running properly ......... please help

[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   Expand viewCollapse view

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

[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

[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

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

[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