Results 1 to 4 of 4
  1. #1
    cfe5001
    Guest

    SelectionChange (XL97)

    I need help with code that will trigger an event in the Worksheet_SelectionChange procedure. I have a named range on a worksheet, that if a user selects any of the cells in that range I need the move the curser to move to column B.

  2. #2
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Warrington, Cheshire
    Posts
    355
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: SelectionChange (XL97)

    Using the address of the currently active cell:

    Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    Select Case ActiveCell.Address

    Case "$D$7"
    Cells(1, 2).Select

    Case "$E$19"
    Cells(1, 2).Select
    End Select

    End Sub

    hope this helps

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: SelectionChange (XL97)

    The following code should do what you want - just change the reference to RangeName to the actual name of the range in your worksheet. The code move the cursor to the selected row but in column B.<pre>Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim lRow As Long
    If Not Intersect(Target, ActiveSheet.Range("RangeName")) Is Nothing Then
    lRow = Target.Row
    Cells(lRow, 2).Select
    End If
    End Sub</pre>

    Andrew C

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Flims, Switzerland
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SelectionChange (XL97)

    if the user selects more than one cell, do you want to 'narrow' the selection to just column B, or do you just want a single cell selected in column B which is on the same row as the first selected cell?

    if the former, then try:-

    Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    If Not Application.Intersect(Target, Range("myrange")) Is Nothing Then
    With Target
    Range(Cells(.Cells(1).Row, 2), Cells(.Cells(.Cells.Count).Row, 2)).Select
    End With
    End If
    End Sub

    if the latter, then try:-

    Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    If Not Application.Intersect(Target, Range("myrange")) Is Nothing Then
    Cells(Target.Cells(1).Row, 2).Select
    End If
    End Sub

    there are other variations... for instance, you might want to base this only on the active cell...e.g.

    Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    If Not Application.Intersect(ActiveCell, Range("myrange")) Is Nothing Then
    Cells(ActiveCell.Cells(1).Row, 2).Select
    End If
    End Sub

    I hope one of these does what you want. If not, let me know more specifically what behaviour you want and I'll tailor it further.

    Regards,

Posting Permissions

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