Programatically added buttons don't launch macros - The macro may not be available in this workbook or all macros may be disabled.

[Skirmish]

Hey All!

I was wondering if someone could help with a problem I'm having trying to implement programatically added buttons that launch a macro.

I'm using the accepted solution from here - excel - How to add a button programmatically in VBA next to some sheet cell data? - Stack Overflow

[COLOR=#101094][FONT=Consolas]Sub[/FONT][/COLOR][COLOR=#303336][FONT=Consolas] a[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]()
[/FONT][/COLOR]<code style="margin: **** padding: **** border: **** font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; white-space: inherit;">[COLOR=#101094]  Dim[/COLOR][COLOR=#303336] btn [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#303336] Button
  Application[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]ScreenUpdating [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#7D2727]False[/COLOR][COLOR=#303336]
  ActiveSheet[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Buttons[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Delete
  [/COLOR][COLOR=#101094]Dim[/COLOR][COLOR=#303336] t [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#303336] Range
  [/COLOR][COLOR=#101094]For[/COLOR][COLOR=#303336] i [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#7D2727]2[/COLOR][COLOR=#101094]To[/COLOR][COLOR=#7D2727]6[/COLOR][COLOR=#101094]Step[/COLOR][COLOR=#7D2727]2[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#101094]Set[/COLOR][COLOR=#303336] t [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] ActiveSheet[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Range[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]Cells[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]i[/COLOR][COLOR=#303336],[/COLOR][COLOR=#7D2727]3[/COLOR][COLOR=#303336]),[/COLOR][COLOR=#303336] Cells[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]i[/COLOR][COLOR=#303336],[/COLOR][COLOR=#7D2727]3[/COLOR][COLOR=#303336]))[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#101094]Set[/COLOR][COLOR=#303336] btn [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] ActiveSheet[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Buttons[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Add[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]t[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Left[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] t[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Top[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] t[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Width[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] t[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Height[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#101094]With[/COLOR][COLOR=#303336] btn
      [/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]OnAction [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#7D2727]"btnS"[/COLOR][COLOR=#303336]
      [/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Caption [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#7D2727]"Btn "[/COLOR][COLOR=#303336]&[/COLOR][COLOR=#303336] i
      [/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Name [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#7D2727]"Btn"[/COLOR][COLOR=#303336]&[/COLOR][COLOR=#303336] i
    [/COLOR][COLOR=#101094]End[/COLOR][COLOR=#101094]With[/COLOR][COLOR=#303336]
  [/COLOR][COLOR=#101094]Next[/COLOR][COLOR=#303336] i
  Application[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]ScreenUpdating [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#7D2727]True[/COLOR][COLOR=#303336]
[/COLOR][COLOR=#101094]End[/COLOR][COLOR=#101094]Sub[/COLOR][COLOR=#303336]

[/COLOR][COLOR=#101094]Sub[/COLOR][COLOR=#303336] btnS[/COLOR][COLOR=#303336]()[/COLOR][COLOR=#303336]
 MsgBox Application[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Caller 
[/COLOR]</code>[COLOR=#101094][FONT=Consolas]End [/FONT][/COLOR][COLOR=#101094][FONT=Consolas]Sub
[/FONT][/COLOR]

The buttons are added successfully but don't call the macros when pushed. Instead they give the error

Cannot run the macro "%workbookname%'!btnS'. The macro may not be available in this workbook or all macros may be disabled.

%workbookname% is just the placeholder for the name of the actual workbook the code is in.

I've tried making the function btnS public in my version, and tried moving the functions from 'This workbook' to 'Sheet 1' but nothing's made a difference, I sill get the error listed above.

Could anyone point out where I'm going wrong? I'd really appreciate it!

Thanks in advance!

 

[mikerickson]

If that code is in a normal module, it should work.
If it is in a sheet's code module then changing this should work (change Sheet1 to match the code name of your sheet)

.OnAction ="Sheet1.btnS"

 

[Skirmish]

Thanks for the reply Mike!
The code I posted it still refuses to work in the ThisWorkbook area.
If I modify it with your code it does work in the Sheet1 area.

I'm not sure why it's not working in ThisWorkbook though :/

I'm running Excel 2010 32bit if that makes any difference?

 

[mikerickson]

ThisWorkbook is an object and routines in it have to be preceded by its name, unlike normal modules.
Change the line to

.OnAction ="ThisWorkbook.btnS"

And, just for luck, you might want to explicitly declare btnS as Public

Public Sub btnS()