One way using a dropdown (but I used the worksheet_selectionchange event to show
the dropdown).

First, I'd only add the dropdown once--then move it to where ever I needed it.

I'd add it each time the workbook opened.

All this in a General module:

Option Explicit
Public myDDName As String
Sub auto_open()

Dim ddBox As DropDown
Dim lookUpList As Variant

myDDName = "myDDForColE"

On Error Resume Next
Sheet3.DropDowns(myDDName).Delete
On Error GoTo 0

lookUpList = Array("apple", "banana")
With Sheet3.Range("e1")
Set ddBox = .Parent.DropDowns.Add(.Left, .Top, .Width, .Height)
End With

With ddBox
.List = lookUpList
.Name = myDDName
.Visible = False
.OnAction = ThisWorkbook.Name & "!PutValue"
End With

End Sub

Sub PutValue()

Dim myDD As DropDown
Set myDD = ActiveSheet.DropDowns(Application.Caller)

With myDD
If .ListIndex -1 Then
.TopLeftCell.Value = .List(.ListIndex)
.Visible = False
.ListIndex = 0
End If
End With

End Sub

Then behind sheet3:

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

'hide it here or in the other code???
'Me.DropDowns(myDDName).Visible = False

If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("E:E")) Is Nothing Then Exit Sub

With Me.DropDowns(myDDName)
.Left = Target.Left
.Top = Target.Top
.Width = Target.Width
.Height = Target.Height
.Visible = True
End With

End Sub

I was going to hide the dropdown when you moved off the cell, but I changed
(midstream) to hiding it right after I plop the value into the cell.




mango wrote:

dear all,
1)how to refer a range in other sheet in the same workbook as an array?
instead of lookuplist = Array("apple", "banana")
how can i define a range to refer as dropdown value?
2)another thing is how to remove the dropdown icon when added to cell?

Pls help.
Thanks

Private Sub Worksheet_BeforeDoubleClick(ByVal target As Range, Cancel As
Boolean)
If Not Intersect(target, Columns("E")) Is Nothing Then
Call AddDropDown(target)
Cancel = True
End If

End Sub

Sub AddDropDown(target As Range)
Dim ddbox As DropDown
Dim i As Integer
Dim lookuplist As Variant

lookuplist = Array("apple", "banana")
With target
Set ddbox = Sheet3.DropDowns.Add(.Left, .Top, .Width, .Height)
End With

With ddbox
For i = LBound(lookuplist) To UBound(lookuplist)
.AddItem lookuplist(i)
Next i
End With

--

Dave Peterson