Results 1 to 12 of 12
  1. #1
    3 Star Lounger Omega3's Avatar
    Join Date
    Jan 2004
    Location
    Los Angeles, California, USA
    Posts
    343
    Thanks
    2
    Thanked 1 Time in 1 Post

    OnAction for a menu item (2002)

    I have the following partial code in a module that is creating a menu item:

    <pre>Set cbmCommandBarMenuCascade = .Controls.Add(msoControlPopup)
    With cbmCommandBarMenuCascade
    .Caption = "Format Conditions"
    With .Controls.Add(msoControlButton)
    .Caption = "Alternate Green"
    .Tag = .Caption
    .OnAction = "AlternateGreen"
    End With
    End With
    </pre>



    I tried modifying the OnAction property to pass it an argument:
    You know it's time to diet when you push away from the table and the table moves.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: OnAction for a menu item (2002)

    Do you want to pass the string "row" as argument to AlternateGreen? If so, you should use

    <code>.OnAction = "AlternateGreen(""row"")"</code>

    Note that I used fewer double quotes. If you want something else, please explain.

  3. #3
    3 Star Lounger Omega3's Avatar
    Join Date
    Jan 2004
    Location
    Los Angeles, California, USA
    Posts
    343
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: OnAction for a menu item (2002)

    Hello Hans,

    Thank you for your reply. Yes that's exactly what I am trying to do, pass the string row to the procedure AlternateGreen. I tried using two double quotes before and after row as you suggested and I still get the macro not found error. I've double checked the spelling of the macro and it's correct. I've tried using three double quotes before and after with the same result. It is used in my Personal.xls to build a menu item. It worked well without the argument but when I tried adding an argument then this problem occurred.

    It "appears" as though you can't pass arguments to the procedure from the OnAction property. Maybe it has something to do with it being used in a menu item. I don't know if that is true or not. I was hoping maybe there was a different property that I may need to use to pass arguments. Any help, suggestion would be appreciated.
    You know it's time to diet when you push away from the table and the table moves.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: OnAction for a menu item (2002)

    The attached example is not very useful, but is shows that you can pass an argument.

  5. #5
    3 Star Lounger Omega3's Avatar
    Join Date
    Jan 2004
    Location
    Los Angeles, California, USA
    Posts
    343
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: OnAction for a menu item (2002)

    Thank you for taking your valuable time in writing that code. I opened your file and ran it and it ran correctly but I notice something else funny. Your message box pop up twice in a row. As soon as I clicked okay the first time is showed up again.

    I looked at the code and I could not find a reason why. I rebooted and tried again with the same problem. I removed my Personal.xls from Xlstart to elimate any problem with that and it still appeared twice. Then I commented the message box out and added this code: range("b3").select and b3 did not get selected. Nothing seemed to happen on the screen. No error message. Then I tried it without the quotes around b3. This will cause a compile error but I ran it anyway as a test and this caused the error message "macro AlternateGreen not found". Since it couldn't compile it it won't find it. That makes sense. When I manually compile it I get the message b3 is not defined. When I run a compile on my Personal.xls I get no compile error that would cause AlternateGreen to not be compiled. Sigh.

    I'm sure if you added the range statement without the quotes that you would probably get the same not found error message. It may be a clue to something.
    You know it's time to diet when you push away from the table and the table moves.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: OnAction for a menu item (2002)

    It seems that the OnAction is run twice if you specify an argument. This problem is reported repeatedly in the newsgroups, but without a solution, and I cannot find a mention of it in the Knowledge Base. I fear that you will have to find another way to handle this, for example by removing the argument from AlternateGreen and looking at the CommandBars.ActionControl object in this procedure.

  7. #7
    3 Star Lounger Omega3's Avatar
    Join Date
    Jan 2004
    Location
    Los Angeles, California, USA
    Posts
    343
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: OnAction for a menu item (2002)

    Thank you for verifying that. I thought I was losing it. I haven't used the ActionControl object before so I'll play with it and see what work around I can use. To bad OnAction didn't work. It would come in very handy when passing arguments. Thanks for your time.
    You know it's time to diet when you push away from the table and the table moves.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: OnAction for a menu item (2002)

    You would still use OnAction - set it to the name of a procedure, but without argument. Inspect ActionControl in this procedure to know who is calling it.

  9. #9
    3 Star Lounger Omega3's Avatar
    Join Date
    Jan 2004
    Location
    Los Angeles, California, USA
    Posts
    343
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: OnAction for a menu item (2002)

    That will be great Hans. I'll do as you suggest. I appreciate your time and help.
    You know it's time to diet when you push away from the table and the table moves.

  10. #10
    3 Star Lounger Omega3's Avatar
    Join Date
    Jan 2004
    Location
    Los Angeles, California, USA
    Posts
    343
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: OnAction for a menu item (2002)

    Hello Hans,

    Did as you suggested and it worked. I also discovered another property that anyone can use. It is the Tag property. Turns out I can use either one.

    For any loungers interested, here's my test code but first set the tag and/or parameter properties of the menu item to the desired value and run this code that the OnAction property points to:

    <pre> Dim cmdBar As CommandBarControl

    Set cmdBar = Application.CommandBars.ActionControl
    If cmdBar Is Nothing Then
    Exit Sub
    End If

    MsgBox "Tag is " & cmdBar.Tag & " Parameter is " & cmdBar.Parameter
    </pre>

    You know it's time to diet when you push away from the table and the table moves.

  11. #11
    3 Star Lounger Omega3's Avatar
    Join Date
    Jan 2004
    Location
    Los Angeles, California, USA
    Posts
    343
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: OnAction for a menu item (2002)

    Hans, thanks for pointing me in the right direction! I hope to lounge with you again. <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    You know it's time to diet when you push away from the table and the table moves.

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: OnAction for a menu item (2002)

    You're welcome!

Posting Permissions

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