-
Jan 5th, 2009, 05:10 AM
#1
Thread Starter
Member
[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
-
Jan 5th, 2009, 06:05 AM
#2
Re: [Excel] Custom Toolbar for Every New Worksheet
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.
Please Mark your Thread "Resolved", if the query is solved & Rate those who have helped you
-
Jan 5th, 2009, 06:39 AM
#3
Thread Starter
Member
Re: [Excel] Custom Toolbar for Every New Worksheet
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?
-
Jan 5th, 2009, 07:03 AM
#4
Re: [Excel] Custom Toolbar for Every New Worksheet
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.
Please Mark your Thread "Resolved", if the query is solved & Rate those who have helped you
-
Jan 5th, 2009, 07:21 AM
#5
Thread Starter
Member
Re: [Excel] Custom Toolbar for Every New Worksheet
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?
-
Jan 5th, 2009, 09:35 AM
#6
Re: [Excel] Custom Toolbar for Every New Worksheet
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 ?
Please Mark your Thread "Resolved", if the query is solved & Rate those who have helped you
-
Jan 5th, 2009, 09:43 AM
#7
Thread Starter
Member
Re: [Excel] Custom Toolbar for Every New Worksheet
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.
-
Jan 5th, 2009, 11:54 AM
#8
Re: [Excel] Custom Toolbar for Every New Worksheet
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)
Please Mark your Thread "Resolved", if the query is solved & Rate those who have helped you
-
Jan 6th, 2009, 12:43 AM
#9
Thread Starter
Member
Re: [Excel] Custom Toolbar for Every New Worksheet
Thanks, it works brilliant. So simple to do, yet it had me troubleshooting for hours.
-
Jan 6th, 2009, 04:25 AM
#10
Re: [RESOLVED] [Excel] Custom Toolbar for Every New Worksheet
No Problem,
Glad I was able to help !!
Please Mark your Thread "Resolved", if the query is solved & Rate those who have helped you
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|