Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to assign macro to menu button?
I have this code in the "ThisWorkbook" Module:
Private Sub Workbook_Open() Call Module1.MenuBar End Sub And this code in Module1: Private Sub MenuBar() Dim ctl as CommandBarButton Application.CommandBars("Worksheet Menu Bar").Controls("MyOptions").Delete Set ctl = Application.CommandBars("Worksheet Menu Bar").Controls.Add ctl.Caption = "&MyOptions" ctl.Style = msoButtonCaption 'ctl.OnAction = MyFunction End Sub This code adds a "MyOptions" button to the menu bar when the workbook is opened. The reason I want the button located on the menu bar is because it's always visible regardless of what tool bars are displayed, and regardless of what worksheet is active. I want the button to open a user form ("frmOptions") when the "MyOptions" button is clicked. All the code behind frmOptions will be in Module1 and will enable the user to perform various filtering, formatting and what-if scenarios. The OnAction event (commented out above) runs when the workbook is opened (which I don't want) and will not run on click (which I do want). If I right click on the menu bar, select Customize, then right click on the "MyOptions" button after the Customize window appears, I can assign a macro, and the macro will run on click. But I need to be able to assign the macro programmatically via automation from Access - and I want to assign the on click event of the button to a function in Module1 (rather than a macro). Is this possible?. Any suggestions on how to do this? Thanks in advance. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to assign macro to menu button?
Try using
ctl.OnAction = "MyFunction" -- HTH Bob Phillips "deko" wrote in message ... I have this code in the "ThisWorkbook" Module: Private Sub Workbook_Open() Call Module1.MenuBar End Sub And this code in Module1: Private Sub MenuBar() Dim ctl as CommandBarButton Application.CommandBars("Worksheet Menu Bar").Controls("MyOptions").Delete Set ctl = Application.CommandBars("Worksheet Menu Bar").Controls.Add ctl.Caption = "&MyOptions" ctl.Style = msoButtonCaption 'ctl.OnAction = MyFunction End Sub This code adds a "MyOptions" button to the menu bar when the workbook is opened. The reason I want the button located on the menu bar is because it's always visible regardless of what tool bars are displayed, and regardless of what worksheet is active. I want the button to open a user form ("frmOptions") when the "MyOptions" button is clicked. All the code behind frmOptions will be in Module1 and will enable the user to perform various filtering, formatting and what-if scenarios. The OnAction event (commented out above) runs when the workbook is opened (which I don't want) and will not run on click (which I do want). If I right click on the menu bar, select Customize, then right click on the "MyOptions" button after the Customize window appears, I can assign a macro, and the macro will run on click. But I need to be able to assign the macro programmatically via automation from Access - and I want to assign the on click event of the button to a function in Module1 (rather than a macro). Is this possible?. Any suggestions on how to do this? Thanks in advance. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to assign macro to menu button?
ctl.OnAction = "MyFunction"
Well, that was easy. Thanks. So I've successfully inserted a module and code into Excel from Access, and the menu bar button I've created launches a message box. Now I'm in the home stretch. I need to insert a user form. I'm not sure I see any reason to put all the form's code in the new module I've inserted - that is, it just as well could run from the form's module, could it not? What would be nice is if I could store the form and code together as a unit in an Access table and dump the whole thing in at once. Is this possible? Are there any best practices for inserting code/forms/objects into Excel? I'm wondering if I could store each procedure in a table for ease of management. Other suggestions? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to assign macro to menu button?
http://www.cpearson.com/excel/vbe.htm
-- Regards, Tom Ogilvy "deko" wrote in message om... ctl.OnAction = "MyFunction" Well, that was easy. Thanks. So I've successfully inserted a module and code into Excel from Access, and the menu bar button I've created launches a message box. Now I'm in the home stretch. I need to insert a user form. I'm not sure I see any reason to put all the form's code in the new module I've inserted - that is, it just as well could run from the form's module, could it not? What would be nice is if I could store the form and code together as a unit in an Access table and dump the whole thing in at once. Is this possible? Are there any best practices for inserting code/forms/objects into Excel? I'm wondering if I could store each procedure in a table for ease of management. Other suggestions? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to assign macro to menu button?
http://www.cpearson.com/excel/vbe.htm
That's a good resource. So what I've got so far looks like this: strCode = GetCode("frmOptions") xlapp.VBE.ActiveVBProject.VBComponents.Add _ (vbext_ct_MSForm).Name = "frmOptions" lngLn = xlapp.VBE.ActiveVBProject.VBComponents _ ("frmOptions").CodeModule.CountOfLines + 1 xlapp.VBE.ActiveVBProject.VBComponents _ ("frmOptions").CodeModule.InsertLines lngLn, strCode This adds the form nicely, and I can store code snippets in a table which is helpful, but painting the form with controls could be a challenge. Is there an easy way to do this? Perhaps I could just make the button on the menu bar a drop-down list of different options. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to assign macro to menu button?
I am not sure why you want to build the useform with code. If so, there is
no need to import the form itself. You can add it with code as well. http://j-walk.com/ss/excel/tips/tip76.htm Creating a Userform Programmatically -- Regards, Tom Ogilvy "deko" wrote in message . .. http://www.cpearson.com/excel/vbe.htm That's a good resource. So what I've got so far looks like this: strCode = GetCode("frmOptions") xlapp.VBE.ActiveVBProject.VBComponents.Add _ (vbext_ct_MSForm).Name = "frmOptions" lngLn = xlapp.VBE.ActiveVBProject.VBComponents _ ("frmOptions").CodeModule.CountOfLines + 1 xlapp.VBE.ActiveVBProject.VBComponents _ ("frmOptions").CodeModule.InsertLines lngLn, strCode This adds the form nicely, and I can store code snippets in a table which is helpful, but painting the form with controls could be a challenge. Is there an easy way to do this? Perhaps I could just make the button on the menu bar a drop-down list of different options. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to assign macro to menu button?
I am not sure why you want to build the useform with code. If so, there
is no need to import the form itself. You can add it with code as well. http://j-walk.com/ss/excel/tips/tip76.htm Creating a Userform Programmatically Thanks again for the tip. But I think I can skip the form. What I've got below seems to work. What's nice is I can write and test the code in Excel and then just cut and paste from the IDE into an Access table. So I can catalog all kinds of options. Users select the options they want before they create the workbook, and my code in Access will insert whatever code is necessary to support those selections. Public Sub AddCustomMenu() On Error Resume Next Dim mbMain As CommandBar Dim mbCustom As CommandBarControl Application.CommandBars("Worksheet Menu Bar").Controls("Custom").Delete Set mbMain = Application.CommandBars("Worksheet Menu Bar") Set mbCustom = mbMain.Controls.Add(Type:=msoControlPopup, Temporary:=True) mbCustom.Caption = "Custom" With mbCustom.Controls.Add(Type:=msoControlButton) .Caption = "Option1" .Style = msoControlButton .OnAction = "Option1Code" End With With mbCustom.Controls.Add(Type:=msoControlButton) .Caption = "Option2" .Style = msoControlButton .OnAction = "Option2Code" End With With mbCustom.Controls.Add(Type:=msoControlButton) .Caption = "Option3" .Style = msoControlButton .OnAction = "Option3Code" End With End Sub Public Sub Option1Code() MsgBox "Apply Option 1" End Sub Public Sub Option2Code() MsgBox "Apply Option 2" End Sub Public Sub Option3Code() MsgBox "Apply Option 3" End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
macro does not assign to menu? | Excel Discussion (Misc queries) | |||
Assign a macro to a button | Excel Discussion (Misc queries) | |||
Assign macro name to menu command w/o using workbook name | Excel Discussion (Misc queries) | |||
Assign macro to button | Excel Programming | |||
Assign button to run macro | Excel Programming |