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
|