Data validation

[gavin]

I am using Data Validation to restrict entry into a cell to the items in a
list and I have two questions.

Firstly, can I adjust the width of the drop down box which appears when I
click on the arrow in the cell? The items in the list are only a few
characters and the box is much wider - I just want to "tidy" this up a bit.

Secondly is there a way of making the list drop down automatically when the
cell has the focus rather than having to click on the arrow - this would
just speed up the date entry as I have a number of columns with Data
Validation on the sheet?


Many thanks for any help with these,



Gavin

 

[Bob Umlas]

Firstly, no
Secondly: Right-click the sheet tab, select View Code, enter:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim tf As Boolean
On Error Resume Next
tf = Target.Resize(1, 1).Validation.InCellDropdown
If Err.Number <> 0 Then Exit Sub
If tf Then
SendKeys "%{down}"
End If
End Sub

 

[gavin]

Shame about the first one

But the second one is brilliant!!!


Thanks so much for your help, Bob - it's much appreciated.


Regards,


Gavin

 

[gavin]

Although this works perfectly I wondered if the code to be tweaked to
account for the occasions when a user opens the workbook with a cell with
Data Validation already having the focus - in which case the drop down
doesn't work. In other words this code only seems to work when a cell gains
the focus.


Regards,



Gavin

 

[Dave Peterson]

Maybe you could select an out of the way cell on that sheet when you open the
workbook:

Option Explicit
Private Sub Workbook_Open()

Dim CurWks As Worksheet
Set CurWks = ActiveSheet

With Application
.EnableEvents = False
.ScreenUpdating = False
.Goto Worksheets("sheet1").Cells _
.SpecialCells(xlCellTypeLastCell).Offset(1, 1)
CurWks.Select
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub

Now the user has to select the cell to start it off. And Bob's event will fire.