Bob is suggesting using Data|Validation--not a dropdown from the Forms toolbar.

If that's acceptable, then take a look at Debra Dalgleish's instructions:

http://www.contextures.com/xlDataVal01.html

especially this portion:

http://www.contextures.com/xlDataVal01.html#Name



mango wrote:

sorry bob, can you see my another post "how to make dropdown arrow disappear
in vba". i want to make the arrow disappear via vba.
thanks alot

"Bob Phillips" wrote:

1) In data validation, select List from the Allow dropdown, and type
=myRangeName in the Source textbox

2) The In-cell dropdwon is a checkbox on the right when you select List

--

HTH

RP
(remove nothere from the email address if mailing direct)


"mango" wrote in message
...
sorry bob, i still not so understand.
1) after i put in the name, how to make use of the name in the vba as
follow?
2)what did u mean by in-cell dropdown box?

thanks alot





"Bob Phillips" wrote:

1) Select the range, then InsertNameDefine Name ..., and use that name
in
the list

2) Uncheck the in-cell dropdown box

--

HTH

RP
(remove nothere from the email address if mailing direct)


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