Auto opening of Validation list

[Joe]

I have a simple data validation rule applied to a cell which has the
validation set as List and references approx 50 cells.
What I would like to occur is that, when the sheet is selected, the
drop-down list is revealed with the mouse hovering over the down arrow of
the cell.
Although there's no problem selecting the cell when the sheet is selected, I
cannot get a macro to record the selecting of the cell dropdown list and
cannot find a way to do it. It may not be possible for this to happen at
all, but I would appreciate anyone's assistance if such a procedure can be
done.

Rob

 

[Frank Kabel]

Hi
AFAIK this is not possible

 

[Rob]

Thanks for your prompt reply Frank. I thought that would have been the case
but you and some others in this newsgroup do come up with what the rest
think are impossibilities, so I needed to ask.
Rob

 

[Gord Dibben]

Joe

This code will open the drop-down box.

Sub test()
Range("A1").Select
SendKeys "%{UP}"
'sends ALT + Up Arrow
End Sub

Does NOT position the Mouse pointer over the scroll bar of the drop-down.

Gord Dibben Excel MVP

 

[Dave Peterson]

And stealing from Gord....

Right click on the worksheet tab that should have this behavior. Then select
view code. Paste this version of Gord's macro into the code window:

Option Explicit
Private Sub Worksheet_Activate()
Application.EnableEvents = False
Me.Range("A1").Select
Application.EnableEvents = True
SendKeys "%{UP}" 'sends ALT + Up Arrow
End Sub



Whenever you click to another sheet, then come back, the macro will run.

 

[Rob]

And when you think it can't be done.....then presto!!

Thanks for the steal, Dave.
And thankyou Gord!

Rob

 

[Rob]

Gord, thanks very much!
I already answered to Dave P in another message. Don't know how this got
outa sync.
PS. Having the mouse or curser hover over the arrow was not that
important.....would have been a nice touch if it was possible.
Rob