Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Load Macros from xla on open
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Load Macros from xla on open
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, Befo=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" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Load Macros from xla on open
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" wrote: 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, Befo=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" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Load Macros from xla on open
excellent! "Bam" wrote in message ... 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" wrote: 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, Befo=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" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to easily (!) load Macros from external files on demand? | Excel Discussion (Misc queries) | |||
Do not load if you dont enable macros | Excel Worksheet Functions | |||
how do i make my macros load on all machines on my network | Excel Programming | |||
Excel don't load macros from add-in | Excel Programming | |||
Auto load macros | Excel Programming |