[Solved] Creating push button in Calc macro

[manojg]

Hi guys,

I am trying to create a push button on the calc spreadsheet using macro. On push it should run a macro which I already have created and works fine.
I recorded the macro for creating the push button and connecting it to execute the macro, and it worked fine. But when I tried to run the saved macro (by closing the calc and opening it again), the push button was not created although its macro was saved.

What I am trying to do is to write a macro (not record, recorded macro looks terrible) to create a push button and connect it to run another macro. I googled about it but they all talked about recording the macro or by using mouse. Any suggestion and helpful information will be appreciated.

Thanks.

[FJCC]

Here is some code I found in my archive of macros. It doesn't look like something I would write but I failed to note where I got it.

Code: Select all

Sub CreateButton 
  oDoc = ThisComponent
  oSheet = oDoc.Sheets.getByIndex(1)
  oDrawPage = oSheet.DrawPage  'Was oDrawPage = oDoc.getDrawPage()
 
  sScriptURL = "vnd.sun.star.script:Standard.Module1.ButtonPushEvent?language=Basic&location=document" 'Was location=application
  oButtonModel = AddNewButton("Button_1", "Button_1", oDoc, oDrawPage)
  oForm = oDrawPage.getForms().getByIndex(0)
  ' find index inside the form container
  nIndex = GetIndex(oButtonModel, oForm)
  AssignAction(nIndex, sScriptURL, oForm)
End Sub

' assign sScriptURL event as css.awt.XActionListener::actionPerformed.
' event is assigned to the control described by the nIndex in the oForm container
Sub AssignAction(nIndex As Integer, sScriptURL As String, oForm As Object)
  aEvent = CreateUnoStruct("com.sun.star.script.ScriptEventDescriptor")
  With aEvent
    .AddListenerParam = ""
    .EventMethod = "actionPerformed"
    .ListenerType = "XActionListener"
    .ScriptCode = sScriptURL
    .ScriptType = "Script"
  End With
 
  oForm.registerScriptEvent(nIndex, aEvent)
End Sub


Function AddNewButton(sName As String, sLabel As String, oDoc As Object, oDrawPage As Object) As Object
  oControlShape = oDoc.createInstance("com.sun.star.drawing.ControlShape")

  aPoint = CreateUnoStruct("com.sun.star.awt.Point")
  aSize = CreateUnoStruct("com.sun.star.awt.Size")
  aPoint.X = 1000
  aPoint.Y = 1000
  aSize.Width = 3000
  aSize.Height = 1000
  oControlShape.setPosition(aPoint)
  oControlShape.setSize(aSize)

  oButtonModel = CreateUnoService("com.sun.star.form.component.CommandButton")
  oButtonModel.Name = sName
  oButtonModel.Label = sLabel
 
  oControlShape.setControl(oButtonModel)
  oDrawPage.add(oControlShape)
 
  AddNewButton = oButtonModel
End Function


Function GetIndex(oControl As Object, oForm As Object) As Integer
  Dim nIndex As Integer
  nIndex = -1
  For i = 0 To oForm.getCount() - 1 step 1
    If EqualUnoObjects(oControl, oForm.getByIndex(i)) Then
      nIndex = i
      Exit For
    End If
  Next
  GetIndex = nIndex
End Function


Sub ButtonPushEvent(ev as com.sun.star.awt.ActionEvent)
  Print "HERE"
  'msgbox ev.Source.Model.Name
End Sub

[Villeroy]

=HYPERLINK("vnd.sun.star.script:Library.Module.Routine?language=Basic&location=document";"Push Button")&T(STYLE("Button"))
"Button" is some cell style that makes a cell look like a button.

[manojg]

Thanks guys. I am using FJCC's code and it works to create the button. However, I am still working how to connect this button to execute the macro.

[FJCC]

The sub called AssignAction uses the value of sScriptURL to assign a macro to the button. In the code I posted, sScriptURL has the value
"vnd.sun.star.script:Standard.Module1.ButtonPushEvent?language=Basic&location=document"
which causes the button to call the sub ButtonPushEvent in Module1 of the Standard library of the document.

[manojg]

Hi FJCC,

I got following error when I pressed the push button (plz see the attached image). Here "Options" is the name of the module and for test I have kept the same code "ButtonPushEvent"

[FJCC]

Here is a document in which the macro works. Delete the button on Sheet2 and then run the macro to replace it. Can you post a non-functioning document if this doesn't solve your problem?

[manojg]

Now I got very strange thing. If I load your macro then it works even if I modify it. However, if I copy your macro to "My Macros & Dialogs" and run then the push button gives the same error as before. I have no idea why. I checked, everything is identical. Does it has to do any other things?

[FJCC]

If your macro is in My Macros then the last bit of sScriptURL has to be location=application
"vnd.sun.star.script:Standard.Module1.ButtonPushEvent?language=Basic&location=application"

[manojg]

Yes, I have already changed the Module1 to the name of my module. I will work on this again tomorrow and let us see what is wrong.

[Villeroy]

&location=document
if the macro is stored in the document.

[manojg]

Yep, that was the problem. I changed &location=document to &location=application and it works fine. Thanks for helping me out.

But another concern, why it is such a terribly slow? I made the the push button to recalculate (calculation is just two lines simple addition/subtraction/multiplication/division). when I push the button it should clear the sheet (only 20 columns and 80 rows) and then should print the recalculated values on the sheet.
First to clear the sheet it took about 1-2 sec and then waited for about another 2-4 sec and then printed the new values. It is terrible. I wonder other API like C++ or Python (if there are) might be faster? Have anybody used other APIs? . I am using OpenOffice 4.0.1 with Fedora 14 on Intel 1.6 GHz Duo core and 2GB ram.

If you guys don't mind, I want to put another question. Is there way to format the text in the cell, like change the text size, font, alignment, color etc. in the macro?

Thanks.

[Villeroy]

F9 is the shortcut to recalculate in Calc, Excel and Gnumeric.
You can define your own tool bar button to recalculate (Tools>Customize...).
".uno:Calculate" is the command URL which does the same. You can add a button calling this URL instead of a http: or file: URL. It will trigger the calculation command in the same way as F9. You can also add a text hyperlink or a formula hyperlink =HYPERLINK(".uno:Calculate").
In any case it makes absolutely no sense to add the push button itself by macro code. If it has to be a push button for each new document then it should be defined in your document template.