Context Menu via Button...

[GUIDO22]

Hi - I have upgraded my database to the latest flavour of Office 365.... I note CommandBars are no longer inevidence and so have elected to use a Macro in order to create a shortcut menu to mimic the way the display worked before.

However, the form that displays has a command button that has simple click functionality to display a report.
But I want this button to operate differently for a higher level of user access. When a user with higher level presses the Commandbutton i would like a shortcut menu to appear similar to that which displays when you right click a form to display a shortcut menu.

Is this possible and if so.. how can I do it..?

Thanks in an advance for any help you can offer.

 

[theDBguy]

Hi. If you know the name of the shortcut menu, you might be able to use the ShowPopup method.

CommandBar.ShowPopup method (Office) | Microsoft Docs

 

[sonic8]

I note CommandBars are no longer inevidence and so have elected to use a Macro in order to create a shortcut menu to mimic the way the display worked before.

Commandbars as context menus still work exactly the same as they always did.

 

[GUIDO22]

Commandbars as context menus still work exactly the same as they always did.

As objects in code.. (not sure how you mean specifically context menu - do you mean when only activated by right mouse click...?) not so. At least I cant get them to work.... even using MicrosoftOffice 15.0 Lib reference... throws a compiler error... RunTime Error 5 - : Invalid Procedure Call or Argument...

 

[sonic8]

I specifically mentioned context menus because their behaviour did not change at all, while menu bars and tool bars also still work the same in code but were moved to the "Add Ins" tab of the ribbon.

Invalid Procedure Call or Argument...

The most common cause for this error when adding a command bar is that the command with the same name already exists.

 

[GUIDO22]

I specifically mentioned context menus because their behaviour did not change at all, while menu bars and tool bars also still work the same in code but were moved to the "Add Ins" tab of the ribbon.


The most common cause for this error when adding a command bar is that the command with the same name already exists.

Will check out Add-ins...

Same name... no, have checked all code throughout the project. This var name is unique to this subroutine...

 

[GUIDO22]

So given what I have advised , any ideas how I can get this working please.? (The Addins dialog comes up blank... but if that is specific to menu and tool bars thats not what I need anyway ) ...

 

[theDBguy]

So given what I have advised , any ideas how I can get this working please.? (The Addins dialog comes up blank... but if that is specific to menu and tool bars thats not what I need anyway ) ...

Hi. Did you try my suggestion? If you need help with that, you might consider posting a sample copy of your db for testing and assistance.

 

[sonic8]

Same name... no, have checked all code throughout the project. This var name is unique to this subroutine...

The variable name is completely irrelevant. What matters is the name of the command bar.
If your code throws the runtime error, you could run the following sub to check the command bar name.

Public Sub testCommandBar()
    Dim myBar As CommandBar
    On Error Resume Next
    Set myBar = CommandBars("Custom")
    On Error GoTo 0
    If myBar Is Nothing Then
        MsgBox "CommandBar 'Custom' does not exist yet."
    Else
        MsgBox "CommandBar 'Custom' exists already!"
    End If
End Sub

 

[GUIDO22]

OK -thanks.. it was duplicated.. presumably the Temporary attribute was set to FALSE..

 

[GUIDO22]

So I have the context menu displaying correctly and I am using .OnAction to qualify the VB function to run when the selection from the menu is made..., but it is not running the routine in each instance...?

Set newbutton = myBar.Controls.Add(msoControlButton, 1, Me.txtJobNumber)

With newbutton
.BeginGroup = 1
.Caption = "Planning Sheet"
.OnAction = "=PlanningSheet()"
End With

I would have expected this to call the function PlanningSheet() and pass the parameter [txtJobNumber] to it....
Any ideas please...?

 

[MarkK]

The .OnAction parameter does not require "=" prefixed or "()" suffixed, so your code might look like...

With newbutton
    .BeginGroup = 1
    .Caption = "Planning Sheet"
    .OnAction = "mnuPlanningSheet"
End With

...and the subroutine to call--in a standard module--might look like...

Public Sub mnuPlanningSheet()
    MsgBox "You clicked the 'Planning Sheet' popup menu item!"
End Sub

hth

 

[GUIDO22]

The .OnAction parameter does not require "=" prefixed or "()" suffixed, so your code might look like...

