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

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

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

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

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

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

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

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

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

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

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