Rightclick Menu in VBA Userform

[hicksi]

I have a CommandButton defined that creates a report.
I want to do two different (related) things:
1: Define shift-keys so:

• If no shift-key, the report is shown in preview
• If the user Shift-clicks. the report is sent directly to the printer
• If the user Ctrl-Clicks, the report is automatically saved to disk
• Double-click, a new form is displayed that allows them to filter the data being reported

2: Provide a right-click that displays a popup that gives menu-access to those (and displays the shortcut keysets)

Some of it I have working (key-combinations), and I have some templates done to get the popup.

BUT...
I have seen some posts about using the CommandBars object which seems to be cleverer and doesn't need to access and intimately control low-level functions.
Anybody know of how to make all this work, and whether it is possible/probable to do this?

 

[hicksi]

The shift-key template is in

Easy Way to Check for 'Shift/Alt/Ctrl' keys

I needed to provide multiple uses for Command-Buttons in an Excel UserForm. Ie, to Preview a report (click); to Print the report without preview (Alt-Click); to Save the report to file (Control-click) The following code was developed to illustrate the options, so I post it here. Use it as you...

www.mrexcel.com

 

[Jaafar Tribak]

Take a look here:

Home | Ron de Bruin Excel Automation

Welcome to https://www.rondebruin.nl

www.rondebruin.nl

 

[hicksi]

Take a look here:

Home | Ron de Bruin Excel Automation

Welcome to https://www.rondebruin.nl

www.rondebruin.nl

That's great. Now, one more...How do I get the Alt-key to be written to the menu? A bit of a juggle and FaceId 5, 4, 3, 191 will work for me. But indicating the shortcut key? Haven't figured that yet

 

[Jaafar Tribak]

Assign an accelerator key to the menu item by using use an ampersand (&) in the menu caption before the accelerator key.
For example:

With .Controls.Add(Type:=msoControlButton)
  .Caption = "&Button 1"

Also, have a look at the msoControlButton ShortcutText Property :
For example:

With .Controls.Add(Type:=msoControlButton)
  .Caption = "Button 1"
  .ShortcutText = "Alt+B"

 

[hicksi]

{{{hugs}}} That is all so simple when you know how.
Thank You

 

[Jaafar Tribak]

{{{hugs}}} That is all so simple when you know how.
Thank You

I should have mentioned that the ampersand (&) accelerator won't work with the Alt or Ctrl keys .. It only works with Shift key .

I guess you wil need more vba coding if you want to evoke the menu onaction macro via the Alt + Key combination.

 

[hicksi]

I should remove half-a-hug. Your

.ShortcutText = "Alt+B"

doesn't work!!!

 

[hicksi]

But I'll reinstate it if you can also tell me how to put a line to separate segments of the popup
Like

 

[Jaafar Tribak]

But I'll reinstate it if you can also tell me how to put a line to separate segments of the popup
Like
View attachment 23179

Use the BeginGroup Property:

With .Controls.Add(Type:=msoControlButton)
    .BeginGroup = True
    .Caption = "Button 2"

I have experimented a bit with this and have found that pressing the ALT key while a popup menu is on display unloads (closes) the entire popup menu... Try that and see. (In fact, the Alt key closes all drop menus- not just context menus.)

I think, the only workaround that may work for setting the shortcut keys combo (Alt + Key ) is via the RegisterHotKey API or a by setting a keyboard hook.

I'll further experiment with this and will post if anything comes up.