With newbutton
    .BeginGroup = 1
    .Caption = "Planning Sheet"
    .OnAction = "mnuPlanningSheet"
End With

...and the subroutine to call--in a standard module--might look like...

Public Sub mnuPlanningSheet()
    MsgBox "You clicked the 'Planning Sheet' popup menu item!"
End Sub

hth

Regretably, this doesnt work. With a breakpoint on the mnuPlanningSheet function - no interrupt / ie. it doesnt call it...
Tried declaring a function local to the form that is called from this menu option - no parameters, simply to display a msgbox .. same result, function is not called...

 

[GUIDO22]

So Im not making any progress on this at all..
I can create the commandbar and a button and this initially displays as a shortcut menu.
When I attempt to do it again in the same session.. when it checks that the commandbar doesnt exist.. if it exists, it attempts to delete it...
I get the following error..

I still cant get the menu selection to execute any call to OnAction correctly either.. all told I'm getting rather cheesed off given the time this has taken me... :-(
Reaching out to all your experts out there...any help would be greatly appreciated please...

 

[sonic8]

When I attempt to do it again in the same session.. when it checks that the commandbar doesnt exist.. if it exists, it attempts to delete it...
I get the following error..

When exactly do you get the error?
Why do you delete and rebuilt the commandbar in the first place? If it exists, just leave as it is. If required, you can still change the Tag or Parameter properties to other values.

I still cant get the menu selection to execute any call to OnAction correctly either..

What seems to be the problem?
Your original approach (.OnAction = "=PlanningSheet()") should work for functions, while @MarkK 's approach should work as well, but for sub procedures.

I have no clue though, how the Parameter property of a command bar control is supposed to work to pass any value to the called VBA code, despite the documentation insinuates it should be possible.

What does work however, is using Commandbars.ActionControl to get a reference to the control triggering the code.

 

[GUIDO22]

When exactly do you get the error?
Why do you delete and rebuilt the commandbar in the first place? If it exists, just leave as it is. If required, you can still change the Tag or Parameter properties to other values.


What seems to be the problem?
Your original approach (.OnAction = "=PlanningSheet()") should work for functions, while @MarkK 's approach should work as well, but for sub procedures.

I have no clue though, how the Parameter property of a command bar control is supposed to work to pass any value to the called VBA code, despite the documentation insinuates it should be possible.

What does work however, is using Commandbars.ActionControl to get a reference to the control triggering the code.

>>Your original approach (.OnAction = "=PlanningSheet()") should work for functions, while @MarkK 's >>approach should work as well, but for sub procedures.

No - it doesnt... try a simple commandbar demo using a call to MsgBox against the .OnAction attribute... it does nothing...

 

[sonic8]

try a simple commandbar demo using a call to MsgBox against the .OnAction attribute... it does nothing...

It works as expected.
If you pay attention to detail, you might notice that the sample code in your screenshot is different from what you originally posted and what I quoted. - However, this difference should not be causing it "doing nothing" but should trigger an error "...cannot run the macro or callback function...".

 

[GUIDO22]

OK - so I foind on StackOverflow some helpful tidbits... and in a nutshell...

1. You need to declare the subroutine as Public AND in a Public Module (it cant be declared local to the form you are working in ...)
2. In order to use a parameter, use the .Parameter attribute of the command button and in the subroutine you are calling .. you a 'callback' reference ie. CommandBars.ActionControl.Parameter (where ActionControl references the last active control prior to the subroutine being called...)

That was the only thing that worked for me.

 

[GUIDO22]

It works as expected.
If you pay attention to detail, you might notice that the sample code in your screenshot is different from what you originally posted and what I quoted. - However, this difference should not be causing it "doing nothing" but should trigger an error "...cannot run the macro or callback function...".

Respectfully, I have paid attention to detail but perhaps have been blinded by staring at the code for so long and running various different scenarios that were complicated by shortcut menus created by Macros that were also assigned to the form ... (long story)

However, with all the errors I did get... I never got a 'cannot run the macro macro...' error. you mention...

 

[MarkK]

Here's a very rough popup menu sample. It makes a different menu depending on the state of a couple of radio buttons. It adds controls to the menu using an array of strings.