Inserting and removing text into a textbox on a SHEET in Libreoffice calc BASIC

user_19804219 0
Viewed 46

As I can't solve my problem I'd like to ask someone more experienced.I created simple dialog (4 fields) to let the user enter few data. After clicking "Submit" button those data should be inserted into textboxes put ON THE SHEET (not on any dialog). How to refer to those sheet texboxes in code to insert those data? Other thing is deleting those data after clicking other button "Clear". Suppose it will be similar to inserting but how this piece of code should look like? Thanks in advance.

libreoffice-basic libreoffice-calc libreoffice basic
1 Answers

The trick is to create a com.sun.star.drawing.TextShape object and add it to the Draw Page of the target sheet. The following works for me. You should be able to assign it to the appropriate button on your dialog.

Sub InsertTextBox()

    Dim oDocument As Object
    oDocument = ThisComponent

    If oDocument.SupportsService("com.sun.star.sheet.SpreadsheetDocument") Then

        Dim sText As String
        sText = "Blah,blah, blah!"

        Dim oPosition As New com.sun.star.awt.Point
        oPosition.X = 1000
        oPosition.Y = 1000

        Dim oSize As New com.sun.star.awt.Size
        oSize.Width = 10000
        oSize.Height = 5000

        Dim oTextShape As Object
        oTextShape = oDocument.createInstance("com.sun.star.drawing.TextShape")

        oTextShape.setPosition(oPosition)
        oTextShape.setSize(oSize)
        oTextShape.setPropertyValue("FillStyle", "SOLID")
        oTextShape.Visible = 1

        ' Give it a name so you can find it again when you want to delete it
        oTextShape.setPropertyValue("Name", "Thingy")

        Dim oDrawPage As Object
        oDrawPage = oDocument.getSheets().getByIndex(0).getDrawPage()

        oDrawPage.add(oTextShape)

        ' Set the string of the text shape AFTER adding it to the
        ' draw page, otherwise the text will not be set.
        oTextShape.setString(sText)

    End If

End Sub

In the above routine the TextShape object was given the name "Thingy". You can obviously give it any name you like, but it should be unique. To delete it, you need to loop through all the objects in the draw page, find the one that is a TextShape and has the name you gave it (in this case "Thingy") and remove it. This can be done as follows:

Sub DeleteTextBox()

    Dim oDocument As Object
    oDocument = ThisComponent

    If oDocument.SupportsService("com.sun.star.sheet.SpreadsheetDocument") Then

        Dim oDrawPage As Object
        oDrawPage = oDocument.getSheets().getByIndex(0).getDrawPage()

        Dim oShape As Object
        Dim i As Long
        For i = (oDrawPage.getCount() - 1) To 0 Step -1
            oShape = oDrawPage.getByIndex(i)
            If oShape.SupportsService("com.sun.star.drawing.TextShape") Then
                If StrComp(oShape.getPropertyValue("Name"), "Thingy") = 0 Then
                    oDrawPage.remove(oShape)
                End If
            End If
        Next i
    End If

End Sub

This will delete all objects of type TextShape and named "Thingy"

user_125375250
Related