Execute a sub Function on a button which is created programatically

Question

im rather new to VBA. MY problem is:

I create a Button when I Click another Button: the create Button is created as followed:

Set t = ActiveSheet.Range(Cells(i +6, 4), Cells(i+6, 5)) '+6 to start at cell 6
    Set buttons =   ActiveSheet.Buttons.Add(t.Left, t.Top , t.Width, t.Height)
        With buttons
            .OnAction = ""  
            .Caption = "Add TestCase to  " & sReturn
            .Name = "Btn" & i

        End With

And I want to have an function in "OnAction". But I just doesn't Work, I receive a "Syntax Error". Basically I just want to have a function executed on a Click on a button which is created programatically. I also looked several forums for this problem and nothing worked, I think cause it is libreoffice.
Edit: the solutions work in Excel not in Libreoffice

On what line is error? And what do you mean by "And I want to have an function in "OnAction" " ? — QHarr, Apr 04 '18 at 08:25
Hello, the error is in line .OnAction = "add_simple_Line_for_useCaseSheet" The button creation works if I leave OnAction blank add_simple_Line_for_useCaseSheet is a function — Florian Pilsl, Apr 04 '18 at 08:39
The error says what ? and does your function 'add_simple_Line_for_useCaseSheet ' have arguments ? — LatifaShi, Apr 04 '18 at 08:50
Hello, my function add_simple_Line_for_useCaseSheet is a function without parameters. it looks like sub add_simple_Line_for_useCaseSheet some Code End sub — Florian Pilsl, Apr 04 '18 at 09:18
Please ask a complete question. Do not add many comments all over here, edit your question to make it better. — Michael Piefel, Apr 06 '18 at 18:24

QHarr · Accepted Answer · 2018-04-04T11:53:39.097

1

For Excel:

Using a direct example from here:

Sub test()

    Dim t As Range
    Dim Buttons As Object

    Set t = ActiveSheet.Range(Cells(i + 6, 4), Cells(i + 6, 5)) '+6 to start at cell 6
    Set Buttons = ActiveSheet.Buttons.Add(t.Left, t.Top, t.Width, t.Height)

    With Buttons
        .OnAction = "'Btn3 ""Hello"", 123'"
        .Caption = "Add TestCase to  " & sReturn
        .Name = "Btn" & i

    End With

End Sub

Sub Btn3(strString As String, iInt As Integer)
    MsgBox "String = " & strString & vbNewLine & "Integer = " & iInt
End Sub

Caveat:

Unable to test on Office Libre. This may be unsupported functionality.

edited Apr 04 '18 at 11:53

answered Apr 04 '18 at 08:30

QHarr

83,427
12
54
101

Thank you for taking your time to answer. I copied your example and looked on the example on ozgrid.com and I still got an error in the .OnAction line. BASIC runtime Error '1' Type: com.sun.star.uno.RuntimeException Message: . Thats my error Message Im using the newest libre office. Do you think your example works in Libre office ? i have no idea why it doesn't work, every example I try results in the same result. – Florian Pilsl Apr 04 '18 at 08:58
It may indeed be a problem with what is supported in Libre Office. What is the rest of the message? BASIC runtime Error '1' Type: com.sun.star.uno.RuntimeException Message: ......... – QHarr Apr 04 '18 at 09:00
Hello, no there is no information besides BASIC runtime error. '1' Type: com.sun.star.uno.RuntimeException Message: thats all. No further information. If I google basic runtime error 1 there is also not much information – Florian Pilsl Apr 04 '18 at 09:17

DisplayName · Answer 2 · 2018-04-04T09:05:47.443

0

go as follows

    With ActiveSheet.Buttons.Add(t.Left, t.Top, t.Width, t.Height)
        .OnAction = "MyMacro" ' change "MyMacro" to your actual macro name
        .Caption = "Add TestCase to  " & sReturn
        .Name = "Btn" & i
    End With

I tested it with the following macro attached to the button:

Sub MyMacro()
    MsgBox "HellO"
End Sub

edited Apr 04 '18 at 09:05

answered Apr 04 '18 at 08:26

DisplayName

13,283
2
11
19

Hello, I did it that way several times. Could you show me how your "MyMacro" looks like ? maybe im missing a keyword or something. – Florian Pilsl Apr 04 '18 at 08:59
Hello, did you test it with Libreoffice 6 ? – Florian Pilsl Apr 04 '18 at 09:26
I didn’t. And I can’t since I don’t have it – DisplayName Apr 04 '18 at 09:29
And did your function look like sub myNewFunction() With ActiveSheet.Buttons.Add(t.Left, t.Top, t.Width, t.Height) .OnAction = "MyMacro" ' change "MyMacro" to your actual macro name .Caption = "Add TestCase to " & sReturn .Name = "Btn" & i End With End Sub sry I don't know how to comment in code format – Florian Pilsl Apr 04 '18 at 09:30
I’m not by my PC anymore, but I can tell you my Sub was like my shown code plus preceeding 1) dimming of involved variables (e.g: Dim t As Range, Dim i As Long) 2) initialization of i and setting of t range – DisplayName Apr 04 '18 at 10:17

score 0 · Answer 3 · answered Apr 04 '18 at 19:30

im rather new to VBA... the solutions work in Excel not in Libreoffice.

So new that you do not even know what language to use! For some reason, this seems to be a common misunderstanding. Microsoft Excel uses VBA. For LibreOffice, write in LibreOffice Basic.

Complete example code for creating buttons is at https://forum.openoffice.org/en/forum/viewtopic.php?t=27424.

A good place to start learning Basic is http://www.pitonyak.org/oo.php.

Execute a sub Function on a button which is created programatically

3 Answers3