VBA selection change event not working

[pjpassa]

I am trying to create a cell that updates whenever a cell within a set range is selected. The cell will be changed to the selected cell's value. However, I am having problems with the selection change event. Here is what I have so far:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)


MsgBox "Selection Changed"


    If Target.Count > 1 Then Exit Sub
    If Intersect(Target.Address, Range("TEMP")) Then MsgBox "Selection is in range"
    
End Sub

I put in the Message boxes just to troubleshoot, but I am not even getting the "Selection Changed" message box. Any idea what I am doing wrong? Also, do I have anything wrong with my code to detect if the selection is in the TEMP named range?

Eventually, I want to update a cell, say A1, to be the selected cell's value.

I am using Office 2010

Thanks!

 

[JLGWhiz]

I am trying to create a cell that updates whenever a cell within a set range is selected. The cell will be changed to the selected cell's value. However, I am having problems with the selection change event. Here is what I have so far:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)


MsgBox "Selection Changed"


    If Target.Count > 1 Then Exit Sub
    If Intersect(Target.Address, Range("TEMP")) Then MsgBox "Selection is in range"
    
End Sub

I put in the Message boxes just to troubleshoot, but I am not even getting the "Selection Changed" message box. Any idea what I am doing wrong? Also, do I have anything wrong with my code to detect if the selection is in the TEMP named range?

Eventually, I want to update a cell, say A1, to be the selected cell's value.

I am using Office 2010

Thanks!

Try removing .Address after Target in your Intersect function.
Actually, you should probably state it like:

If Not Intersect(Target, Range("Temp") Is Nothing Then...etc.

 

[jim may]

Give this a shot...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
MsgBox "Your Selected Cell address is " & Target.Address
Set myRng = ActiveWorkbook.Names("TEMP").RefersToRange
    
    For Each C In myRng
        If C = Target Then
        MsgBox "Your Selection is in the range TEMP"
        Exit For
        End If
    Next C
End Sub

 

[jim may]

Woops, my code is FLAWED,, HOLD ON!!

 

[jim may]

Better... (below)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
'MsgBox "Your Selected Cell address is " & Target.Address
Set myRng = ActiveWorkbook.Names("TEMP").RefersToRange

    For Each C In myRng
        If C.Address = Target.Address Then
        MsgBox "Your Selection is in the range TEMP"
        Exit For
        End If
    Next C
End Sub
[code]

 

[Marcelo Branco]

Hi,
Probably the events are disabled.
To fix: create and run this Sub

Sub Test()
Application.EnableEvents = True
End Sub

Or

enter in the Immediate Window
Application.EnableEvents = True
and hit Enter

M.

 

[pjpassa]

Thank you for replying, Marcelo, Jim May, and JLGWhiz. Your input was helpful. I solved my problems. It wasn't the Application.EnableEvents setting; my code was in a module instead of in the worksheet code....

Here is my final, working code.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)


    If Target.Count > 1 Then Exit Sub
        
        
    Set myRng = ActiveWorkbook.Names("TEMP").RefersToRange
    
    For Each C In myRng
        If C.Address = Target.Address Then
            Range("A2").Value = Target.Value
            Exit For
        End If
    Next C
    
End Sub