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 ...
22nd November 2010, 11:33 AM #1
- Rep Power
Assigning a excel Macro to work in all workbooks
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!
22nd November 2010, 11:41 AM #2
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):
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
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)
.Caption = "Outlook Send All"
.OnAction = "'" & ThisWorkbook.Name & "'!outlook_send_all" 'Replace outlook_send_all with the name of your sub routine
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
Last edited by LosOjos; 22nd November 2010 at 11:54 AM.
Reason: Spelling & Grammar
22nd November 2010, 11:55 AM #3
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
22nd November 2010, 11:57 AM #4
- Rep Power
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!
22nd November 2010, 12:00 PM #5
That's because I've sent you on a wild goose chase (I should never work from memory, I forget too much!)
Originally Posted by FatBoy
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
22nd November 2010, 12:16 PM #6
- Rep Power
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?
22nd November 2010, 12:25 PM #7
There will be a new ribbon called "Add-Ins" and any buttons you add via code will be in there
Thanks to LosOjos from:
FatBoy (22nd November 2010)
24th November 2010, 01:45 PM #8
- Rep Power
All working well thanks via the button it creates as well. Spot on, really appreciate your help, a thousand thanks
24th November 2010, 02:23 PM #9
By Duall in forum Office Software
Last Post: 20th August 2010, 04:51 PM
By SteveB in forum MIS Systems
Last Post: 3rd March 2010, 09:23 AM
By ahuxham in forum Office Software
Last Post: 18th December 2009, 10:27 AM
By oyoung in forum Windows
Last Post: 30th March 2007, 10:27 AM
By steve in forum Educational Software
Last Post: 16th May 2006, 02:49 PM
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)