when certain number is typed in a cell to display a message

[Guest]

Hello I want to display a message "Please contact your Manager" whenever the
number 040 0r 044 is entered into a cell. F another number besides i040 or
044 is entred nothing happens I tried an if formula but that gets
deleted whenever a number is entred in the cell HELP! thanks

 

[Guest]

Try 'data'....'validation' then stick your formula and error message in there.

 

[Guest]

Thanks Tim m
I did some thinking over the weekend and I came up with the follwoing
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells = 40 Then Call ShowForm
If Target.Cells = 44 Then Call ShowForm
End Sub
This works but I was wondering, did if I leave something out ?

 

[Guest]

Your original post indicated that you are working with TEXT numbers, not real
numbers, as indicated by the leading zero........this should work if thats
the case.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Target Is Nothing Then
If Target.Value = "040" Then
MsgBox "Please contact your Manager (040)"
ElseIf Target.Value = "044" Then
MsgBox "Plese contact your Manager (044)"
ElseIf Not Application.Intersect(Target, Range("A1")) Is Nothing Then
Else
End If
End If
End Sub


Or, this one limits the action to only cell A1.

Private Sub Worksheet_Change(ByVal Target As Range)
If Application.Intersect(Target, Range("a1")) = "040" Then
MsgBox "Please contact your Manager (040)"
ElseIf Application.Intersect(Target, Range("a1")) = "044" Then
MsgBox "Plese contact your Manager (044)"
ElseIf Not Application.Intersect(Target, Range("A1")) Is Nothing Then
End If
End Sub

hth
Vaya con Dios,
Chuck, CABGx3

 

[Guest]

CLR
This is good. I had posted a comment before I received this geat response.
This is what I came up with during the weekend.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells = 40 Then Call ShowForm
If Target.Cells = 44 Then Call ShowForm
End Sub

This is somewhat different from yours but will this work the same way?

 

[Guest]

You were on the right track, but your specific code does not work at all,
as-is, in my Excel 97. First off, it is looking for a number 40 instead of
TEXT 040, and then is it calling another macro (ShowForm) which does not yet
exist....

hth
Vaya con Dios,
Chuck , CABGx3

 

[Guest]

CLR Thanks for your prompt response. I have the cells formatted as
"general" and it accepts 040, or 40; the Show form macro does exist. When I
tested this it works. Since I have never done this before, Just want to
make sure that I did not omit something .

 

[Guest]

I don't know what else to say, except that cells formatted as "General" in my
workbooks will not accept a text value of 040 as an entry. They
automatically convert it to 40, a number. Your macro, as is, will probably
not find 040 whereas mine, as is, will not find 40. If you wish to use
yours, and want 040, try changing

If Target.Cells = 40 Then Call ShowForm
to
If Target.Cells = "040" Then Call ShowForm

hth
Vaya con Dios,
Chuck, CABGx3

 

[Guest]

CLR
I will do that. and send out the report and see what happens. Have a
great day!