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ᴇʜ, Apr 07 '21 at 08:42
Put the `FolderSelector` routine in a normal module, not in `ThisWorkbook`. Also make sure the module is not called `FolderSelector` too! — Rory, Apr 07 '21 at 08:43
https://stackoverflow.com/questions/36621960/vba-excel-2010-how-to-assign-a-macro-to-a-command-button-that-was-made-with-a — Siddharth Rout, Apr 07 '21 at 10:08

score 2 · Accepted Answer · answered Apr 07 '21 at 08:46

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).

Assign macro to a programmatically added vba button

1 Answers1