Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
mango
 
Posts: n/a
Default dropdown value from range in other sheet

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


  #2   Report Post  
Bob Phillips
 
Posts: n/a
Default

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




  #3   Report Post  
mango
 
Posts: n/a
Default

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





  #4   Report Post  
Bob Phillips
 
Posts: n/a
Default

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







  #5   Report Post  
mango
 
Posts: n/a
Default

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










  #6   Report Post  
Dave Peterson
 
Posts: n/a
Default

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
  #7   Report Post  
Dave Peterson
 
Posts: n/a
Default

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
  #8   Report Post  
Dave Peterson
 
Posts: n/a
Default

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
  #9   Report Post  
mango
 
Posts: n/a
Default

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

  #10   Report Post  
Dave Peterson
 
Posts: n/a
Default

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


  #11   Report Post  
mango
 
Posts: n/a
Default

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

  #12   Report Post  
Dave Peterson
 
Posts: n/a
Default

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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Transfer data from sheet to sheet Jenn Excel Discussion (Misc queries) 4 January 20th 05 04:07 PM
Linking sheets to a summary sheet in workbook gambinijr Excel Discussion (Misc queries) 4 December 16th 04 09:13 PM
linking multiple sheets to a summary sheet greg g Excel Discussion (Misc queries) 1 December 16th 04 08:43 AM
Function to automatically insert a new sheet as a result of data entry? Mark Mulik Excel Worksheet Functions 2 November 28th 04 03:21 AM
Naming & renaming a sheet tab Cgbilliar Excel Worksheet Functions 1 November 7th 04 06:57 PM


All times are GMT +1. The time now is 12:42 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"