+ Post New Thread
Results 1 to 9 of 9
Office Software Thread, Assigning a excel Macro to work in all workbooks in Technical; Hi all, I have found a cool little macro to e-mail individual worksheets inside a workbook, here: Sending Just a ...
  1. #1
    FatBoy's Avatar
    Join Date
    Oct 2007
    Location
    Kent, UK
    Posts
    249
    Thank Post
    55
    Thanked 20 Times in 16 Posts
    Rep Power
    17

    Assigning a excel Macro to work in all workbooks

    Hi all,

    I have found a cool little macro to e-mail individual worksheets inside a workbook, here: Sending Just a Worksheet via E-mail. I have managed to save the vb macro to a workbook but what I really want to do for a staff member is save the macro in excel not the workbook so its available in any workbook she opens! Is this possible? She tells me this option used to be available to her but the staff member that did this has long since left and I can't see how you would do this!

    Thanks
    FB

  2. #2

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,447
    Thank Post
    1,438
    Thanked 1,168 Times in 797 Posts
    Rep Power
    707
    What you need is an Excel Add-In: create a new workbook, add in the code you want to a module within it, then save as an Excel Add-In (*.xla). You can then add this to a users Excel via the trust center on 2007+ or via the Tools > Add-In menu on older versions of Excel.

    I'd also recommend adding code to add a menu item on load and also to remove it should the Add-In be removed, something like this (on the 'ThisWorkbook' module of your add-in's VBA):

    Code:
    Private Sub Workbook_AddinInstall()
    On Error Resume Next
    Dim CmdBar As CommandBar
    Dim CmdBarMenu As CommandBarControl
    Dim CmdBarMenuItem As CommandBarControl
    '
    ' Point to the Worksheet Menu Bar
    '
    Set CmdBar = Application.CommandBars("Worksheet Menu Bar")
    '
    ' Point to the Tools menu on the menu bar
    '
    CmdBar.Controls("My Add-Ins").Delete                             
    Set CmdBarMenu = CmdBar.Controls.Add(Type:=msoControlPopup)
    CmdBarMenu.Caption = "My Add-Ins"
    '
    ' Add a new menu item to the Tools menu
    '
    
    Set CmdBarMenuItem = CmdBarMenu.Controls.Add(Type:=msoControlButton)
    With CmdBarMenuItem
        .Caption = "Outlook Send All"
        .OnAction = "'" & ThisWorkbook.Name & "'!outlook_send_all"      'Replace outlook_send_all with the name of your sub routine
    End With
    End Sub
    
    Private Sub Workbook_AddinUninstall()
        On Error Resume Next 'In case it has already gone.
        Application.CommandBars("Worksheet Menu Bar").Controls("My Add-Ins").Delete
        On Error GoTo 0
    End Sub
    Last edited by LosOjos; 22nd November 2010 at 11:54 AM. Reason: Spelling & Grammar

  3. #3

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,447
    Thank Post
    1,438
    Thanked 1,168 Times in 797 Posts
    Rep Power
    707
    If you're unsure how to go about it, post/PM the code you're using and I'll turn it in to an add-in for you if you want

  4. #4
    FatBoy's Avatar
    Join Date
    Oct 2007
    Location
    Kent, UK
    Posts
    249
    Thank Post
    55
    Thanked 20 Times in 16 Posts
    Rep Power
    17
    I think I have created the addin ok...(Not tested it yet!) but can't work out how to add it (DOH) gone to excel options > Trust center settings... > but than can't find a place to add it. I can see a place to add the trust location but not add the actual add-in!

  5. #5

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,447
    Thank Post
    1,438
    Thanked 1,168 Times in 797 Posts
    Rep Power
    707
    Quote Originally Posted by FatBoy View Post
    I think I have created the addin ok...(Not tested it yet!) but can't work out how to add it (DOH) gone to excel options > Trust center settings... > but than can't find a place to add it. I can see a place to add the trust location but not add the actual add-in!
    That's because I've sent you on a wild goose chase (I should never work from memory, I forget too much!)

    There is a page in Excel Options called Add-Ins, at the bottom of that page is a label with a drop down box (Manage), make sure it is set to "Excel Add-ins", then press "Go..." In the window that pops-up, click browse and find your add-in file; it'll ask if you want to move it to the user area, say yes otherwise Excel sometimes struggle to find it again; then make sure your add-in is ticked and it should now be loaded whenever that user loads Excel

  6. #6
    FatBoy's Avatar
    Join Date
    Oct 2007
    Location
    Kent, UK
    Posts
    249
    Thank Post
    55
    Thanked 20 Times in 16 Posts
    Rep Power
    17
    No worries...

    I have done it and it worked, the only thing that didn't was the adding a button script thing you put in the code above (Unless I can't see the button, where should it be?) I change the "outlook_send_all" to the name of the subroutine "EmailWithOutlook" but no luck with that part. Not the end of the world I added a quick access button to the macro and that works fine it seems. Where should the button be added out of interest though?

    Thanks

  7. #7

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,447
    Thank Post
    1,438
    Thanked 1,168 Times in 797 Posts
    Rep Power
    707
    There will be a new ribbon called "Add-Ins" and any buttons you add via code will be in there

  8. Thanks to LosOjos from:

    FatBoy (22nd November 2010)

  9. #8
    FatBoy's Avatar
    Join Date
    Oct 2007
    Location
    Kent, UK
    Posts
    249
    Thank Post
    55
    Thanked 20 Times in 16 Posts
    Rep Power
    17
    All working well thanks via the button it creates as well. Spot on, really appreciate your help, a thousand thanks

  10. #9

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,447
    Thank Post
    1,438
    Thanked 1,168 Times in 797 Posts
    Rep Power
    707
    Any time!

SHARE:
+ Post New Thread

Similar Threads

  1. [MS Office - 2007] Excel Macro - Save values individually
    By Duall in forum Office Software
    Replies: 1
    Last Post: 20th August 2010, 04:51 PM
  2. Macro + Excel 2007 + SIMS
    By SteveB in forum MIS Systems
    Replies: 20
    Last Post: 3rd March 2010, 09:23 AM
  3. [MS Office - XP] Excel Macro - If Cell Value
    By ahuxham in forum Office Software
    Replies: 0
    Last Post: 18th December 2009, 10:27 AM
  4. help with excel macro needed
    By oyoung in forum Windows
    Replies: 0
    Last Post: 30th March 2007, 10:27 AM
  5. Excel Macro - Edexcel GCE Exams
    By steve in forum Educational Software
    Replies: 0
    Last Post: 16th May 2006, 02:49 PM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •