PDA

View Full Version : Solved: Trapping a FillDown event



Dr Fear
06-26-2009, 05:15 AM
I'd like to usurp the normal behaviour for filling cells (down or up), that is, I think I need to trap the event that user has selected a cell then moved the cursor to the bottom right corner and watched it change to a plus sign.

But that does not seem to be a trappable event, at least, there is no event handler for it in the usual drop-down list or anywhere I can see in help or web search.

If I can't do anything else, I would at least like to be able to stop users from doing this at all. (But - I can't just disable events, I need all the usual event traps active.)

Failing that, does anyone know the name of the menu that follows right-click select-for-fill, and if I modify that right-click menu, and use it to call the function I really want, can I prevent the subsequent fill?

But the ideal I am really hoping for is to allow a user to do go through the entire process of selecting a range with the plus sign, using either right or left click and drag, indicating it would be filled, but then usurp the .FillDown method itself to do something other than .FillDown.

Any pointers?

Oorang
06-29-2009, 09:32 AM
Good question. I don't think you can trap that feature. You can disable it with:
Excel.Application.CellDragAndDrop = false

And I believe the commandbar you asked about is named "AutoFill":)

Dr Fear
06-29-2009, 03:46 PM
Thanks very much indeed for those pointers.

I did rather suspect the answer to the trap question might be "you cannot" !

I'd still like to know how to overwrite a built-in method but I guess that is really a different and more generic question I will start in a new thread.

Oorang
06-30-2009, 01:39 AM
Unfortunately VBA only supports a very small feature set of OOP. If you want to get "fancy" you need to use C#. You can do basic things like using a function with the same name and parameters to intercept unprefixed, and if the object has events you can intercept those using the "WithEvents" keyword. But there are some very real limitations.

An silly example of using scope to overide a call to the msgbox is here: http://www.vbaexpress.com/forum/showthread.php?t=25809