Is there a way to override buttons in the autofilter context menu?

Add-in Express™ Support Service
That's what is more important than anything else

Is there a way to override buttons in the autofilter context menu?
 
kmcdowellLB


Guest


Hi

I'm trying to see if there is any way to override/hijack the buttons in the autofilter context menu; the menu that pops up when you click on the "down-arrow" of a header to filter/sort it. In particular I want to hijack the Sort Smallest to Largest, Sort A to Z buttons.

[img]https://drive.google.com/open?id=1vhx895Ix3jIJWMdD0RNx7iKTeAOeYv08[/img]
Posted 30 Mar, 2020 14:23:46 Top
Andrei Smolin


Add-in Express team


Posts: 18829
 Joined: 2006-05-11
Hello,

They didn't connect the Ribbon command to these controls: the Ribbon Command component isn't triggered when you click that button. I've also checked all the CommandBar controls having "Sort" in their captions: 

Sub test_VBA_macro()
Dim cbb As Office.CommandBars
Set cbb = Application.CommandBars
Dim cbcs As Office.CommandBarControls
Set cbcs = cbb.FindControls(msoControlButton)
Dim i As Integer
Dim cbc As Office.CommandBarControl
For i = 1 To cbcs.Count
    Set cbc = cbcs.Item(i)
    Dim c As String
    c = cbc.Caption
    If InStr(LCase(c), "sort") <> 0 Then
        Debug.Print c, CStr(cbc.ID)
    End If
Next i
End Sub


None of them is triggered though: I use a Built-in Control Connector component to connect to them.

Summing up, I don't think it is possible to intercept clicking that button.

You can find however whether it is clicked, though: the caption of the Undo CommandBar control (Id=128) is "&Undo Sort" in this case; I suppose the caption is different in non-English Excel. You can check the Undo control in the OnUpdate event of the Office.CommandBars object; in Add-in Express, that event is mapped to the CommandbarsUpdate event of the ADXExcelAppEvents component. Note that this event occurs quite often: your code should be effective. 


using Office = Microsoft.Office.Core;
//...
Office.CommandBars commandBars = ExcelApp.CommandBars;
Office.CommandBarComboBox undoControl = commandBars.FindControl(Office.MsoControlType.msoControlSplitDropdown, 128, Type.Missing, false) as Office.CommandBarComboBox;
string action = undoControl.get_List(1);
System.Windows.Forms.MessageBox.Show(action);
Marshal.ReleaseComObject(undoControl);
Marshal.ReleaseComObject(commandBars);


Also find a code sample at https://www.add-in-express.com/forum/read.php?FID=5&TID=13077.


Andrei Smolin
Add-in Express Team Leader
Posted 31 Mar, 2020 03:07:05 Top