Message box in a formula

[test80]

hi i really need some help

i need to create a formula or macro that looks at a cell and if it
contains summin (e.g. the text 're-order') it displays a message box
that says do you want to blah blah blah with options yes or no , no
doing nothing yes linking to a macro/or another sheet etc.

I can't find how to do this. can you have a message box in a formula
and if not could this be done using a macro. it needs to be done
automaticly so whenever text enters that cell it needs to realise
straight away, not after manually clicking a macro button.

can anybody help? would be so greatfull!

have a gd sunday everybody

 

[Bob Phillips]

Put this code in a worksheet code module

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo ws_exit:
If Not Intersect(Target, Range("A1")) Is Nothing Then
If Target.Value = "re-order" Then
MsgBox "This is it"
End If
End If

ws_exit:
Application.EnableEvents = True
End Sub

Adjustto do what you want.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

hi i really need some help

i need to create a formula or macro that looks at a cell and if it
contains summin (e.g. the text 're-order') it displays a message box
that says do you want to blah blah blah with options yes or no , no
doing nothing yes linking to a macro/or another sheet etc.

I can't find how to do this. can you have a message box in a formula
and if not could this be done using a macro. it needs to be done
automaticly so whenever text enters that cell it needs to realise
straight away, not after manually clicking a macro button.

can anybody help? would be so greatfull!

have a gd sunday everybody

creating financial statements

 

[Gord Dibben]

test

You can have a message in a formula but it can nothing more than inform.

=If(A1="re-order", "Not Good", "OK")

To do something else, you would need code. To do it automatically you would
need Event Code like the following.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim Msg, Style, Title, Response
Application.EnableEvents = False
On Error GoTo enditall
If Target.Address = "$A$1" Then
If Target.Value = "re-order" Then
Msg = "Are you displeased?"
Style = vbYesNo
Response = MsgBox(Msg, Style)
If Response = vbNo Then Exit Sub
If Response = vbYes Then
MsgBox "do summin else"
End If
End If
End If
enditall:
Application.EnableEvents = True
End Sub

Copy this code. Right-click on your worksheet and "View Code". Paste in
there.

Gord Dibben XL2002

 

[Tim Zych]

There's also Data Validation under the Data menu.

Allow: Custom
Formula: =A1="re-order"

which only allows "re-order" to be typed into A1.

One of the limitations with validation is that an invalid value can be
pasted into the cell and data validation won't catch it. In fact since data
validation is copy-pastable, someone can circumvent the protection.

Check the help file for more info or post back with more about what you
want.

hi i really need some help

i need to create a formula or macro that looks at a cell and if it
contains summin (e.g. the text 're-order') it displays a message box
that says do you want to blah blah blah with options yes or no , no
doing nothing yes linking to a macro/or another sheet etc.

I can't find how to do this. can you have a message box in a formula
and if not could this be done using a macro. it needs to be done
automaticly so whenever text enters that cell it needs to realise
straight away, not after manually clicking a macro button.

can anybody help? would be so greatfull!

have a gd sunday everybody

creating financial statements