Auto opening of Validation list

  • Thread starter Thread starter Joe
  • Start date Start date
J

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
 
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
 
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
 
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.
 
And when you think it can't be done.....then presto!!

Thanks for the steal, Dave.
And thankyou Gord!

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
 
Back
Top