Hi, below is some code I got off a forum. It allows one combobox to be used
in numerous cells with a dropdown list. I've modified it a bit to not
require a double click to activate the dropdown list. In general it works
pretty well.

However there are several things I'd like to change.
1. After I select what I want from the dropdown list, I hit Enter, and the
selection moves down two rows, instead of the normal one row. I see this
line (ActiveCell.Offset(1, 0).Activate), and tried to change the 1 to 0, but
then it didn't move at all when I hit Enter. How do I fix this?

2. When the dropdown list is activated, the list does not show the first
item on the list, but rather it shows a blank. I'd prefer it show the first
item on the list when activated.

3. Not too important - After a selection is made from the dropdown list in
the combobox, that item remains highlighted. Thus the only ways to move off
that cell is with the mouse or Enter key (because it scrolls down the list
instead). I can't use the Down arrow, which would be nice because there are
about 20 rows where such selections need to be made and down arrow is often
the way I've done it prior to introducing the combobox (I used a plain
dropdown list before).

Thanks for any help you can offer! Harold
===========================
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'http://www.ozgrid.com/forum/showthread.php?t=69918 states how to fire the
event with Single Click
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Dim wsList As Worksheet
Set ws = ActiveSheet
Set wsList = Sheets("Planning")
UnProtectSheet
Cancel = True
Set cboTemp = ws.OLEObjects("Books")
On Error Resume Next
With cboTemp
'clear and hide the combo box
.ListFillRange = ""
.LinkedCell = ""
.Visible = False 'when the active cell is moved off the combobox
dropdown
'after the selection is made, this removes focus from the original cell.
End With
On Error GoTo errHandler
If Target.Validation.Type = 3 Then
'if the cell contains a data validation list
Application.EnableEvents = False
'get the data validation formula
str = Target.Validation.Formula1
str = Right(str, Len(str) - 1)
With cboTemp
'show the combobox with the list
.Visible = True
.Left = Target.Left
.Top = Target.Top
.Width = Target.Width + 15
.Height = Target.Height + 2
.ListFillRange = str
.LinkedCell = Target.Address
End With
cboTemp.Activate
End If

errHandler:
Application.EnableEvents = True
Exit Sub
ProtectSheet
End Sub


Private Sub Worksheet(ByVal Target As Range)
'Private Sub Worksheet_SelectionChange(ByVal Target As Range) originally
named this, but had to change to go with singleclick
'this is to allow the ComboBox to fire when the event is triggered
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Set ws = ActiveSheet
UnProtectSheet
Application.EnableEvents = False
Application.ScreenUpdating = True

Set cboTemp = ws.OLEObjects("Books")
On Error Resume Next
With cboTemp
.Top = 10
.Left = 10
.Width = 0
.ListFillRange = ""
.LinkedCell = ""
.Visible = False
.Value = ""
End With

errHandler:
Application.EnableEvents = True

Exit Sub
ProtectSheet
End Sub '====================================
'Optional code to move to next cell if Tab or Enter are pressed
'from code by Ted Lanham
Private Sub Books_KeyDown(ByVal _
KeyCode As MSForms.ReturnInteger, _
ByVal Shift As Integer)
'UnProtectSheet
Select Case KeyCode
Case 9 'Tab
ActiveCell.Offset(0, 1).Activate
Case 13 'Enter
ActiveCell.Offset(1, 0).Activate
'ActiveCell.Offset(1, 0).Activate this was the original line
Case Else
'do nothing
End Select
'ProtectSheet
End Sub