Load Macros from xla on open

[Bam]

Hi All,

Apologies, i'm sure this question has an answer already posted, but i've
searched for hours, probably with the wrong criteria.

I have created an xla which i'm hoping to use for all our Customer service
staff.
It will contain macros that create orders in our system, and perhaps more??

I'm hoping to create a "one-stop-shop" that will be "loaded" whenever they
open an excel file.

I'd like to have it sit in the background silently, or perhaps load a
toolbar with all the Utilities i would like to have.

I've tried the xlstart but this opens the xls file visibly. (Using 2003)

I have installed an xla which is visible in the Add-In's but I can't run the
macro's from it when i open another excel file.

Can someone please let me know how to do this so that i can allow a "number"
of macro's to be run by the user?

Also, would it need to be "loaded" on each machine, or could i park it on a
network share for all to access?

Thanks in anticipation.

Bam.

 

[Patrick Molloy]

certainly an XLA not an XLS. I'd suggest that on opening the XLA's open
event creates a new menubar with the macros triggered by the menu items
Excel 2003 menus are quite easy to create.

here's a simple code example :
you'd call AddMenuItems from the workbook's open event and you'd call
RemoveMenuItems from the close event
put the code below in a standard module

Option Explicit
Sub AddMenuItems()
Dim cbMain As CommandBar
Dim ctrl As CommandBarPopup
RemoveMenuItems
Set cbMain = CommandBars("Worksheet Menu Bar")
With cbMain.Controls.Add(msoControlPopup, Before:=cbMain.Controls.Count,
Temporary:=True)
.Caption = "Tes&t"
With .Controls.Add(msoControlButton)
.OnAction = "ABC"
.Caption = "ABC"
End With
With .Controls.Add(msoControlButton)
.OnAction = "DEF"
.Caption = "DEF"
End With
End With
End Sub
Sub RemoveMenuItems()
Dim mn As CommandBarControl
On Error GoTo quit
Set mn = CommandBars("Worksheet Menu Bar").Controls("tes&t")
Do While Not mn Is Nothing
mn.Delete
Set mn = CommandBars("Worksheet Menu Bar").Controls("tes&t")
Loop
quit:
On Error GoTo 0
End Sub
Sub ABC()
MsgBox "ABC running"
End Sub
Sub DEF()
MsgBox "DEF running"
End Sub

there are numerous articles on the web, here's a recommendation for further
reading
http://www.cpearson.com/Excel/menus.htm#vba

 

[Bam]

Thankyou Patrick.

I've been testing & trying since you posted & finally.... just got it to work!

Many thanks. Your example made more sense for me, personally, than others.

I'm learning... Cheers. Bam.

 

[Patrick Molloy]

excellent!

Thankyou Patrick.

I've been testing & trying since you posted & finally.... just got it to
work!

Many thanks. Your example made more sense for me, personally, than others.

I'm learning... Cheers. Bam.