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

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

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

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

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

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