CODEWITHSUNDEEP
vbaexcel
Cyril
Cyril

Reputation: 6829

Worksheet_SelectionChange not performing desired task

Background:

I don't appear to know what I'm doing with SelectionChange and would like some help to ensure I'm using it correctly.

I have right-clicked This Workbook, in the VBA window, and selected View Code. I have my code in that field. In the immediate window (ctrl+g), I have input:

Application.EnableEvents=True

Code:

The code I'm using to test is supposed to display the target row/column in a cells(1,1):

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim r, c As Integer
    If Selection.Count = 1 Then
        c = Target.Column
        r = Target.Row
        Cells(1, 1).Value = "Cells(" & r & ", " & c & ")"
    End If
End Sub

Question:

Am I missing something to enable the selection change event?

Upvotes: 1

Views: 214

Answers (2)

CLR
CLR

Reputation: 12279

If you right click on ThisWorkbook and select View Code you will see two dropdowns above the code window. The leftmost allows you to select (General) or Workbook. If you select Workbook, the rightmost dropdown will provide all the events you can utilise at the Workbook level. Worksheet_SelectionChange is not there as it's not a Workbook level event.

Workbook level events include events like Open, Close and SheetChange. Note: SheetChange fires when the user changes the sheet selected, not makes a change to a given sheet.

If you do the same but with the Sheet1 (Sheet1) object, the leftmost dropdown will show (General) and Worksheet. Selecting this will provide the events available at the Worksheet level. Pick SelectionChange and the VBE will automatically create the shell of the event ready for you to populate with your code.

Note, this event will only fire if a cell is changed on the sheet you've added the code to so keep this in mind when creating your event(s).

Upvotes: 3

braX
braX

Reputation: 11755

Don't right click on ThisWorkbook, instead, right click on the Sheet1.

Upvotes: 1

Related Questions