Is there a way when cell selected with small arrow button that the list range
will allways show from the top of list range and not from last selection
within the list range
-
Jim at Eagle

lists generally show the selected item -- if you clear the cell, the the kist
will show the items top-down
You can use the selection change event to clear the cell.
In this example, cell B6 is data validated. When I select it, the event
fires, clearing the cell's value, making the list start from the first item

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$B$7" Then
Target.Value = ""
End If
End Sub

The following code is similar, but instead of clearing the cell, sets its
value to the first item in the list (range named 'MyList')- a default value
if you will:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim cell As Range
If Target.Address = "$B$7" Then
Set cell =
ActiveWorkbook.Names.Item("MyList").RefersToRange. Range("A1")
Target.Value = cell.Value
End If
End Sub


"Jim at Eagle" wrote:

Is there a way when cell selected with small arrow button that the list range
will allways show from the top of list range and not from last selection
within the list range
-
Jim at Eagle

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
If Target.Address = "$B$2" Then
Set cell = ActiveWorkbook.Names. _
Item("Cus").RefersToRange.Range("A1")
Target.Value = cell.Value
End If

This will allways display the first name in my list named "Cus" never
allowing a change.
The code causes a lockup for about 6 sec.
The other code allways leaves B2 empty and appears to cycle for 2 sec.
because the cell is in the corner of the worksheet I might be able to place
a clear box under B2 so that when the mouse passes over it clears B2 or at
least selects first name in Cus list.
What do you think?


"Patrick Molloy" wrote:

lists generally show the selected item -- if you clear the cell, the the kist
will show the items top-down
You can use the selection change event to clear the cell.
In this example, cell B6 is data validated. When I select it, the event
fires, clearing the cell's value, making the list start from the first item

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$B$7" Then
Target.Value = ""
End If
End Sub

The following code is similar, but instead of clearing the cell, sets its
value to the first item in the list (range named 'MyList')- a default value
if you will:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim cell As Range
If Target.Address = "$B$7" Then
Set cell =
ActiveWorkbook.Names.Item("MyList").RefersToRange. Range("A1")
Target.Value = cell.Value
End If
End Sub


"Jim at Eagle" wrote:

Is there a way when cell selected with small arrow button that the list range
will allways show from the top of list range and not from last selection
within the list range
-
Jim at Eagle

Guess what happens when the mouse runs away.
I think were both using the same oar

"Patrick Molloy" wrote:

lists generally show the selected item -- if you clear the cell, the the kist
will show the items top-down
You can use the selection change event to clear the cell.
In this example, cell B6 is data validated. When I select it, the event
fires, clearing the cell's value, making the list start from the first item

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$B$7" Then
Target.Value = ""
End If
End Sub

The following code is similar, but instead of clearing the cell, sets its
value to the first item in the list (range named 'MyList')- a default value
if you will:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim cell As Range
If Target.Address = "$B$7" Then
Set cell =
ActiveWorkbook.Names.Item("MyList").RefersToRange. Range("A1")
Target.Value = cell.Value
End If
End Sub


"Jim at Eagle" wrote:

Is there a way when cell selected with small arrow button that the list range
will allways show from the top of list range and not from last selection
within the list range
-
Jim at Eagle