can't create a button from Personal.XLSB Excel VBA

[dmills335]

Hello,

i have a strange issue with Personal.XLSB where it won't let me create a button from there.
if i use the code below within the File instead of Personal it works every time.

the code i am using is:
ActiveSheet.Buttons.Add(47, 43, 41, 35).Select
Selection.Name = "Test"
Selection.OnAction = "ThisWorkbook.Test_Click"

because the button isn't being created, i get the failure message:
"Run-time error '5':
Invalid procedure or call argument"

any help would be greatly appreciated.
thanks in advance.

 

[RoryA]

Where in the Personal.xlsb file is that code located?

 

[dmills335]

it's located within "ThisWorkbook" of "PERSONAL.XLSB"

 

[RoryA]

That's why it's failing. In the ThisWorkbook module, ActiveSheet refers to the active sheet in that workbook. Since the personal macro workbook is hidden, you can't select a button you add to it. Are you trying to add a button to the active sheet in the active workbook that calls a macro in the ThisWorkbook module of Personal.xlsb? If so, you should put the macro into a normal module, and use code like this:

With Activeworkbook.ActiveSheet.Buttons.Add(47, 43, 41, 35)
.Name = "Test"
.OnAction = "Personal.xlsb!Test_Click"
End With

 

[dmills335]

Hi Rory,
thanks for the reply; it works if i insert it into a module as you suggest.
however, my problem is that i download data from a server into a spreadsheet and every time i do this a new file is created with it's own unique filename.
i thought i'd be able to get around this by using Personal.xlsb as it's impractical to set up a new module every time i download the data i need.

based on your advice, i may need to look at creating a ribbon tab.

many thanks,
derek

 

[RoryA]

The module can be in Personal.xlsb - it doesn't have to be in the data workbook.