totally newbie to VBA need help

[cuongvt]

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

 

[Norman Jones]

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.