Assign macro to a programmatically added vba button

Question

I try to programmatically adding a VBA button to my workbook. I can creat it but for some reason I can't link a macro on it.

I always get the error message :

Cannot run the macro "xxx". the macro may not be available in this WB or all macros may be disabled

Here is my code :

Private Sub Workbook_Open()
'Remove all old buttons of the worksheet
ActiveSheet.Buttons.Delete
'Restore Folder selector button
Set t = ActiveSheet.Range(Cells(2, 1), Cells(2, 1))
Set btn = ActiveSheet.Buttons.Add(t.Left, t.Top, t.Width, t.Height)
With btn
    .OnAction = "FolderSelector"
    .Caption = "Folder selector"
    .Name = "Folder Selector"
End With
End Sub

Sub FolderSelector()
 MsgBox Application.Caller
End Sub

Is there anyone know what's wrong ?

Is Sub FolderSelector() in a module or in ThisWorkbook scope as the Workbook_Open? The Sub FolderSelector() must be in a module and defined as Public Sub FolderSelector() — Pᴇʜ
– Pᴇʜ, Commented Apr 7, 2021 at 8:42
Put the FolderSelector routine in a normal module, not in ThisWorkbook. Also make sure the module is not called FolderSelector too! — Rory
– Rory, Commented Apr 7, 2021 at 8:43

Pᴇʜ · Accepted Answer · 2021-04-07 08:46:40Z

I recommend the following syntax to avoid odd naming issues:

In ThisWorkbook

Option Explicit

Private Sub Workbook_Open()
    'Remove all old buttons of the worksheet
    ActiveSheet.Buttons.Delete
    'Restore Folder selector button
    Dim t As Range
    Set t = ActiveSheet.Cells(2, 1) 'note this is shorter and the same as .Range(Cells(2, 1), Cells(2, 1))

    Dim btn As Object
    Set btn = ActiveSheet.Buttons.Add(t.Left, t.Top, t.Width, t.Height)

    With btn
        .OnAction = "FolderSelectorButton_Click"
        .Caption = "Folder selector"
        .Name = "FolderSelectorButton"
    End With
End Sub

In a module CommandButtonActions

Option Explicit

Public Sub FolderSelectorButton_Click()
   MsgBox Application.Caller
End Sub

The procedure you call from the button has to be in a normal module and it has to be Public (which is actually default).

Collectives™ on Stack Overflow

Assign macro to a programmatically added vba button

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related