Attaching a toolbar to an Excel add-in

[Nelviticus]

Hello,

I've created a spreadsheet with some forms, a few modules and its own toolbar and I've attached the toolbar to the spreadsheet.

However, if I save it as an add-in then whenever I click the toolbar button it opens up my original spreadsheet and tries to run the code in that.

I created a little function to view the OnAction property of the toolbar button and it shows the full UNC path to my spreadsheet. I've tried setting it with code to just the function name but it doesn't change.

Any idea how I attach a toolbar to an add-in so that the buttons reference the code in the add-in? It's doing my head in!

Thanks

Nelviticus

 

[Nelviticus]

One extra bit of info - the toolbar button has a custom bitmap so I can't create it at run-time.

N.

 

[combo]

When you create and add toolbar to the woorkbook it does not disappear when you close the workbook. Again, when you save the workbook as an add-in, you have both files, i.e. an add-in and original xls file. All have toolbars inside. This makes some mess.
To initially clean it and make sure you deal with add-in's toolbar:
1. check macro, assign custom toolbar to workbook, save it,
2. save your xls file as an add-in,
3. close xls file and delete custom toolbar,
4. install an add-in.

combo

 

[Nelviticus]

I tried that but it still didn't work. It seems that when you create the toolbar button and assign a macro to it, the full path to the macro - UNC path, file name, module and procedure name - are attached to the button's OnAction property. When you save it as an add-in, even though the file name and the path to it change the OnAction property of the button isn't updated, so it still refers to the 'old' code.

I managed to get around this in the end by adding a 'Workbook_Open' procedure which sets the OnAction property to Module.Function - in my case "modMain.StartProcess". It then seems to figure out that you mean the module in this workbook. If you leave out the module name and just use the function name, it doesn't work because it doesn't change the path.

Even though my solution works, I just can't believe that this is the right way of doing it. Surely adding custom buttons to run code in add-ins is a pretty common thing? Crazy.

Regards

Nelviticus