MsgBox when Cell Selected

[lucasb]

I have a written a little piece of code that i cannot seem to get to work, basically when a range is selected (in this case D9) i want a message box to appear, vbyesno choices given and then a simple clear contents to follow, can some one help me with why this does not work?

As Always, thanks in advance

<code>Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = Range("D9").Address Then

MsgBox("Do You Want To Delete all Historical Data For This Person?", vbYesNo + vbQuestion)

If vbYes Then

Range("ManData").ClearContents

Else

Exit Sub

MsgBox "Please Enter New Name, All old Data Has Been Cleared"


End Sub<code>

 

[VoG]

Try

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim x As VbMsgBoxResult
If Target.Address = Range("D9").Address Then
    x = MsgBox("Do You Want To Delete all Historical Data For This Person?", vbYesNo + vbQuestion)
    If x = vbYes Then
        Range("ManData").ClearContents
    Else
        Exit Sub
    End If
    MsgBox "Please Enter New Name, All old Data Has Been Cleared"
End If
End Sub

 

[lucasb]

[SOLVED]Re: MsgBox when Cell Selected

VoG,

Love your work, does exactly what i need.

Thanks over and over!

 

[VoG]

You are welcome. It could be simplified

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = Range("D9").Address Then
    If MsgBox("Do You Want To Delete all Historical Data For This Person?", vbYesNo + vbQuestion) = vbYes Then
        Range("ManData").ClearContents
        MsgBox "Please Enter New Name, All old Data Has Been Cleared", vbInformation
    End If
End If
End Sub

 

[lucasb]

Thanks again,

I was close, the shorter version allows me to see the couple of steps i took wron, always useful.

Cheers