[RESOLVED] [Excel] Custom Toolbar for Every New Worksheet

Hi,

I am able to create a customized toolbar that runs a specific macro linked to it. My problem is that it is only associated with that specific worksheet where the macro is stored. If I close the worksheet with the macroand open a new worksheet, the custom toolbar ca't find the macro.

How do I create custom toolbars that will always be available in Excel, to use in any worksheet open at the time.

I am using the office VB design mode for coding. Can one only do this using VB.Net or similar?

Thanks

I would have thought the best way to have your toolbar & Macro available across workbooks would be to save it as an Add-In.

Create your toolbar & associated macros in a new workbook, then when you save it change the file extension in the dropdown list to Microsoft Office Excel Add-In (*.xla), at the bottom of the list, and save it.

To install the Add-In, go to Tools | Add-Ins in excel find your Add-In in the list, and add a check mark to its name.

You toolbar should now be available across workbooks.

If you dont want to go through the trouble of creating an Add-In your other option is when recording your Macro, change the recording location.

When you record a macro you get the option to Store Macro In and the default will be - This Workbook.

Change this to Personal Macro Workbook, it will save your macro into a central workbook and your macro should be available across workbooks.

Thanks, it works. Only problem is that it now comes up with the security warning regarding macro's each time I press the custom button in a new sheet for the first time. Any way to get around this?

Yes you need to change the macro security settings in Excel. Go to Tools | Macros & go to Security Settings. Set your Security to low and the message should stop appearing.

I solved the security problem by adding a digital signature - Thanks.

How do I share my add-in with others. I tried to copy the .xla file to the addinns folder on their machine. I can then select the ad inn file, but the customize toolbar does not appear?

Yes a digital signature is the other way, if you don't want to lower your security settings.

Have you checked to see if the toolbar is just not visible by right clicking on the toolbar section and seeing if it is unticked ?

I have checked it and it is not available on the customize toolbars list.

I manually created the toolbar and button using customize toolbar - add new function. It is not automatically created through code. Is this not the problem?

If I should add the custom toolbar and buttons through code, under which section should I enter the code : objects (ThisWorkbook) or Modules (Macro1)? My Macro that performs the custom function lies under modules.

This could be because you Toolbar doesn't actually live in the Add-In !!!

Go back to your Add-In on your PC, Open it and then in Excel go to Tools | Customize, select you toolbar from the list (highlight in blue) and click on the button that says - Attach...

If your toolbar only appears on the left hand listbox, then you need to select your toolbar from the list, click Copy & Ok.

This should then add the toolbar itself to you Add-In workbook, Then re-save your Add-In.

Let me know if it works :0)

Thanks, it works brilliant. So simple to do, yet it had me troubleshooting for hours.

No Problem,

Glad I was able to help !!