+ Reply to Thread
Results 1 to 4 of 4

from menu Data-Validation, with List

  1. #1
    Jim at Eagle
    Guest

    from menu Data-Validation, with List

    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

  2. #2
    Patrick Molloy
    Guest

    RE: from menu Data-Validation, with List

    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


  3. #3
    Jim at Eagle
    Guest

    RE: from menu Data-Validation, with List

    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


  4. #4
    Jim at Eagle
    Guest

    RE: from menu Data-Validation, with List

    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


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1