CODEWITHSUNDEEP
excelkeyboard-eventsselectionchangedvba
Axiom42
Axiom42

Reputation: 35

Activate a cell-range on SelectionChange via keyboard input

I activate a given cell range on a worksheet by means of the following excel-vba code:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
' msgbox("Change recognized")
If Sh.Name <> "Spielfeld" Then
    If ActiveCell.Column > 1 Then
        Dim UserSelection As Range
        Set UserSelection = Sh.Range(Cells(1, ActiveCell.Column), Cells(16, ActiveCell.Column))
        UserSelection.Activate
    End If
End If
End Sub

The code works fine when changing the column / cell via mouse input. But if I just press the left or right arrow key, excel changes the active cell / active column without selecting the defined UserSelection! Nevertheless the sub itselfs is always activated when pressing the arrow keys (a msgbox will popup even if I change the cells via the arrow keys).
Can anyone tell me why my selection is not automatically updateded when changing the column with the arrow keys? How can I do this?

Upvotes: 2

Views: 1710

Answers (1)

Gary&#39;s Student
Gary&#39;s Student

Reputation: 96753

You must Select rather than Activate

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
If Sh.Name <> "Spielfeld" Then
    If ActiveCell.Column > 1 Then
        Dim UserSelection As Range
        Set UserSelection = Sh.Range(Cells(1, ActiveCell.Column), Cells(16, ActiveCell.Column))
        Application.EnableEvents = False
            UserSelection.Select
        Application.EnableEvents = True
    End If
End If
End Sub

Upvotes: 1

Related Questions