worksheet change event doesn't work

[gig]

I have the following simple code for example reasons. For some reason,
when the range "attn" is changed, the macro won't operate.

Here is the simple code I have:

Private Sub Worksheet_Change(ByVal attn As Excel.Range)

Application.EnableEvents = False

MsgBox "hello"


Application.EnableEvents = True

End Sub

There is code to be inserted where the msgbox is. Any suggestions are
greatly appreciated.

Thank you,
Greg

 

[Dave Peterson]

You don't want to change the procedure:
Private Sub Worksheet_Change(ByVal Target As Range)

But you can check to see if the range Attn was in the range that got changed.
(Attn is a range name on that sheet???)

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myCell As Range
Dim myIntersect As Range

Set myIntersect = Nothing
On Error Resume Next
Set myIntersect = Intersect(Target, Me.Range("attn"))
On Error GoTo 0

If myIntersect Is Nothing Then
'not in there
Exit Sub
End If

For Each myCell In myIntersect.Cells
MsgBox myCell.Address
Next myCell

End Sub

 

[gig]

Yes, "attn" is the range name on the worksheet. When data is entered
into the "attn" range, I want the macro to commence.

Greg

 

[Bob Phillips]

Private Sub Worksheet_Change(ByVal Target As Range)

APplication.EnableEvents = False
On Error Goto ws_exit:
If Not Intersect(Target, Me.Range("attn")) Is Nothing Then
myMacro
End If

ws_exit:
Application.EnableEvents = True
End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)