What's in this range? Sheet1.Cells.Range("a2:a300")

Do you have empty cells in that range?

If you always have stuff in A2:A???, but not sure how far down to go:

with sheet1
lookUpList = .Range("a2:a" & .cells(.rows.count,"A").end(xlup).row).Value
end with

You could add them one by one and check to see if they're ok first:

In the ThisWorkbook module:

Option Explicit

Private Sub Workbook_Open()
Dim ddBox As DropDown
Dim lookUpRng As Range
Dim myCell As Range

myDDName = "myDDForColE"

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

' lookUpList = Array(Sheet1.Cells.Range("a2:a300"))
Set lookUpRng = Sheet1.Cells.Range("a2:a300")

With Sheet5.Range("f1")
Set ddBox = .Parent.DropDowns.Add(.Left, .Top, .Width, .Height)
End With

With ddBox
For Each myCell In lookUpRng.Cells
If IsEmpty(myCell) Then
'do nothing
Else
.AddItem myCell.Value
End If
Next myCell
.Name = myDDName
.Visible = False
.OnAction = ThisWorkbook.Name & "!PutValue"
End With

End Sub

(And you changed from column E to column F! This makes less sense now:
myDDName = "myDDForColE"--but it doesn't hurt.)




mango wrote:

dear dave,
i still got the run time error "unable to set the list property of the
dropdown class after i changed to "lookuplist =
sheet1.cells.range("a2:a300").value

i wonder if i put to the correct events.
thanks alot.

(in thisworkbook)
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"))
lookUpList = Sheet1.Cells.Range("a2:a300").Value

With Sheet5.Range("f1")
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

(in sheet5)
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("f:f")) 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

(in modules)
Public myDDName As String

Sub PutValue()

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

MsgBox Application.Caller

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

End Sub

------------------------------------------------------------------------------------------



"Dave Peterson" wrote:

Change this line:
lookUpList = Array(Sheet1.Cells.Range("a2:a300"))
to
lookUpList =Sheet1.Cells.Range("a2:a300").value

The arrow should disappear when you click on a different cell. (make sure you
uncomment that line (in the second post).

'Me.DropDowns(myDDName).Visible = False
remove the apostrophe:
Me.DropDowns(myDDName).Visible = False

And it should be invisible right after it's added.

If that arrow/box is still visible, are you sure it belongs to this dropdown?


===
application.caller is the thing that you hit to run the macro.

Try putting

msgbox application.caller

in the code (just for a time or two).


And an unintended consequence...just select your cell and type. You'll be able
to see what you're typing in the formula bar. When you click off that cell
(without using the dropdown), you'll see that it worked.

But these dropdowns don't support that kind of behavior. Comboboxes from the
control toolbox do, though, but the code would have to change.

mango wrote:

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


--

Dave Peterson


--

Dave Peterson