Worksheet_change not working how i expected

[aberenguel]

Here is the code i'm working with

Private Sub Worksheet_Change(ByVal Target As Range)

Dim trgtCell As Range
Set trgtCell = Range("c22")
If trgtCell.Text = "Use Address from PNCM verb" Then
MsgBox "ANALYST: Confrim Mainframe is coded."
End If

End Sub

The problem i am having is annoying. Once the target cell = "use Address from PNCM verb" the msgbox appears every time any cell on the spreadsheet changes. I want the msgbox to appear only once (whenever the target cell = "Use address from PNCM verb")

any ideas?

thanks

 

[Legacy 98055]

The code between the "If Then...End If" only runs if the range c22 has actually changed. See the Intesect method in VBA help.

<table width="100%" border="1" bgcolor="White" style="filterrogid:DXImageTransform.Microsoft.Gradient(endColorstr='#C0CFE2', startColorstr='#FFFFFF', gradientType='0');"><tr><TD><font size="2" face=Courier New>  <font color="#0000A0">Private</font> <font color="#0000A0">Sub</font> Worksheet_Change(ByVal Target <font color="#0000A0">As</font> Range)

       <font color="#0000A0">If</font> <font color="#0000A0">Not</font> Intersect(Target, Range("c22")) <font color="#0000A0">Is</font> <font color="#0000A0">Nothing</font> <font color="#0000A0">Then</font>
           <font color="#0000A0">If</font> Target.Text = "Use Address from PNCM verb" <font color="#0000A0">Then</font>
               MsgBox "ANALYST: Confrim Mainframe is coded."
           <font color="#0000A0">End</font> <font color="#0000A0">If</font>
       <font color="#0000A0">End</font> <font color="#0000A0">If</font>

  <font color="#0000A0">End</font> <font color="#0000A0">Sub</font>
</FONT></td></tr></table><button onclick='document.all("105200618428890").value=document.all("105200618428890").value.replace(/<br \/>\s\s/g,"");document.all("105200618428890").value=document.all("105200618428890").value.replace(/<br \/>/g,"");window.clipboardData.setData("Text",document.all("105200618428890").value);'>Copy to Clipboard</BUTTON><textarea style="position:absolute;visibility:hidden" name="105200618428890" wrap="virtual">
Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("c22")) Is Nothing Then
If Target.Text = "Use Address from PNCM verb" Then
MsgBox "ANALYST: Confrim Mainframe is coded."
End If
End If

End Sub</textarea>

 

[aberenguel]

thanks Tom. Works perfectly!!!