Message Box Reminder

[TBS2766]

I have the following code to pop up a reminder for the user to complete another section of the form based on their selection from drop down lists located in various cells in a range. I really like how it displays the reminder but it keeps displaying it everytime anything is changed. Is there a way to adapt it so that it only appears the first time the condition is met. I am still pretty new to VBA and am sure it is probably quite simple but most of my success in VBA has been through trial and error, of which I am not having much luck with this one.


Private Sub Worksheet_Change(ByVal Target As Range)
Dim Mycell
Dim Rng
Set Rng = Range("c10:s28")
For Each Mycell In Rng
If Mycell.Value = "Off-Site Comm Support" Then
MsgBox "When Off-Site Comm Support is selected the Off-Site Community Support Verification Form located at the bottom-right of your timesheet needs completed."
End If
Next Mycell
End Sub

[CatDaddy]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Mycell
Dim Rng
Dim i as integer
i=0
Set Rng = Range("c10:s28")
For Each Mycell In Rng
If Mycell.Value = "Off-Site Comm Support" And i <> 1 Then
MsgBox "When Off-Site Comm Support is selected the Off-Site Community Support Verification Form located at the bottom-right of your timesheet needs completed."
i=1
End If
Next Mycell
End Sub

[TBS2766]

Thank you for your prompt response however it still is displaying the message everytime anything is changed on the worksheet if any of the cells have the "Off-Site Comm Support" selected.

What I would like to happen is that I as the user of this worksheet select from the drop down menu in say cell c13, the message box displays with the reminder, i click ok and then go about my business entering other information into the form.

If this is not possible maybe it could be a message box that is displayed upon saving or before printing.

[CatDaddy]

can i see the worksheet?

[Kenneth Hobs]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Mycell As Range
Dim Rng As Range
Set Rng = Intersect(Target, Range("C10:S28"))
If Rng Is Nothing Then Exit Sub
For Each Mycell In Rng
If Mycell.Value = "Off-Site Comm Support" Then
MsgBox "When Off-Site Comm Support is selected the Off-Site Community Support Verification Form located at the bottom-right of your timesheet needs completed."
Exit Sub
End If
Next Mycell
End Sub

[TBS2766]

Thanks Kenneth that worked.
Also thanks again catdaddy for your assistance as well.
I knew it had to be some simple modification but in my limited travels into VBA I haven't come in contact with the Intersect function. I love learning new things thanks again.:hi: