worksheet change event doesn't work

  • Thread starter Thread starter gig
  • Start date Start date
G

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
 
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
 
Yes, "attn" is the range name on the worksheet. When data is entered
into the "attn" range, I want the macro to commence.

Greg
 
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)
 
Back
Top