Results 1 to 7 of 7
  1. #1
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Cells context menu (VBA) (2000 sp-3/ 98SE)

    I've been wanting to pass a parameter to a sub in the .OnAction property of a msoControlButton, part of a popup context menu. I find this isn't possible directly i.e. .OnAction = "MySub(arg)". However, I've been able to do it from a separate handler module, as described in <post#=237159>post 237159</post#>. Trouble is that I lose the local variable I'm trying to pass. Other than declaring a public variable with module scope, is there any other way of doing this?

    And on this topic, what is the significance in the post above of the "caller(1)" in
    <code>Select Case application.caller(1)</code> ?

    Alan

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cells context menu (VBA) (2000 sp-3/ 98SE)

    Could you show us the code?

    Application.Caller(1) gives the position of the menu item chosen.
    There seem to be 4 possible indices, couldn't find anything about them in Help.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cells context menu (VBA) (2000 sp-3/ 98SE)

    Hi Jan

    The general structure of things is similar to the example you gave in the post I cited. But in particular, my context menu will only show when a user clicks within one of several named ranges. The menu has options to delete the selected "record" or to add a new blank "record" to the end of the selected range. What I need to pass is the name of the clicked range - rngnameSelected or rngname
    <code>
    Sub AddNameCommandbar(rngname As String)
    rngnameSelected = rngname ' <font color=red>This is the global variable I'm using at module level</font color=red>

    Dim cCommandbar As CommandBar
    On Error Resume Next
    RemoveCommandbars
    Set cCommandbar = CommandBars("NamesMenu Popup")
    If cCommandbar Is Nothing Then
    With CommandBars.Add(Name:="NamesMenu Popup", Position:=msoBarPopup, temporary:=True)

    With .Controls.Add(Type:=msoControlButton)
    .Caption = "Add New " & rngname & " Record"
    .OnAction = "HandleNamesMenuClick" ' <font color=red>Here I'd prefer to use addNew(rngname)</font color=red>
    __________________________________
    Sub HandleNamesMenuClick()
    Select Case Application.Caller(1)

    Case 1
    addNew
    __________________________________
    Function addNew() As String
    Dim rngname As String, strNameRef As String, strSheet As String, i As Integer

    rngname = rngnameSelected ' <font color=red>Here I use the global rngnameSelected to obtain the required name.</font color=red>
    </code>

    It appears that the .OnAction won't allow me to take the "direct route" of using addNew(rngname), which is what I'd prefer to do. I'm wondering if there's a workaround other than using the global variable "rngnameSelected"?

    Alan

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

    Re: Cells context menu (VBA) (2000 sp-3/ 98SE)

    According to the online help, the OnAction properry of a command bar control is a string that contains the name of the macro executed by the command bar control. A macro is by definition a procedure (sub) without arguments. So the definition excludes functions, as well as procedures that take one or more arguments.

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

    Re: Cells context menu (VBA) (2000 sp-3/ 98SE)

    You can also use the ActionControl property of the CommandBars object. Whenever the user clicks a toolbar button, menu item or context menu item, CommandBars.ActionControl refers to the control that is running its OnAction macro.
    You can set the Tag property of command bar controls, and inspect the Tag property of the ActionControl. This is a way of passing information to the OnAction macro. The Parameter property is similar.
    Look up ActionControl, Tag and Parameter in the online help for more info.

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cells context menu (VBA) (2000 sp-3/ 98SE)

    <hr>ActionControl<hr>
    Of course!
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cells context menu (VBA) (2000 sp-3/ 98SE)

    <P ID="edit" class=small>(Edited by AlanMiller on 12-May-05 22:14. Works beautifully... was there ever a doubt?[grin])</P>Sounds like a viable solution Hans. I'll give it a whirl, but from your description, I'm sure it will be a good vector for parameter passing. Good thinking!

    cheers
    Alan

Posting Permissions

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