Ken Puls
Posts: 34
Joined: 2014-12-17
|
I'm trying to build a Dynamic Menu which, when clicked, will show a list of all open workbooks in Excel. So far I've managed to create the menu, but I'm having a problem getting it to (re) populate on demand.
In testing, what I've found is if I open Excel, then click the menu, it will show Book1. But when I open another workbook, the menu shows blank. (It is firing the code to load the menu items, but comes up blank.)
If I load my add-in, then open three workbooks, then click my menu button, all three workbooks show up in the list. But if I then activate one of the other workbooks, the menu blanks out again.
In VBA, I would hook up an application level Workbook_Close and Workbook_Open event, then invalidate the menu so that it could be re-created on the fly, but I'm really not sure if this is necessary, as the event does seem to be firing when the menu is clicked.
Here's the (VB.NET) code I'm using at the moment which seems to work to populate the menu once:
Private Sub dMenu_wbList_OnCreate(sender As Object, e As ADXRibbonCreateMenuEventArgs) Handles rxMenu_Workbooks.OnCreate
For Each wb In xlApp.Workbooks
iBtn = iBtn + 1
If wb.Name.ToUpper <> "PERSONAL.XLSB" Then
rxButton = New ADXRibbonButton
With rxButton
.Caption = wb.Name
.Id = "Connect_Workbook_" & CStr(iBtn)
.Ribbons = ADXRibbons.msrExcelWorkbook
End With
e.AddControl(rxButton)
End If
Next
End Sub
Would appreciate any advice as to how to get this to work.Ken Puls, FCPA, FCMA, MS MVP |
|
Dmitry Kostochko
Add-in Express team
Posts: 2875
Joined: 2004-04-05
|
Hi Ken,
Please check the Dynamic property of your menu control, it should be set to True. Also, please have a look at the Creating Ribbon Controls at Run Time (page 140) chapter of the Add-in Express documentation. |
|
Ken Puls
Posts: 34
Joined: 2014-12-17
|
Thanks Dmitry! I never actually found the documentation, which was super helpful. Outside the event handler part, it looks like all I needed was to add the e.Clear() at the beginning.
FYI, on the documentation, the link on page 137 where it says "You may also find these files useful..." (near the bottom of the page) is broken.
Appreciate the help!Ken Puls, FCPA, FCMA, MS MVP |
|
Dmitry Kostochko
Add-in Express team
Posts: 2875
Joined: 2004-04-05
|
Hi Ken,
If you face any problems, feel free to contact us.
FYI, on the documentation, the link on page 137 where it says "You may also find these files useful..." (near the bottom of the page) is broken.
Thank you! |
|