No 'Click' for CommandBarPopups, so what should I do?

[mrmack]

I would like to have a custom menu (CommandBarPopup) to have it's items
enabled/disabled depending on the current user selection (e.g. chart/cell).

The problem I'm facing now is that there is no Click property attached to
the CommandBarPopup class. I am aware of the OnAction property which can be
used to call VBA code, but what is the best way to approach this?

This is for a COM Add-in, not for a single workbook and all the examples
I've seen of using VBA are bound to a single file.

The rest of my Add-in is written in C#.

I was considering having a placeholding CommandBarButton that, when Clicked,
would execute the required code, before making itself invisible and making
the true CommandBarPopup menu visible.

However, this solution is for blind users, who would be accessing the menu
via keyboard shortcuts and having the contents read out by a screen reader.
If the menu item originally selected is not the CommandBarPopup, I don't know
how this would confuse the screen readers/users mental model of what is going
on.

So, in short, how does one invoke a method upon the clicking of a
CommandBarPopup?

 

[Chip Pearson]

I don't know C#, but the following is what I use in a COM Add-In
in VB6. You should be able to translate to C# fairly easily. In
the Exccel Designer object, declare WithEevent your button
object:

Private WithEvents p_mnuAbout As Office.CommandBarButton

Then write the Click event procedure in the designer:

Private Sub p_mnuAbout_Click(ByVal Ctrl As
Office.CommandBarButton, CancelDefault As Boolean)
MsgBox C_APP_NAME & " Version: " & App.Major & "." & _
App.Minor & vbCrLf & C_COPYRIGHT, vbOKOnly, C_APP_NAME
End Sub

When you create your command button, create a unique tag and set
the OnAction event to the
Const PROG_ID_START As String = "!<"
Const PROG_ID_END As String = ">"

.Tag = CreateTag("MyCustomAboutButton")
.OnAction = PROG_ID_START & AddInInst.ProgId & PROG_ID_END

where AddInInst is the object is the AddInInst is passed in to
the OnConnection event.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[mrmack]

Thanks for your quick reply!

I had no success, as I can't really understand how this would work.

I am creating a CommandBarPopup - a popup menu. In fact, I am creating
several - at least one for the "Worksheet Menu Bar" and one for the "Chart
Menu Bar".

I already have a Click event handler to handle the CommandBarButtons inside
the Popup, which has the signature

private void MenuItem_Click(Office.CommandBarButton Ctrl, ref Boolean
CancelDefault)

So I thought I'd have a go and see if there was some sort of gypsy magic
attached to this onAction business. Here are the steps I took.


1. Added an Office.COMAddIn attribute to the Connect class, setting it in
the OnConnection method.

2. Retrieved from this attribute the ProgId and packaged that inside the
"!<" and ">" and set the OnAction attribute of the CommandBarPopup to it.

3. Remained thoroughly unsurprised when it didn't work:

"The macro '!<ProgramLauncher.Connect>' cannot be found"


Obviously I don't know much about Visual Basic - how is the event handler
ever getting attached to the Button - is there something going on in your
CreateTag function?

Clearly I have the wrong end of the stick here, as even if an event handler
with the appropriate signature were to somehow be called, there is no event
handler that involves the CommandBarPopup class.

Where am I going wrong here?


Thanks again,
David

 

[mrmack]

so, have I missed something obvious, or was my original question just not
clear enough?