VBA and Validation Lists

  • Thread starter Thread starter Network Admin
  • Start date Start date
N

Network Admin

Is there any way to automatically (without double clicking or clicking
the down arrow) display a list to choose from if a cell is used for
validation. I have numerous cells that are "validation".

Example:

Cell C2 (on sheet1) is linked to a list (defined as "Styles") on
sheet2 using the Data -> Validation from the excel menus. A user
either has to click on this cell then click the down arrow or double
click the cell in order for the choices to display.

I need to have it auto display (using VBA is fine) because the
spreadsheet will be used on a mouseless computer. So if the user tabs
over and the cell is selected, the choices will drop down, the user
can use the up and down keys to highlight what they want, hit enter,
then hit tab again to move on.

Thanks
 
I'm not sure I'd use this..but it seems to work.
Code should go into the sheet's code module.


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim lValType As Long
If Target.Cells.Count > 1 Then Exit Sub
On Error Resume Next
lValType = Target.Validation.Type
On Error GoTo 0
If lValType = xlValidateList Then SendKeys "%{down}"
End Sub


Ammend, refine & improve where needed.



keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >
 
Try holding the Alt key down and then use either the up or down arrow while
in the cell with data validation.

Neil
 
Back
Top