Right-click menu running code twice (Excel 2003 SP1)

Topic

New Reply

I have a spreadsheet which needs 2 extra options on the “Cell” shortcut menu : “New Movement” and “Edit Movement”. They are visible when the appropriate sheet and workbook is active, and hidden when not.

The problem is that when clicked these options seem to be running the function in the OnAction property twice. I have attached a very cutdown version of the sheet

Reply | Quote

Replies

Change the .OnAction statements to:
.OnAction = "NewMovement"
rather than:
.OnAction = "=NewMovement()"
and similarly for EditMovement.
Why are you using functions rather than subroutines?

Reply | Quote

Rory

Thank you very much for that advice which solves the problem.

I was using functions because I thought you had to use them – at least, I think you have to in Access anyway. I have now changed them to subs.

I’m just puzzled why the ‘=’ and the ‘()’ make the code run twice.

Thanks again

Nick

Reply | Quote

Honestly, I have no idea why. Perhaps it tries to evaluate it as a formula, thereby calling the code, then runs it as an OnAction as normal. I guess MS never expected to encounter an = sign in the OnAction property.

Reply | Quote

I’ve just found out why I should have been using functions – it is because they do not show up when the user clicks Tools/Macros/Macros.. whereas the subs do, and that is not desirable for us.

Reply | Quote

Just declare the subs as Private, and they won’t appear in the macro list.

Reply | Quote

Perhaps it’s because your OnAction for the menu item is a Function rather than a Sub.

zeddy

Reply | Quote