| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
when certain number is typed in a cell to display a message
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
when certain number is typed in a cell to display a message
Try 'data'....'validation' then stick your formula and error message in there.
"Wanna Learn" wrote: 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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
when certain number is typed in a cell to display a message
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 ? "tim m" wrote: Try 'data'....'validation' then stick your formula and error message in there. "Wanna Learn" wrote: 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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
when certain number is typed in a cell to display a message
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 "Wanna Learn" wrote: 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 ? "tim m" wrote: Try 'data'....'validation' then stick your formula and error message in there. "Wanna Learn" wrote: 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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
when certain number is typed in a cell to display a message
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? "CLR" wrote: 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 "Wanna Learn" wrote: 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 ? "tim m" wrote: Try 'data'....'validation' then stick your formula and error message in there. "Wanna Learn" wrote: 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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
when certain number is typed in a cell to display a message
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 "Wanna Learn" wrote: 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? "CLR" wrote: 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 "Wanna Learn" wrote: 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 ? "tim m" wrote: Try 'data'....'validation' then stick your formula and error message in there. "Wanna Learn" wrote: 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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
when certain number is typed in a cell to display a message
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 . CLR" wrote: 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 "Wanna Learn" wrote: 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? "CLR" wrote: 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 "Wanna Learn" wrote: 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 ? "tim m" wrote: Try 'data'....'validation' then stick your formula and error message in there. "Wanna Learn" wrote: 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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
when certain number is typed in a cell to display a message
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 "Wanna Learn" wrote: 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 . CLR" wrote: 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 "Wanna Learn" wrote: 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? "CLR" wrote: 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 "Wanna Learn" wrote: 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 ? "tim m" wrote: Try 'data'....'validation' then stick your formula and error message in there. "Wanna Learn" wrote: 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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
when certain number is typed in a cell to display a message
CLR
I will do that. and send out the report and see what happens. Have a great day! "CLR" wrote: 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 "Wanna Learn" wrote: 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 . CLR" wrote: 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 "Wanna Learn" wrote: 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? "CLR" wrote: 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 "Wanna Learn" wrote: 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 ? "tim m" wrote: Try 'data'....'validation' then stick your formula and error message in there. "Wanna Learn" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| a cell to display number of days in a month given in another cell | Excel Worksheet Functions | |||
| New numbers | Excel Worksheet Functions | |||
| Significant number rounding based on key cell | Excel Worksheet Functions | |||
| Help with this conditional IF statement | Excel Discussion (Misc queries) | |||
| resetting last cell | Excel Discussion (Misc queries) | |||
Linear Mode
