SelectionChange (XL97)

Topic

New Reply

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.

Reply | Quote

Replies

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

Reply | Quote

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.

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

Andrew C

Reply | Quote

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,

Reply | Quote