OnAction for a menu item (2002)

Topic

New Reply

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

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

I tried modifying the OnAction property to pass it an argument:

Reply | Quote

Replies

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

.OnAction = "AlternateGreen(""row"")"

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

That will be great Hans. I’ll do as you suggest. I appreciate your time and help.

Reply | Quote

That will be great Hans. I’ll do as you suggest. I appreciate your time and help.

Reply | Quote

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:

    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

Reply | Quote

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:

    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

Reply | Quote

Hans, thanks for pointing me in the right direction! I hope to lounge with you again.

Reply | Quote

You’re welcome!

Reply | Quote

You’re welcome!

Reply | Quote

Hans, thanks for pointing me in the right direction! I hope to lounge with you again.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

.OnAction = "AlternateGreen(""row"")"

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

Reply | Quote