Help with MsgBox...

[tjb]

When the user selects the number 350 from a list validated cell or enters 350
into a list validated cell I want to use MsgBox or a UserForm to display a
message. On the MsgBox or UserForm I want two options, OK and another button
that will clear the entry of 350 from the cell, like a Cancel button or
something along those lines. Thanks!

 

[Harald Staff]

Hi

Macro work. Rightclick the sheet tab, choose View code, paste this in:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target(1).Value = 350 Then
If MsgBox("350 -you mean for real ?", vbYesNo + vbQuestion) = vbNo Then
Target(1).Value = ""
End If
End If
End Sub

HTH. Best wishes Harald

 

[tjb]

This is good except for I need the warning to occur when 350 is entered in a
specific cell, not any cell on the worksheet. Any suggestions?

 

[Dave Peterson]

You can modify Harald's suggested code to check for a range first:

Private Sub Worksheet_Change(ByVal Target As Range)

if intersect(target(1),me.range("a1")) is nothing then exit sub

If Target(1).Value = 350 Then
If MsgBox("350 -you mean for real ?", vbYesNo + vbQuestion) = vbNo Then
Target(1).Value = ""
End If
End If
End Sub

Or even check a complete column with something like:

if intersect(target(1),me.range("a:a")) is nothing then exit sub