Dear Dave,
1) i hv run time error type mismatch at ".list = lookuplist"
2) anyway to make the cell e1 arrow button disappear?
3) yr code is suit my needs. thanks alot. but would like to know if i can
key in other than the value in the list? you see, actually not all the cell
in the same column need to refer to the list.
4)what is application.caller
5)again, you have been so helpful.

thanks


Private Sub Workbook_Open()
Dim ddBox As DropDown
Dim lookUpList As Variant

myDDName = "myDDForColE"

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

lookUpList = Array(Sheet1.Cells.Range("a2:a300"))
With Sheet5.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

Public myDDName As String
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


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






"Dave Peterson" wrote:

And uncomment that .visible line in the worksheet_selectionchange.

If you select a cell in column E and don't select a value, then the dropdown
will still be visible when you click on a cell not in column E.



Dave Peterson wrote:

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

--

Dave Peterson