Add curved connectors and callout message by maping Cell A1

[vinod.excel]

Hi All,

I am trying to create "Curved Connectors Ends with Arrow" between two rectangale objects using following code.
For the moment I'm using connecter but I need to use "Curved Connectors Ends with Arrow". Please help me out.

Code: Select all

Function AddConnectorShape(oDoc, oMyPages, Rectangle1, Rectangle2) As Object

    Connectorshape = oDoc.createInstance("com.sun.star.drawing.ConnectorShape")

    'Add the connector shape before setting the glue points, although the gluepoint shapes
    'could be obtained by using the shape name instead of the index
    oMyPages.Add (Connectorshape)
    
    'Add the glue points to the connector
    
    'Get Rectangle 1
    Connectorshape.StartShape = Rectangle1
    'ConnectorShape.StartShape = ConnectorCurve
    Connectorshape.StartGluePointIndex = 2      'Connect to the bottom
    
    'Rectangle 2
    Connectorshape.EndShape = Rectangle2
    Connectorshape.EndGluePointIndex = 0        'Connect to the top
    
    'Use arrows on the connector line
    Connectorshape.LineEndName = "Arrow"

    AddConnectorShape = Connectorshape
End Function

Also I have created Callout bubble text and assigning the text/message from cell A1, but when I change the message in cell A1 it should update Callout message as well, can you tell me how to do this?

Callout code:

Code: Select all

   strRecTxt = Worksheets("Sheet1").Cells(1,1)

   PropVal(0).Name = "Type"
   PropVal(0).Value = "round-rectangular-callout"  '"rectangular-callout"
   Doc = ThisComponent

   Sheet = Doc.Sheets.getByName(WS.Name)
   DrawPage = Sheet.DrawPage
   Callout = Doc.createInstance("com.sun.star.drawing.CustomShape")
   DrawPage.add(Callout)


   CalloutShape = DrawPage.getByIndex(DrawPage.count-1)
   Callout.CustomShapeGeometry = PropVal()
   Callout.FillColor = RGB(255,255,255)
   Callout.LineColor = RGB(0,0,0)
   Callout.Position = Point 'Pos
   Callout.Size = Size
   Callout.String = calloutmesg
   DrawPage.add(Callout)

Please share your thoughts by providing solution for above two cases and it can be highly appreciated.

Thanks in advance.

Best Regards,
Vinod

[hanya]

Set EdgeKind property of your connector to css.drawing.ConnectorType.CURVE from http://api.openoffice.org/docs/common/r ... rType.html .

To change the text of Callout bubble according to A1 cell, try to use modification listener. Search for addModifyListener in the forum.

[vinod.excel]

Thanks Hanya for your response, on top of your answers, I have below 2 queries

Currved connector:
EdgeKind property works for me along with this I would like to know the excel VBA quavalent code for "Selection.ShapeRange.ConnectorFormat.Parent.RerouteConnections" Open Office.

Callout bubble:Without modification listner approach, Is there a away to achieve dynamic message for callout bubble like in excel VBA code (after selecting callout object running this VBA statement) "ExecuteExcel4Macro "FORMULA(""=Sheet1!R[1]C[1]"")"".

Please share your thoughts by providing solution for above two queries and it can be highly appreciated.

Thanks in advance.

Best Regards,
Vinod

[hanya]

Currved connector:
EdgeKind property works for me along with this I would like to know the excel VBA quavalent code for "Selection.ShapeRange.ConnectorFormat.Parent.RerouteConnections" Open Office.

I have not seen the way to do.

Callout bubble:Without modification listner approach, Is there a away to achieve dynamic message for callout bubble like in excel VBA code (after selecting callout object running this VBA statement) "ExecuteExcel4Macro "FORMULA(""=Sheet1!R[1]C[1]"")"".

Write your own function for it.

[vinod.excel]

Hi Hanya,

I have applied Modify listner for input sheet so that whenever there is a change in range then target sheet rectangle text gets updated. Below is the code I have made...

Code: Select all

Sub AddListener(lstRow)
Dim Doc, Sheet, Cell as Object

	Doc = ThisComponent
	Sheet = Doc.Sheets.getByName("Input Datasheet")  'Input sheet
	
	'CellRng = Sheet.getCellrangeByName("A2:L65536") 
	CellRng = Sheet.getCellrangeByName("A2:L" & lstRow) 

	oListener = createUnoListener("Modify_","com.sun.star.util.XModifyListener")  'create a listener
	Cellrng.addModifyListener(oListener)  'register the listener with Modify_modified (oEv) 
	

End Sub

Now I have comeup with new question that, if the target sheet deleted then I wanted to remove/dispose the listner from Input sheet for all the cells.

Is there a way to dispose/unregister the modify lister while deleting target sheet?

Your quick response will be appreciated.

Thanks in Advnace.

[hanya]

Is there a way to dispose/unregister the modify lister while deleting target sheet?

I have never seen to do surely. It seems you can add ModifyListener to the document model but it takes all modifications on your document and you have to check what is changed.

UNO component model has mechanism to get a component disposing which is provided by css.lang.XComponent interface. But a spreadsheet does not support it. And also, if you remove a sheet from your document, it is not going to be disposed because the application keeps it for undo-ability. This means we can not use all listeners based on css.lang.XEventListener. Other objects like a drawing shape and something document content is the same.