Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
totally newbie to VBA need help
Now I'm reading "Excel VBA Macro Programming". It say that: Here is a simple example that adds a new menu item under Tools and attaches some code to it. Insert a module and then add the following subroutine: Sub Test_Menu() MsgBox "You pressed my menu item" End Sub When this is run, it will display a message box with the message “You pressed my menu item.” Now add the following code. Note that there is a continuation character (underscore) shown in two of the lines. This allows long lines of code to wrap around onto the next line but still execute: Sub MenuCommand() CommandBars("Worksheet Menu Bar").Controls _ ("Tools").Controls.Add _ (Type:=msoControlButton).Caption = "MyMenu" CommandBars("Worksheet Menu Bar").Controls _ ("Tools").Controls("MyMenu").OnAction = "Test_Menu" End Sub Run the code only once and then go to the spreadsheet. Choose Tools from the menu, and you will see an option at the bottom called MyMenu. Select it, and you will get your message box. (If you run this code again, a second menu item called MyMenu will appear, which could be confusing.) The first line of the code adds the menu bar MyMenu to the Tools menu. The second line of code describes what action to take when the user does this. The OnAction property is set to point to the subroutine Test_Menu, which you just created. Afer typing this code into module and pressing F5, a error message displays: "error 5. Invalid procedure call or parameter" Then the follwing code is pointed and become yellow color: CommandBars("Worksheet Menu Bar").Controls _ ("Tools").Controls.Add _ (Type:=msoControlButton).Caption = "MyMenu" Could anyone tell me where thing goes wrong and how to fix it? many thanks -- cuongvt ------------------------------------------------------------------------ cuongvt's Profile: http://www.excelforum.com/member.php...o&userid=13439 View this thread: http://www.excelforum.com/showthread...hreadid=471652 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
totally newbie to VBA need help
Hi Cuongvt,
The error you have encountered would occur if the "Worksheet Menu Bar". commandbar or the "Tools" menu item did not exist. If you were using non-English version of Excel the 'Tools' menu might well be otherwise named, for example. --- Regards, Norman "cuongvt" wrote in message ... Now I'm reading "Excel VBA Macro Programming". It say that: Here is a simple example that adds a new menu item under Tools and attaches some code to it. Insert a module and then add the following subroutine: Sub Test_Menu() MsgBox "You pressed my menu item" End Sub When this is run, it will display a message box with the message "You pressed my menu item." Now add the following code. Note that there is a continuation character (underscore) shown in two of the lines. This allows long lines of code to wrap around onto the next line but still execute: Sub MenuCommand() CommandBars("Worksheet Menu Bar").Controls _ ("Tools").Controls.Add _ (Type:=msoControlButton).Caption = "MyMenu" CommandBars("Worksheet Menu Bar").Controls _ ("Tools").Controls("MyMenu").OnAction = "Test_Menu" End Sub Run the code only once and then go to the spreadsheet. Choose Tools from the menu, and you will see an option at the bottom called MyMenu. Select it, and you will get your message box. (If you run this code again, a second menu item called MyMenu will appear, which could be confusing.) The first line of the code adds the menu bar MyMenu to the Tools menu. The second line of code describes what action to take when the user does this. The OnAction property is set to point to the subroutine Test_Menu, which you just created. Afer typing this code into module and pressing F5, a error message displays: "error 5. Invalid procedure call or parameter" Then the follwing code is pointed and become yellow color: CommandBars("Worksheet Menu Bar").Controls _ ("Tools").Controls.Add _ (Type:=msoControlButton).Caption = "MyMenu" Could anyone tell me where thing goes wrong and how to fix it? many thanks -- cuongvt ------------------------------------------------------------------------ cuongvt's Profile: http://www.excelforum.com/member.php...o&userid=13439 View this thread: http://www.excelforum.com/showthread...hreadid=471652 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
What If, Count - totally confused...... | Excel Worksheet Functions | |||
Totally confused - please help! | Excel Worksheet Functions | |||
Help! Totally Grey Sheet | Excel Worksheet Functions | |||
Totally wierd | Excel Worksheet Functions | |||
Totally Stumped | Excel Discussion (Misc queries) |