Forcing an OnKey

[Bird]

I am looking for the code that will immediately drop the validation list when I enter into a giving cell.

Setting up the event is no problem, but I am stuck the code that actually drops the validation list.

I have tried using the macro recorder to emulate dropping the list, but it just doesn’t capture it.

If there is no method for dropping the list, I know that Alt+DownArrow accomplishes the task. Is there a way to force an OnKey upon entering the cell?

Someone is going to find this question trivial, or the answer intuitive, but isn’t me. I appreciate any time that can be giving to getting me through this issue.

Thanks,

Bird

[Peter81]

Is this what you are looking for?

Application.SendKeys ("%{DOWN}")

[Bird]

Yes SIR! That’s it. Thank you so very much.

Bird

[Peter81]

Your Welcome.

[Peter Rooney]

Bird,

Taking Peter's example a stage further, you could paste this into the code
sheet of the sheet containing your dropdown list:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target = Range("Cell") Then
Application.SendKeys ("%{DOWN}")
End If
End Sub

Whenever you click the cell, the dropdown will automatically be displayed
(the code assumes that the cell containing the validation list has a range
name of "Cell")

Or, you can invoke the dropdown manually:

Sub ForceDropDownList()
Range("Cell").Select
Application.SendKeys ("%{DOWN}")
End Sub

Hope this helps - I found it very useful!

Regards

Pete

"Bird" wrote:

>
> I am looking for the code that will immediately drop the validation list
> when I enter into a giving cell.
>
> Setting up the event is no problem, but I am stuck the code that
> actually drops the validation list.
>
> I have tried using the macro recorder to emulate dropping the list, but
> it just doesn’t capture it.
>
> If there is no method for dropping the list, I know that Alt+DownArrow
> accomplishes the task. Is there a way to force an OnKey upon entering
> the cell?
>
> Someone is going to find this question trivial, or the answer
> intuitive, but isn’t me. I appreciate any time that can be giving to
> getting me through this issue.
>
> Thanks,
>
> Bird
>
>
> --
> Bird
> ------------------------------------------------------------------------
> Bird's Profile: http://www.excelforum.com/member.php...o&userid=24469
> View this thread: http://www.excelforum.com/showthread...hreadid=467791
>
>

[Peter Rooney]

Bird,

Taking Peter's example a step further, try pasting this into the code sheet
of the sheet containing your drop down list:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target = Range("Cell") Then
Application.SendKeys ("%{DOWN}")
End If
End Sub

It assumes that the cell containing the validation has a name "Cell". As
soon as you select the cell, the dropdown will be displayed.

Alternatively, you could just do it manually with this code in a standard
macro sheet.

Sub ForceDropDownList()
Range("Cell").Select
Application.SendKeys ("%{DOWN}")
End Sub

Hope this helps

Pete

"Bird" wrote:

>
> I am looking for the code that will immediately drop the validation list
> when I enter into a giving cell.
>
> Setting up the event is no problem, but I am stuck the code that
> actually drops the validation list.
>
> I have tried using the macro recorder to emulate dropping the list, but
> it just doesn’t capture it.
>
> If there is no method for dropping the list, I know that Alt+DownArrow
> accomplishes the task. Is there a way to force an OnKey upon entering
> the cell?
>
> Someone is going to find this question trivial, or the answer
> intuitive, but isn’t me. I appreciate any time that can be giving to
> getting me through this issue.
>
> Thanks,
>
> Bird
>
>
> --
> Bird
> ------------------------------------------------------------------------
> Bird's Profile: http://www.excelforum.com/member.php...o&userid=24469
> View this thread: http://www.excelforum.com/showthread...hreadid=467791
>
>

[Bird]

Yes Pete, the first option offered is pretty much the one I went with. I do however, appreciate the added information that you have taken the time to offer.