Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Lewes, East Sussex, Sussex, United Kingdom
    Posts
    232
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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
    Attached Files Attached Files

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

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

    Change the .OnAction statements to:
    <code>.OnAction = "NewMovement"</code>
    rather than:
    <code> .OnAction = "=NewMovement()"</code>
    and similarly for EditMovement.
    Why are you using functions rather than subroutines?
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 482 Times in 459 Posts

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

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

    zeddy

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Lewes, East Sussex, Sussex, United Kingdom
    Posts
    232
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Lewes, East Sussex, Sussex, United Kingdom
    Posts
    232
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

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

    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. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

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

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

    Microsoft MVP - Excel

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •