Worksheet Change Event

[Tony S.]

I thare any way to execute more than one worksheet change per worksheet; say
for cells "A1" and "E1"?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range

' The variable KeyCells contains the cells that will
' cause an alert when they are changed.
Set KeyCells = Range("A1") 'another code for cell E1??

If Not Application.Intersect(KeyCells, Range(Target.Address)) _
Is Nothing Then

' Display a message when one of the designated cells has been
' changed.
' Place your code here.

End If
End Sub

 

[Don Guillett]

Sure, something like
Private Sub Worksheet_Change(ByVal Target As Range)
if target.address=range("a1") then msgbox "a1")
if target.address=range("e1") then msgbox "e1")
end sub

 

[Mike H]

Maybe this

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1,E1")) Is Nothing Then
MsgBox "You changed " & Target.Address
' Display a message when one of the designated cells has been
' changed.
' Place your code here.
End If
End Sub

Mike

 

[Tony S.]

Hi Don... thanks for your prompt respose. I'm kinda new at programming so I
could use a little more guidance if you please.
Here is my revised code: I get an "End If without block If" error

Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range


If Not Application.Intersect(KeyCells, Range(Target.Address)) _
Is Nothing Then
If Target.Address = Range("a1") Then MLV_wildcard_cell

End If

If Target.Address = Range("e1") Then ERM_wildcard_cell

End If
End Sub

I'm sure you can immediately see what is wrong, but I don't have a clue.

Tony

 

[Tony S.]

Hi Mike, Thanks for your reply.
Perhaps I was unclear with my first post. My apologies. I want to run 2
different macros depending on the value entered in each cell. "A1" would run
Macro1 and E1 would run Macro2. Hope this clears things up.

 

[JLatham]

You almost had it on your own in your response to Don's reply earlier.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then MLV_wildcard_cell
... some code here
Exit Sub ' to keep from testing for E1 which didn't happen
End If

If Target.Address = "$E$1" Then ERM_wildcard_cell
... your code for a change in E1 here
End If
End Sub

Note that Target.Address returns the string representation of the address,
complete with the absolute indicators (dollar signs).

Hope this helps.

 

[Chip Pearson]

If Target.Address = "$A$1" Then MLV_wildcard_cell

should be
If Target.Address = "$A$1" Then
MLV_wildcard_cell
Exit Sub
End If

If Target.Address = "$E$1" Then ERM_wildcard_cell

should be
If Target.Address = "$E$1" Then
ERM_wildcard_cell
End If

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Tony S.]

That's what I was looking for Chip. Thank you and everyone else who helped.
I'm sure they would have reached the same solution, had I been more detailed
in my request.

 

[Don Guillett]

Test this. I don't know what MLV_wildcard_cell refers to. Send your wb to my
address below along with instructions and a snippet of this message.

This assume that if you change A1 then MLV_wildcard_cell. Again, NO idea
what that refers to.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Range("a1") Then MLV_wildcard_cell
If Target.Address = Range("e1") Then ERM_wildcard_cell
End Sub

 

[Don Guillett]

Chip. I goofed in that I did NOT add .address after. Both of these test the
same result.

Private Sub Worksheet_Change(ByVal Target As Range)
'If Target.Address = Range("e1").Address Then MsgBox "hi"
If Target.Address = "$E$1" Then MsgBox "hi"
End Sub

 

[Don Guillett]

My response to Chip
Chip. I goofed in that I did NOT add .address after. Both of these test the
same result.

Private Sub Worksheet_Change(ByVal Target As Range)
'If Target.Address = Range("e1").Address Then MsgBox "hi"
If Target.Address = "$E$1" Then MsgBox "hi"
End Sub