If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
reminder notifications in a column
I have a need for a reminder when particular data is enterd into a cell of a
certain column. In a previous posting I found exactly what I need except for one problem and I really know nothing of writing codes. On 3 14-05 Jason Morin replied to a post from red_oceanus on this same matter. The code he presented pertains to the first column. I need the message to work in the fourth column only. I tried to alter the "1" to "4" in his code but then I recieve a pop up message in any of the first four columns instead of only the fourth column. Everything else I tried doesn't work at all. How can I edit this code to suit my needs? Jason's response as follows: Assuming data entry cells are col. A, right-click the worksheet tab, choose "View Code", copy in the code below, and press ALT+Q to close VBE. Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim sMsg As String With Target If .Column 1 Then Exit Sub If .Count 1 Then Exit Sub Application.ScreenUpdating = False Select Case LCase(.Value) Case "new instrument" sMsg = "Call Propertry Accounting" Case "new house" sMsg = "Call Inspector" Case "new car" sMsg = "Buy Insurance" Case "robbed" sMsg = "Buy Gun" 'additional messages here Case Else sMsg = "Don't Recognize This" End Select MsgBox sMsg Application.ScreenUpdating = True End With End Sub |
#2
|
|||
|
|||
Hi
If .Column 4 Then Exit Sub ( meaning not equal to 4) HTH. Best wishes Harald "L Mieth" skrev i melding ... I have a need for a reminder when particular data is enterd into a cell of a certain column. In a previous posting I found exactly what I need except for one problem and I really know nothing of writing codes. On 3 14-05 Jason Morin replied to a post from red_oceanus on this same matter. The code he presented pertains to the first column. I need the message to work in the fourth column only. I tried to alter the "1" to "4" in his code but then I recieve a pop up message in any of the first four columns instead of only the fourth column. Everything else I tried doesn't work at all. How can I edit this code to suit my needs? Jason's response as follows: Assuming data entry cells are col. A, right-click the worksheet tab, choose "View Code", copy in the code below, and press ALT+Q to close VBE. Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim sMsg As String With Target If .Column 1 Then Exit Sub If .Count 1 Then Exit Sub Application.ScreenUpdating = False Select Case LCase(.Value) Case "new instrument" sMsg = "Call Propertry Accounting" Case "new house" sMsg = "Call Inspector" Case "new car" sMsg = "Buy Insurance" Case "robbed" sMsg = "Buy Gun" 'additional messages here Case Else sMsg = "Don't Recognize This" End Select MsgBox sMsg Application.ScreenUpdating = True End With End Sub |
#3
|
|||
|
|||
That did the trick! I needed this years ago!
A couple more questions if I may. 1.In the code there is a line beneath the one I changed. "If .count 1 Then exit Sub." What does this line refer to? 2.As it is now I get a reminder to pop up in column four only (good thing). But there is always a reminder. I just need a reminder when certain data is entered. I am copying the code as I have it now. You will see the message I need for each particular entry. Those specifically are the only ones that need a reminder. Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim sMsg As String With Target If .Column 4 Then Exit Sub If .Count 4 Then Exit Sub Application.ScreenUpdating = False Select Case LCase(.Value) Case "hp 47" sMsg = "Copy to Great American!" Case "hp47" sMsg = "Copy to Great American!" Case "rdc 1961" sMsg = "Copy to Great American!" Case "rdc1961" sMsg = "Copy to Great American!" Case "7311" sMsg = "Copy to Great American!" Case "7320" sMsg = "Copy to Great American!" Case "2158" sMsg = "Copy to Great American!" Case "9679" sMsg = "Copy to Great American!" Case "9674" sMsg = "Copy to Great American!" Case "9125" sMsg = "Copy to Great American!" Case "9203" sMsg = "Copy to Great American!" Case "9540" sMsg = "Copy to Great American!" Case "9785" sMsg = "Copy to Debra Smith!" Case "9831" sMsg = "Copy to Debra Smith!" Case "hp 10" sMsg = "Copy to Carl Detering!" Case "hp10" sMsg = "Copy to Carl Detering!" Case "hedlt" sMsg = "Copy to Steven Stockseth!" Case "heldt" sMsg = "Copy to Steven Stockseth!" Case "jbs" sMsg = "Copy to Vernon Bates!" Case "avis" sMsg = "Copy to Herbert Morrison!" 'additional messages here' End Select MsgBox sMsg Application.ScreenUpdating = True End With End Sub Much thanks Linda "Harald Staff" wrote: Hi If .Column 4 Then Exit Sub ( meaning not equal to 4) HTH. Best wishes Harald "L Mieth" skrev i melding ... I have a need for a reminder when particular data is enterd into a cell of a certain column. In a previous posting I found exactly what I need except for one problem and I really know nothing of writing codes. On 3 14-05 Jason Morin replied to a post from red_oceanus on this same matter. The code he presented pertains to the first column. I need the message to work in the fourth column only. I tried to alter the "1" to "4" in his code but then I recieve a pop up message in any of the first four columns instead of only the fourth column. Everything else I tried doesn't work at all. How can I edit this code to suit my needs? Jason's response as follows: Assuming data entry cells are col. A, right-click the worksheet tab, choose "View Code", copy in the code below, and press ALT+Q to close VBE. Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim sMsg As String With Target If .Column 1 Then Exit Sub If .Count 1 Then Exit Sub Application.ScreenUpdating = False Select Case LCase(.Value) Case "new instrument" sMsg = "Call Propertry Accounting" Case "new house" sMsg = "Call Inspector" Case "new car" sMsg = "Buy Insurance" Case "robbed" sMsg = "Buy Gun" 'additional messages here Case Else sMsg = "Don't Recognize This" End Select MsgBox sMsg Application.ScreenUpdating = True End With End Sub |
#4
|
|||
|
|||
Harald will correct me if I steer you wrong, but...
#1. .count with the dot in front of it means that it refers back to the previous With statement. In this case "with Target". So your code is checking to see how many cells are changing. If it's more than one, the sub exits. (I like: "if .cells.count 1 then exit sub" just because it makes for easier reading! #2. You can make sure the sMsg is "" if you don't find a reason to change it. Then after you check all the entries, you can see what sMsg is. If it's still "", then don't bother with the MsgBox. #3. You didn't ask! You can stack values inside the Case statement: Case Is = "hp 47", "hp47", "rdc 1961", "rdc1961" might be easier to read and it surely makes for less typing. #4. You didn't ask, again! You check for "hp 47" and "hp47", but what about "hp 47"? Maybe eliminating all the spaces and just checking once would be worth the trouble So this is one way with those suggestions added: Option Explicit Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim sMsg As String With Target If .Column 4 Then Exit Sub If .Count 1 Then Exit Sub '--- changed back to 1 sMsg = "" Select Case Application.Substitute(LCase(.Value), " ", "") Case Is = "hp47", "rdc1961" sMsg = "Copy to Great American!" Case Is = "7311", "7320", "2158", "9679", "9674", _ "9125", "9203", "9540" sMsg = "Copy to Great American!" Case "9785", "9831" sMsg = "Copy to Debra Smith!" Case "hp10" sMsg = "Copy to Carl Detering!" Case "hedlt", "heldt" sMsg = "Copy to Steven Stockseth!" Case "jbs" sMsg = "Copy to Vernon Bates!" Case "avis" sMsg = "Copy to Herbert Morrison!" 'additional messages here' End Select If sMsg = "" Then 'do nothing Else MsgBox sMsg End If End With End Sub ps. If you're using xl2k or higher, you can change this line from: Select Case Application.Substitute(LCase(.Value), " ", "") to: Select Case Replace(LCase(.Value), " ", "") (Replace was added in xl2k.) I also got rid of the application.screenupdating lines. I don't think you need them in this code. They don't hurt, but you're not doing anything where they can help. L Mieth wrote: That did the trick! I needed this years ago! A couple more questions if I may. 1.In the code there is a line beneath the one I changed. "If .count 1 Then exit Sub." What does this line refer to? 2.As it is now I get a reminder to pop up in column four only (good thing). But there is always a reminder. I just need a reminder when certain data is entered. I am copying the code as I have it now. You will see the message I need for each particular entry. Those specifically are the only ones that need a reminder. Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim sMsg As String With Target If .Column 4 Then Exit Sub If .Count 4 Then Exit Sub Application.ScreenUpdating = False Select Case LCase(.Value) Case "hp 47" sMsg = "Copy to Great American!" Case "hp47" sMsg = "Copy to Great American!" Case "rdc 1961" sMsg = "Copy to Great American!" Case "rdc1961" sMsg = "Copy to Great American!" Case "7311" sMsg = "Copy to Great American!" Case "7320" sMsg = "Copy to Great American!" Case "2158" sMsg = "Copy to Great American!" Case "9679" sMsg = "Copy to Great American!" Case "9674" sMsg = "Copy to Great American!" Case "9125" sMsg = "Copy to Great American!" Case "9203" sMsg = "Copy to Great American!" Case "9540" sMsg = "Copy to Great American!" Case "9785" sMsg = "Copy to Debra Smith!" Case "9831" sMsg = "Copy to Debra Smith!" Case "hp 10" sMsg = "Copy to Carl Detering!" Case "hp10" sMsg = "Copy to Carl Detering!" Case "hedlt" sMsg = "Copy to Steven Stockseth!" Case "heldt" sMsg = "Copy to Steven Stockseth!" Case "jbs" sMsg = "Copy to Vernon Bates!" Case "avis" sMsg = "Copy to Herbert Morrison!" 'additional messages here' End Select MsgBox sMsg Application.ScreenUpdating = True End With End Sub Much thanks Linda "Harald Staff" wrote: Hi If .Column 4 Then Exit Sub ( meaning not equal to 4) HTH. Best wishes Harald "L Mieth" skrev i melding ... I have a need for a reminder when particular data is enterd into a cell of a certain column. In a previous posting I found exactly what I need except for one problem and I really know nothing of writing codes. On 3 14-05 Jason Morin replied to a post from red_oceanus on this same matter. The code he presented pertains to the first column. I need the message to work in the fourth column only. I tried to alter the "1" to "4" in his code but then I recieve a pop up message in any of the first four columns instead of only the fourth column. Everything else I tried doesn't work at all. How can I edit this code to suit my needs? Jason's response as follows: Assuming data entry cells are col. A, right-click the worksheet tab, choose "View Code", copy in the code below, and press ALT+Q to close VBE. Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim sMsg As String With Target If .Column 1 Then Exit Sub If .Count 1 Then Exit Sub Application.ScreenUpdating = False Select Case LCase(.Value) Case "new instrument" sMsg = "Call Propertry Accounting" Case "new house" sMsg = "Call Inspector" Case "new car" sMsg = "Buy Insurance" Case "robbed" sMsg = "Buy Gun" 'additional messages here Case Else sMsg = "Don't Recognize This" End Select MsgBox sMsg Application.ScreenUpdating = True End With End Sub -- Dave Peterson |
#5
|
|||
|
|||
Perfectomundo!
As I mentioned I don't know the first thing about code, but this is so cool. Regarding #4...I was telling it to look for ways that I may misspell in haste ( hp10 or hp 10). It appears just one "case" will recognize the text no matter how many spaces I may or may not place in there. Is that always the case or did you instruct it to do that? Will it accept a wild card (*)? "Dave Peterson" wrote: Harald will correct me if I steer you wrong, but... #1. .count with the dot in front of it means that it refers back to the previous With statement. In this case "with Target". So your code is checking to see how many cells are changing. If it's more than one, the sub exits. (I like: "if .cells.count 1 then exit sub" just because it makes for easier reading! #2. You can make sure the sMsg is "" if you don't find a reason to change it. Then after you check all the entries, you can see what sMsg is. If it's still "", then don't bother with the MsgBox. #3. You didn't ask! You can stack values inside the Case statement: Case Is = "hp 47", "hp47", "rdc 1961", "rdc1961" might be easier to read and it surely makes for less typing. #4. You didn't ask, again! You check for "hp 47" and "hp47", but what about "hp 47"? Maybe eliminating all the spaces and just checking once would be worth the trouble So this is one way with those suggestions added: Option Explicit Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim sMsg As String With Target If .Column 4 Then Exit Sub If .Count 1 Then Exit Sub '--- changed back to 1 sMsg = "" Select Case Application.Substitute(LCase(.Value), " ", "") Case Is = "hp47", "rdc1961" sMsg = "Copy to Great American!" Case Is = "7311", "7320", "2158", "9679", "9674", _ "9125", "9203", "9540" sMsg = "Copy to Great American!" Case "9785", "9831" sMsg = "Copy to Debra Smith!" Case "hp10" sMsg = "Copy to Carl Detering!" Case "hedlt", "heldt" sMsg = "Copy to Steven Stockseth!" Case "jbs" sMsg = "Copy to Vernon Bates!" Case "avis" sMsg = "Copy to Herbert Morrison!" 'additional messages here' End Select If sMsg = "" Then 'do nothing Else MsgBox sMsg End If End With End Sub ps. If you're using xl2k or higher, you can change this line from: Select Case Application.Substitute(LCase(.Value), " ", "") to: Select Case Replace(LCase(.Value), " ", "") (Replace was added in xl2k.) I also got rid of the application.screenupdating lines. I don't think you need them in this code. They don't hurt, but you're not doing anything where they can help. L Mieth wrote: That did the trick! I needed this years ago! A couple more questions if I may. 1.In the code there is a line beneath the one I changed. "If .count 1 Then exit Sub." What does this line refer to? 2.As it is now I get a reminder to pop up in column four only (good thing). But there is always a reminder. I just need a reminder when certain data is entered. I am copying the code as I have it now. You will see the message I need for each particular entry. Those specifically are the only ones that need a reminder. Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim sMsg As String With Target If .Column 4 Then Exit Sub If .Count 4 Then Exit Sub Application.ScreenUpdating = False Select Case LCase(.Value) Case "hp 47" sMsg = "Copy to Great American!" Case "hp47" sMsg = "Copy to Great American!" Case "rdc 1961" sMsg = "Copy to Great American!" Case "rdc1961" sMsg = "Copy to Great American!" Case "7311" sMsg = "Copy to Great American!" Case "7320" sMsg = "Copy to Great American!" Case "2158" sMsg = "Copy to Great American!" Case "9679" sMsg = "Copy to Great American!" Case "9674" sMsg = "Copy to Great American!" Case "9125" sMsg = "Copy to Great American!" Case "9203" sMsg = "Copy to Great American!" Case "9540" sMsg = "Copy to Great American!" Case "9785" sMsg = "Copy to Debra Smith!" Case "9831" sMsg = "Copy to Debra Smith!" Case "hp 10" sMsg = "Copy to Carl Detering!" Case "hp10" sMsg = "Copy to Carl Detering!" Case "hedlt" sMsg = "Copy to Steven Stockseth!" Case "heldt" sMsg = "Copy to Steven Stockseth!" Case "jbs" sMsg = "Copy to Vernon Bates!" Case "avis" sMsg = "Copy to Herbert Morrison!" 'additional messages here' End Select MsgBox sMsg Application.ScreenUpdating = True End With End Sub Much thanks Linda "Harald Staff" wrote: Hi If .Column 4 Then Exit Sub ( meaning not equal to 4) HTH. Best wishes Harald "L Mieth" skrev i melding ... I have a need for a reminder when particular data is enterd into a cell of a certain column. In a previous posting I found exactly what I need except for one problem and I really know nothing of writing codes. On 3 14-05 Jason Morin replied to a post from red_oceanus on this same matter. The code he presented pertains to the first column. I need the message to work in the fourth column only. I tried to alter the "1" to "4" in his code but then I recieve a pop up message in any of the first four columns instead of only the fourth column. Everything else I tried doesn't work at all. How can I edit this code to suit my needs? Jason's response as follows: Assuming data entry cells are col. A, right-click the worksheet tab, choose "View Code", copy in the code below, and press ALT+Q to close VBE. Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim sMsg As String With Target If .Column 1 Then Exit Sub If .Count 1 Then Exit Sub Application.ScreenUpdating = False Select Case LCase(.Value) Case "new instrument" sMsg = "Call Propertry Accounting" Case "new house" sMsg = "Call Inspector" Case "new car" sMsg = "Buy Insurance" Case "robbed" sMsg = "Buy Gun" 'additional messages here Case Else sMsg = "Don't Recognize This" End Select MsgBox sMsg Application.ScreenUpdating = True End With End Sub -- Dave Peterson |
#6
|
|||
|
|||
Application.Substitute(LCase(.Value), " ", "")
Actually removes those spaces. So it isn't quite as automatic as you thought. There may be ways of doing what you want -- not using a wild card: Case "hp" to "hp99999" There's another statement you can use to check: if lcase(something) like "*hithere*" then There's lots of ways to skin that cat. L Mieth wrote: Perfectomundo! As I mentioned I don't know the first thing about code, but this is so cool. Regarding #4...I was telling it to look for ways that I may misspell in haste ( hp10 or hp 10). It appears just one "case" will recognize the text no matter how many spaces I may or may not place in there. Is that always the case or did you instruct it to do that? Will it accept a wild card (*)? "Dave Peterson" wrote: Harald will correct me if I steer you wrong, but... #1. .count with the dot in front of it means that it refers back to the previous With statement. In this case "with Target". So your code is checking to see how many cells are changing. If it's more than one, the sub exits. (I like: "if .cells.count 1 then exit sub" just because it makes for easier reading! #2. You can make sure the sMsg is "" if you don't find a reason to change it. Then after you check all the entries, you can see what sMsg is. If it's still "", then don't bother with the MsgBox. #3. You didn't ask! You can stack values inside the Case statement: Case Is = "hp 47", "hp47", "rdc 1961", "rdc1961" might be easier to read and it surely makes for less typing. #4. You didn't ask, again! You check for "hp 47" and "hp47", but what about "hp 47"? Maybe eliminating all the spaces and just checking once would be worth the trouble So this is one way with those suggestions added: Option Explicit Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim sMsg As String With Target If .Column 4 Then Exit Sub If .Count 1 Then Exit Sub '--- changed back to 1 sMsg = "" Select Case Application.Substitute(LCase(.Value), " ", "") Case Is = "hp47", "rdc1961" sMsg = "Copy to Great American!" Case Is = "7311", "7320", "2158", "9679", "9674", _ "9125", "9203", "9540" sMsg = "Copy to Great American!" Case "9785", "9831" sMsg = "Copy to Debra Smith!" Case "hp10" sMsg = "Copy to Carl Detering!" Case "hedlt", "heldt" sMsg = "Copy to Steven Stockseth!" Case "jbs" sMsg = "Copy to Vernon Bates!" Case "avis" sMsg = "Copy to Herbert Morrison!" 'additional messages here' End Select If sMsg = "" Then 'do nothing Else MsgBox sMsg End If End With End Sub ps. If you're using xl2k or higher, you can change this line from: Select Case Application.Substitute(LCase(.Value), " ", "") to: Select Case Replace(LCase(.Value), " ", "") (Replace was added in xl2k.) I also got rid of the application.screenupdating lines. I don't think you need them in this code. They don't hurt, but you're not doing anything where they can help. L Mieth wrote: That did the trick! I needed this years ago! A couple more questions if I may. 1.In the code there is a line beneath the one I changed. "If .count 1 Then exit Sub." What does this line refer to? 2.As it is now I get a reminder to pop up in column four only (good thing). But there is always a reminder. I just need a reminder when certain data is entered. I am copying the code as I have it now. You will see the message I need for each particular entry. Those specifically are the only ones that need a reminder. Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim sMsg As String With Target If .Column 4 Then Exit Sub If .Count 4 Then Exit Sub Application.ScreenUpdating = False Select Case LCase(.Value) Case "hp 47" sMsg = "Copy to Great American!" Case "hp47" sMsg = "Copy to Great American!" Case "rdc 1961" sMsg = "Copy to Great American!" Case "rdc1961" sMsg = "Copy to Great American!" Case "7311" sMsg = "Copy to Great American!" Case "7320" sMsg = "Copy to Great American!" Case "2158" sMsg = "Copy to Great American!" Case "9679" sMsg = "Copy to Great American!" Case "9674" sMsg = "Copy to Great American!" Case "9125" sMsg = "Copy to Great American!" Case "9203" sMsg = "Copy to Great American!" Case "9540" sMsg = "Copy to Great American!" Case "9785" sMsg = "Copy to Debra Smith!" Case "9831" sMsg = "Copy to Debra Smith!" Case "hp 10" sMsg = "Copy to Carl Detering!" Case "hp10" sMsg = "Copy to Carl Detering!" Case "hedlt" sMsg = "Copy to Steven Stockseth!" Case "heldt" sMsg = "Copy to Steven Stockseth!" Case "jbs" sMsg = "Copy to Vernon Bates!" Case "avis" sMsg = "Copy to Herbert Morrison!" 'additional messages here' End Select MsgBox sMsg Application.ScreenUpdating = True End With End Sub Much thanks Linda "Harald Staff" wrote: Hi If .Column 4 Then Exit Sub ( meaning not equal to 4) HTH. Best wishes Harald "L Mieth" skrev i melding ... I have a need for a reminder when particular data is enterd into a cell of a certain column. In a previous posting I found exactly what I need except for one problem and I really know nothing of writing codes. On 3 14-05 Jason Morin replied to a post from red_oceanus on this same matter. The code he presented pertains to the first column. I need the message to work in the fourth column only. I tried to alter the "1" to "4" in his code but then I recieve a pop up message in any of the first four columns instead of only the fourth column. Everything else I tried doesn't work at all. How can I edit this code to suit my needs? Jason's response as follows: Assuming data entry cells are col. A, right-click the worksheet tab, choose "View Code", copy in the code below, and press ALT+Q to close VBE. Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim sMsg As String With Target If .Column 1 Then Exit Sub If .Count 1 Then Exit Sub Application.ScreenUpdating = False Select Case LCase(.Value) Case "new instrument" sMsg = "Call Propertry Accounting" Case "new house" sMsg = "Call Inspector" Case "new car" sMsg = "Buy Insurance" Case "robbed" sMsg = "Buy Gun" 'additional messages here Case Else sMsg = "Don't Recognize This" End Select MsgBox sMsg Application.ScreenUpdating = True End With End Sub -- Dave Peterson -- Dave Peterson |
#7
|
|||
|
|||
Thanks so much for everyones help.
This is the first time I have ventured into this user group. "Dave Peterson" wrote: Application.Substitute(LCase(.Value), " ", "") Actually removes those spaces. So it isn't quite as automatic as you thought. There may be ways of doing what you want -- not using a wild card: Case "hp" to "hp99999" There's another statement you can use to check: if lcase(something) like "*hithere*" then There's lots of ways to skin that cat. L Mieth wrote: Perfectomundo! As I mentioned I don't know the first thing about code, but this is so cool. Regarding #4...I was telling it to look for ways that I may misspell in haste ( hp10 or hp 10). It appears just one "case" will recognize the text no matter how many spaces I may or may not place in there. Is that always the case or did you instruct it to do that? Will it accept a wild card (*)? "Dave Peterson" wrote: Harald will correct me if I steer you wrong, but... #1. .count with the dot in front of it means that it refers back to the previous With statement. In this case "with Target". So your code is checking to see how many cells are changing. If it's more than one, the sub exits. (I like: "if .cells.count 1 then exit sub" just because it makes for easier reading! #2. You can make sure the sMsg is "" if you don't find a reason to change it. Then after you check all the entries, you can see what sMsg is. If it's still "", then don't bother with the MsgBox. #3. You didn't ask! You can stack values inside the Case statement: Case Is = "hp 47", "hp47", "rdc 1961", "rdc1961" might be easier to read and it surely makes for less typing. #4. You didn't ask, again! You check for "hp 47" and "hp47", but what about "hp 47"? Maybe eliminating all the spaces and just checking once would be worth the trouble So this is one way with those suggestions added: Option Explicit Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim sMsg As String With Target If .Column 4 Then Exit Sub If .Count 1 Then Exit Sub '--- changed back to 1 sMsg = "" Select Case Application.Substitute(LCase(.Value), " ", "") Case Is = "hp47", "rdc1961" sMsg = "Copy to Great American!" Case Is = "7311", "7320", "2158", "9679", "9674", _ "9125", "9203", "9540" sMsg = "Copy to Great American!" Case "9785", "9831" sMsg = "Copy to Debra Smith!" Case "hp10" sMsg = "Copy to Carl Detering!" Case "hedlt", "heldt" sMsg = "Copy to Steven Stockseth!" Case "jbs" sMsg = "Copy to Vernon Bates!" Case "avis" sMsg = "Copy to Herbert Morrison!" 'additional messages here' End Select If sMsg = "" Then 'do nothing Else MsgBox sMsg End If End With End Sub ps. If you're using xl2k or higher, you can change this line from: Select Case Application.Substitute(LCase(.Value), " ", "") to: Select Case Replace(LCase(.Value), " ", "") (Replace was added in xl2k.) I also got rid of the application.screenupdating lines. I don't think you need them in this code. They don't hurt, but you're not doing anything where they can help. L Mieth wrote: That did the trick! I needed this years ago! A couple more questions if I may. 1.In the code there is a line beneath the one I changed. "If .count 1 Then exit Sub." What does this line refer to? 2.As it is now I get a reminder to pop up in column four only (good thing). But there is always a reminder. I just need a reminder when certain data is entered. I am copying the code as I have it now. You will see the message I need for each particular entry. Those specifically are the only ones that need a reminder. Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim sMsg As String With Target If .Column 4 Then Exit Sub If .Count 4 Then Exit Sub Application.ScreenUpdating = False Select Case LCase(.Value) Case "hp 47" sMsg = "Copy to Great American!" Case "hp47" sMsg = "Copy to Great American!" Case "rdc 1961" sMsg = "Copy to Great American!" Case "rdc1961" sMsg = "Copy to Great American!" Case "7311" sMsg = "Copy to Great American!" Case "7320" sMsg = "Copy to Great American!" Case "2158" sMsg = "Copy to Great American!" Case "9679" sMsg = "Copy to Great American!" Case "9674" sMsg = "Copy to Great American!" Case "9125" sMsg = "Copy to Great American!" Case "9203" sMsg = "Copy to Great American!" Case "9540" sMsg = "Copy to Great American!" Case "9785" sMsg = "Copy to Debra Smith!" Case "9831" sMsg = "Copy to Debra Smith!" Case "hp 10" sMsg = "Copy to Carl Detering!" Case "hp10" sMsg = "Copy to Carl Detering!" Case "hedlt" sMsg = "Copy to Steven Stockseth!" Case "heldt" sMsg = "Copy to Steven Stockseth!" Case "jbs" sMsg = "Copy to Vernon Bates!" Case "avis" sMsg = "Copy to Herbert Morrison!" 'additional messages here' End Select MsgBox sMsg Application.ScreenUpdating = True End With End Sub Much thanks Linda "Harald Staff" wrote: Hi If .Column 4 Then Exit Sub ( meaning not equal to 4) HTH. Best wishes Harald "L Mieth" skrev i melding ... I have a need for a reminder when particular data is enterd into a cell of a certain column. In a previous posting I found exactly what I need except for one problem and I really know nothing of writing codes. On 3 14-05 Jason Morin replied to a post from red_oceanus on this same matter. The code he presented pertains to the first column. I need the message to work in the fourth column only. I tried to alter the "1" to "4" in his code but then I recieve a pop up message in any of the first four columns instead of only the fourth column. Everything else I tried doesn't work at all. How can I edit this code to suit my needs? Jason's response as follows: Assuming data entry cells are col. A, right-click the worksheet tab, choose "View Code", copy in the code below, and press ALT+Q to close VBE. Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim sMsg As String With Target If .Column 1 Then Exit Sub If .Count 1 Then Exit Sub Application.ScreenUpdating = False Select Case LCase(.Value) Case "new instrument" sMsg = "Call Propertry Accounting" Case "new house" sMsg = "Call Inspector" Case "new car" sMsg = "Buy Insurance" Case "robbed" sMsg = "Buy Gun" 'additional messages here Case Else sMsg = "Don't Recognize This" End Select MsgBox sMsg Application.ScreenUpdating = True End With End Sub -- Dave Peterson -- Dave Peterson |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
How to group similar column titles together???? | vrk1 | General Discussion | 2 | April 30th, 2005 12:17 AM |
Table Design | A. Williams | Database Design | 3 | April 29th, 2005 07:02 PM |
up to 7 functions? | ALex | Worksheet Functions | 10 | April 12th, 2005 06:42 PM |
How can I sort an entire spreadsheet from a list | prod sorter | Worksheet Functions | 4 | November 17th, 2004 03:43 AM |
Macro Fill Down - filling too much of the column | Greegan | Worksheet Functions | 1 | September 20th, 2004 02:48 AM |