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
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
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks