Data Validation drop-down click event

Dziękuję za odpowiedź, Artik.

That is indeed the direction I also followed, by the absence of a drop-down click event.
Nice work on how you dynamically determined the Validation ranges - I had made them a named range.

What I didn't like is that after you select the cell with the Validation in it, you have to take a second action to open the drop-down.

Then, after making the selection, you stay in the same cell, so the original zoom state is not returned.

I fixed that with the following code (your code with some minor adjustments):
I automatically expand the list and select the next cell (which could evetually be the next validation list) after the change.

Please Login or Register  to view this content.

DropDownValidationZoom.xlsm

Grtz, BartH

Originally Posted by BartH_NL

Please Login or Register  to view this content.

Wrrrrrr, my NumLock (not only mine, half the world problem) is going crazy.
I've been using the Win API for a long time. Does not cause NumLock problems and is more reliable than SendKeys (but NOT 100% reliable!).

Please Login or Register  to view this content.

Artik

Ah, Artik,

I remember having seen that too a couple of times (like on Citrix).
Had found another solution for that, but would have to look that up.

This Win API solution works well too.
(I am curious to understand when or why the first or second solution - you mention it also isn't bullet-proof - does or doesn't work.)

I've had to edit the API declaration as I use a 64b Windows/Office version:

Please Login or Register  to view this content.

I've updated the sample workbook: DropDownValidationZoom.xlsm

Grtz, BartH

In your case, I think keybd_event should work 100% because there is no window change in the code. The problem starts when we want to unload the keyboard buffer in another window. The buffer is unloaded to the first active window after issuing the command. We can never be sure (and we have no influence on it) that it will be "our" window. It may happen that between two commands (loading the buffer and activating our window) the system (Win) activates other window for a while, and the buffer will be unloaded to it. Maybe I am wrong, but somehow I have to explain this world to myself.

Originally Posted by BartH_NL

Had found another solution for that, but would have to look that up.

I'd love to get to know another approach to SendKeys

Artik