Results 1 to 10 of 10

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

  1. #1

    Thread Starter
    Member
    Join Date
    Oct 2008
    Location
    South Africa
    Posts
    32

    Resolved [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

  2. #2
    Superbly Moderated NeedSomeAnswers's Avatar
    Join Date
    Jun 2002
    Location
    Manchester uk
    Posts
    2,660

    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



  3. #3

    Thread Starter
    Member
    Join Date
    Oct 2008
    Location
    South Africa
    Posts
    32

    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?

  4. #4
    Superbly Moderated NeedSomeAnswers's Avatar
    Join Date
    Jun 2002
    Location
    Manchester uk
    Posts
    2,660

    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



  5. #5

    Thread Starter
    Member
    Join Date
    Oct 2008
    Location
    South Africa
    Posts
    32

    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?

  6. #6
    Superbly Moderated NeedSomeAnswers's Avatar
    Join Date
    Jun 2002
    Location
    Manchester uk
    Posts
    2,660

    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



  7. #7

    Thread Starter
    Member
    Join Date
    Oct 2008
    Location
    South Africa
    Posts
    32

    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.

  8. #8
    Superbly Moderated NeedSomeAnswers's Avatar
    Join Date
    Jun 2002
    Location
    Manchester uk
    Posts
    2,660

    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



  9. #9

    Thread Starter
    Member
    Join Date
    Oct 2008
    Location
    South Africa
    Posts
    32

    Re: [Excel] Custom Toolbar for Every New Worksheet

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

  10. #10
    Superbly Moderated NeedSomeAnswers's Avatar
    Join Date
    Jun 2002
    Location
    Manchester uk
    Posts
    2,660

    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
  •  



Click Here to Expand Forum to Full Width