Using "Select Case" inside Worksheet_Change() subroutine.....

[Robert Crandal]

I would like to monitor when any of the data in my group of
cells gets modified or deleted.

Here is how my code looks:

Private Sub Worksheet_Change (ByVal Target As Range)
Select Case Target
Case Range("A1")
MsgBox ("A1 was just modified")
Case Range "B1")
MsgBox ("B1 was just modified")
'..... etc. etc.. etc...
Case Range ("M20")
MsgBox ("M20 was just modified")
Case Else
' Do nothing
End Select
End Sub

Does anybody see anything wrong with this approach?? Is my code
correct?? How can I code this in a more efficient manner??

Thank you all!

 

[Dave Peterson]

Your code is really doing:

Private Sub Worksheet_Change (ByVal Target As Range)
Select Case Target.value
Case Range("A1").value
MsgBox "A1 was just modified"
Case Range("B1").value
MsgBox "B1 was just modified"
'..... etc. etc.. etc...
Case Range("M20").value
MsgBox "M20 was just modified"
Case Else
' Do nothing
End Select
End Sub

I bet you want:

Private Sub Worksheet_Change (ByVal Target As Range)
Select Case Target.Address
Case Range("A1").address
MsgBox "A1 was just modified"
Case Range("B1").address
MsgBox "B1 was just modified"
'..... etc. etc.. etc...
Case Range("M20").address
MsgBox "M20 was just modified"
Case Else
' Do nothing
End Select
End Sub

Or Private Sub Worksheet_Change (ByVal Target As Range)
Select Case Target.address
Case "$A$1"
MsgBox "A1 was just modified"
Case "$B$1"
MsgBox "B1 was just modified"
'..... etc. etc.. etc...
Case "$M$20"
MsgBox "M20 was just modified"
Case Else
' Do nothing
End Select
End Sub

 

[OssieMac]

Hi robert,

I would prefer it like the following then all you have to do is add ranges
to the Union instead of lots of case lines.

Note that a space and underscore is a line break in an otherwise single line
of code.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rngUnion As Range
Dim rngIsect As Range

Set rngUnion = Union(Range("A1"), _
Range("B1"), Range("M2040"), _
Range("R20:R40"))

Set rngIsect = _
Application.Intersect(Target, rngUnion)

If Not rngIsect Is Nothing Then
MsgBox Target.Address(0, 0) & _
" was just modified"
End If

End Sub