Selection Change event

[51DegreesNorth]

Hi,

I'd like to keep an eye an a certain range: if the contents of one or more of the cells in that range changes, the selection change event must call a subroutine to perform some actions.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

 If Intersect(Target, Range("A1:A6")) Is Nothing Then
    Exit Sub
 End If
 ' but if there is an overlap:
    Range("E5").Select ' as an example, I'll replace it with Call Some_sub later.
 End Sub

This code works, it selects E5 when the selection change event occurs. That's not what I want. Suppose there's a number in A3 which must be altered or deleted. Click on the cell and you end up at E5 before you had a change to edit or delete A3. The above code is on sheet level. The VBA Project contains no mudules and the workbook is otherwise completely empty.

I'm new to this whole 'events' thing ... Can you point me in the right direction please ? Is this even the correct approach for what I'm trying to do ?

Thank you !

 

[Micron]

I'm not understanding. If you don't want to go to E5 then why not just replace that line with what you do want? What you have basically says "If something other than A1 to A6 was selected, do nothing - exit the procedure. Any code outside of that IF block will execute, which is why you go to E5 (which btw will run the event again).

Maybe you're saying you don't just want an event that fires every time you choose a different cell? Could be that you want the worksheetChange event.

 

[Fluff]

Sounds like you should be using a change event & not a selection change

Private Sub Worksheet_Change(ByVal Target As Range)

 

[51DegreesNorth]

Thank you both.
@ Micron, the E5 thing is just a placeholder. I will call the sub once this piece of code does what I want, which is the trigger to fire after the contents of a cell has changed. Now it seems to fire on clicking/selecting of the cell, and that's too early. Users won't be able to make changes to the cell. The sub looks at the kind of change made and takes it from there.
@ Fluff - I've assumed (assumptions, always dangerous) that "watching" a range of 50 or so cells takes far less time than watching the whole sheet.

 

[Micron]

Change event won't detect that 5 was replaced with 5 (AFAIK) so if that matters to you, use a module level variable like this:

Option Explicit
Dim CellValue As Variant

Then when selection changes, set the variable value:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rPrintArea As Range

 If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
   CellValue = Target
 End If
 
 End Sub

Then when an entry is made, see if the new value is the same as the old value:

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Application.Intersect(Range("A1:A10"), Range(Target.Address)) Is Nothing Then
   If Target <> CellValue Then
      MsgBox "Cell " & Target.Address & " has changed."
   End If
End If

End Sub

Otherwise, forget the variable and use only the change event, I guess. Not sure what range, if any, that you want to be concerned with.

 

[Fluff]

You can still "watch" certain cells, just a change event only triggers when you physically change the value in a cell, whereas the SelectionChange triggers whenever you select a cell

 

[Fluff]

If you are only changing a single cell you can use something like

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Not Intersect(Target, Range("A1:A6")) Is Nothing Then
      Range("E5").Select ' as an example, I'll replace it with Call Some_sub later.
   End If
End Sub

 

[51DegreesNorth]

Some quick testing later .... everything works fine now, even with cells with data validation. I have a much clearer understanding of the difference between the two events now.
Thank you again.

 

[Fluff]

Glad we could help & thanks for the feedback.