Excel validation list drop-down arrow click event detection

Question

I want to capture an event when user shows Excel validation list. I know that validation list may be shown by clicking drop-down arrow next to cell containing validation list or when user right clicks on the cell and choose the option from context menu. Both scenarios handling would be awesome, but just "arrow scenario" is great for me too.

In Excel style it would be named something like:

BeforeDropDownValidationListShow(ByRef Cancel As Boolean)

DropDownValidationListArrowClick // worse, but would be OK too

In perfect scenario: user click on small validation list arrow next to cell containing data validation (list type) and:

• the inbuilt list does not show
• just my action (e.g. MessageBox is raised) instead.

It should work on Windows.

I know that the event not exists, but I would like to create/simulate that behaviour. I think that I must use something like MouseHooks and propbably detect where the user clicked (screen coordinates) relative to ActiveCell in Excel workbook. But maybe there is some clever and more simple way.

I am VBA programmer and very beginner in C# and VSTO, I know that the task is probably quite hard but possible. Give me some ideas please, I'll try to write code by my own. Thanks in advance!

Answer 1

You may try to customize the context menu by repurposing controls (replacing the built-in action with your own). Excel like other Office applications uses the Fluent UI (aka Ribbon UI) for customizing the UI. Read more about repurposing built-in controls in the Temporarily Repurpose Commands on the Office Fluent Ribbon article.

The Fluent UI (aka Ribbon UI) is described in depth in the following series of articles:

• Customizing the 2007 Office Fluent Ribbon for Developers (Part 1 of 3)
• Customizing the 2007 Office Fluent Ribbon for Developers (Part 2 of 3)
• Customizing the 2007 Office Fluent Ribbon for Developers (Part 3 of 3)

See Office 2016 Help Files: Office Fluent User Interface Control Identifiers for ribbon controls IDs.

Answer 2

One idea would be to "rebuild" all of the functionality of the dropdown list. If you're okay with the dropdown arrow always being shown, you could create an image with the dropdown arrow and assign a macro to it

Sub CustomDropdownClicked

'Your pre-dropdown code here

'Your code to show the actual dropdown choices here

End Sub

This would mean you'd need to write code that creates/displays an array of images that (dynamically?) get assigned text for the valid values. But you'd get to have full control over the process at that point.

You'd also want to think through how much of the data validation functionality that you need (e.g. enforcing valid choices only, showing a msgbox to help the user understand what is valid, etc.). Might be able to invoke some of that by clever use of built-in data validation that takes a back seat without having to re-write all of that functionality. I.e. hide the built-in dropdown arrow.