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.

---
Regards,
Norman



"cuongvt" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>