Excel Menu Calls Macro on Focus 1

[john30120]

Using Excel 2003... I have a added a custom submenu "Add New Row" to the "Insert" CommandBar. It calls a VBA subroutine.

The problem is that the subroutine is activated when "Add New Row" gets the focus (or mouseover) and does not wait for a click. As a side note, but maybe part of the problem, the caption "Add New Row" has a right arrow as if there are level 2 submenu items although there are none.

How do I straighten this out??
Thanks

 

[SkipVought]

Hi,

What is the purpose for adding a row? Excel has a limited number of rows, that cannot be changed.

If you are referring to a table, its like asking for something that's already there.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[john30120]

I want to allow adding the row only under certain circumstances and VBA is a nice way to sort this out. Additionally, I am trying to learn the nuances of VBA in Excel and it seemed strange that the menu would call the macro before the caption was clicked.

 

[SkipVought]

What do you mean by adding a row? Any user can select the next available row in a table: NOTHING IS ADDED! Excel is Not MS Access or any other database.

Please post your code.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[john30120]

I have a spreadsheet wherein each row is an entry for a sequential date. It may be necessary to add an entry in the middle of the old data. So add a row in between the 2 rows and process the data and update the spreadsheet.

But that is not my question. Assume I add a custom menu item to the dropdown under the Insert menu. What I want it to do is is call a VBA subroutine when it is clicked. But what is happening is that the subroutine is called when the menu entry is highlighted. None of the other menu items work that way.

 

[SkipVought]

It may be necessary to add an entry in the middle of the old data.

I recommend NOT doing inserts as a matter of practice, as inserting can cause formula reference problems in certain circumstances.

Rather, ALWAYS add your data fter the last row of data and SORT your rows into the necessary sequence.

Where is your code???

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[john30120]

Please let me start over.
There is no code. I have created a custom menu item called "My Menu Item" and it dwells in the Insert dropdown.

I have linked it to a subroutine which does nothing except set j=j. And I have put a breakpoint on that line so I can see when it is called.

I would hope that I could click on "Insert" to drop the menu down, slide the mouse down to the "My Menu Item", and to my surprise and dismay it runs the subroutine immediately.

I expected it to wait until I clicked on the menu item to jump to the subroutine as it does for all other menu operations. Also the custom menu entry has a right arrow as if there were sublevel menu items and I can't remove this.

How do you put in a custom menu item that works like all the other menu items.

 

[SkipVought]

There is no code.

HUH?

...I have linked it to a subroutine ...
...I have put a breakpoint on that line ...
...it runs the subroutine ...
...I clicked on the menu item to jump to the subroutine ...

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[fumei]

1. Yup. Stating "There is no code." is incorrect. Everything you have stated means there IS code.

2. Describe - precisely - what steps you took to get this menu item.

Here is mine to put a "Yadda" menu item on the Insert menu dropdown.

1. Write a procedure. In this case, a procedure named "Yadda"

2. Right click the toolbar and "Customize".

3. Select Macros as the Categories

4. Drop "Custom Menu Item" to the toolbar (rename it if you want)

5. Close dialog

6. Click the menu item, and get the macro selection dialog.

7. Select my "Yadda" procedure.

The menu item is there, pointing to the Yadda procedure. Insert > Yadda requires a click. It does not fire just highlighting it. It does fire if you use a the hot key (if any).

So, what did you do exactly?

BTW: I find this process strange. In Word you can select any macro and simply drag it to a toolbar.


unknown

 

[john30120]

Thanks to Fumei.....

There is an interesting subtlety as to how the new menu item is created.

If you use Tools,Customize,Commands and then under the Categories column select "Macros", then you can rename this menu, drag it to either the toolbar or a submenu. Then right click on that and Assign it to the subroutine, it works perfectly and only when clicked.

But, if instead of selecting "Macros" in the Categories column you select "New Menu Item" (seems logical and this is what I was doing), then follow all the same steps to drag, rename and assign. You get a menu item that looks the same but when located as a submenu item, fires the subroutine when highlighted.

Very odd indeed!!

 

[fumei]

But, if instead of selecting "Macros" in the Categories column you select "New Menu Item" (seems logical and this is what I was doing), then follow all the same steps to drag, rename and assign. You get a menu item that looks the same but when located as a submenu item, fires the subroutine when highlighted."

That is because it IS a menu item.

This is a lot different from a menu item that is not (really) a menu item, but a SUB-menu pointer.

Yadda

is different from

Yadda...

A menu item (inserted as New menu item) is inserted as an OnAction - ummmm - object? thingie? This means as soon as it gets any focus, it fires.


unknown