Data validation
 

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

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" wrote in message
...
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

Shame about the first one :-)

But the second one is brilliant!!!


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


Regards,


Gavin


"Bob Umlas" wrote in message
...
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" wrote in message
...
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

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




"Bob Umlas" wrote in message
...
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" wrote in message
...
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

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.

gavin wrote:

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

"Bob Umlas" wrote in message
...
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" wrote in message
...
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





--

Dave Peterson