Add a button and assign a function on press

Question

On vba, I'd like to add a button and assign to it a function with a parameter.

That's code to the create the button (in module1):

Set testBtn = Worksheets("HOME").Buttons.Add(Position.Left, Position.Top, Position.Width * 2, Position.Height * 2)
With testBtn
           .OnAction = Send_click(ActiveSheet.Name)
           .Caption = "LOAD " & ActiveSheet.Name
           .Name = "LOAD " & ActiveSheet.Name
           .Font.Bold = True
 End With

while the function to call is defined in another module as:

Function Send_click(sheetName As String)

However, the button appears as not clickable and a macro LOADxxx_click appears as assigned (where xxx is the name of the ActiveSheet.name) but it clearly doesn't exist.

I have also tried suggestions I found here and also to specify the module:

.OnAction = Module4.Send_click(ActiveSheet.Name)

with no different results.

score 0 · Answer 1 · edited Mar 09 '21 at 13:55

I believe you should assign a string value to the .OnAction property of a button object.

If the function (Send_click in this case) to be called is set to be public, then you just need to construct an OnAction string like this:

With testBtn
  .OnAction = "'" & "Send_click" & " " & """" & ActiveSheet.Name & """" & "'"
End With

The string concatenation part should return something like:

'Send_click "whatever_the_sheet_name"'

score -1 · Accepted Answer · answered Aug 28 '19 at 10:18

-1

I'd write another macro:

Sub ExecuteSendClick()

    Send_Click ActiveSheet.Name

End Sub

And assign that macro to the button.

answered Aug 28 '19 at 10:18

Damian

5,034
1
9
21

Well, it is not exactly clean. But as a workaround does its job. – Akinn Aug 28 '19 at 11:52

Add a button and assign a function on press

2 Answers2