Solved: Msg Box code for a RANGE

[Bruce Gyger]

the following only works for E14. I want to see the MSG BOX upon the return of "1 EMT" for any cell within range E14:E200, but when I use "E14:E200" as Range, I get a Runtime Error 13: Type mismatch. Any ideas?

I should mention that I am already using Validation List for the values in this range, so that option is out.


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rng As Range
Set rng = Range("e14")
If Not Intersect(Target, rng) Is Nothing Then
If rng = ("1 EMT") Then
MsgBox "option for move-in/move-out only"
End If
End If
Set rng = Nothing
End Sub

[Cyberdude]

Try omitting the parens from this If rng = ("1 EMT")

[mdmackillop]

I think it is Target you want to check, not Rng.
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rng As Range
Set rng = Range("E14:E200")
If Not Intersect(Target, rng) Is Nothing Then
If Target = "1 EMT" Then
MsgBox "option for move-in/move-out only"
End If
End If
Set rng = Nothing
End Sub

[Bruce Gyger]

That did it. Target rather than rng. Thanks.

[YasserKhalil]

Try this
I added a line to avoid debug if you tried for example to clear the contents of more than one cell within the range(E14:E200)

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rng As Range
Set rng = Range("E14:E200")
If Not Intersect(Target, rng) Is Nothing Then
If Target.Count > 1 Then Exit Sub
If Target = "1 EMT" Then
MsgBox "Option for move-in/move-out only"
End If
End If
Set Target = Nothing
End Sub

[Bruce Gyger]

Excellent. Thank you.

[mdmackillop]

Hi Yasser


If Target.Count > 1 Then Exit Sub


Best to place this right after the Dim statements to minimise the lines of code to be processed.

[YasserKhalil]

Nice tip Mr. mdmackillop
We're learning from you every day...