Hi

Please help:

I have a worksheet with a formula the changes a cell(J24) to either
"ORDER" or "OK" ...i have used vb script with
modifications:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Range("J24"), Target) Is Nothing Then
If Target.Value = "ORDER" Then
Mail_with_outlook
End If
End If
End Sub

all works fine if i type ORDER into the cell(J24)..the e-mail is
generated...but when the formula generates the result ORDER based on
the formula...no e-mail is generated.

I suspect this does not work beacuse of the Change event....but i
don't know what else to use...as i am new to vb.

Thanks For your time

You could use the Worksheet_Calculate event- somethin g
like this:

Private Sub Worksheet_Calculate()
If Range("J24").Value = "ORDER" Then Mail_with_outlook
End Sub

The problem with this is that it will send the mail every
time the worksheet calculates- for example, if you enter a
formula in another cell.

To get round this, you could have a variable that
remembers if a mail has already been sent- like this:

Dim blnMailSent as Boolean

Private Sub Worksheet_Calculate()
If Range("J24").Value = "ORDER" And _
blnMailSent = False Then
Mail_with_outlook
blnMailSent = True
End If
End Sub

Obviously, this variable will only hold its value as long
as the workworrk is open. Next time it is openned another
mail would be sent.

Cheers,
Dave.
-----Original Message-----
Hi

Please help:

I have a worksheet with a formula the changes a cell(J24)
to either
"ORDER" or "OK" ...i have used vb
script with
modifications:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Range("J24"), Target) Is
Nothing Then
If Target.Value = "ORDER" Then
Mail_with_outlook
End If
End If
End Sub

all works fine if i type ORDER into the cell(J24)..the e-
mail is
generated...but when the formula generates the result
ORDER based on
the formula...no e-mail is generated.

I suspect this does not work beacuse of the Change
event....but i
don't know what else to use...as i am new to vb.

Thanks For your time
.

"Dave Ramage" wrote in message ...
You could use the Worksheet_Calculate event- somethin g
like this:

Private Sub Worksheet_Calculate()
If Range("J24").Value = "ORDER" Then Mail_with_outlook
End Sub

The problem with this is that it will send the mail every
time the worksheet calculates- for example, if you enter a
formula in another cell.

To get round this, you could have a variable that
remembers if a mail has already been sent- like this:

Dim blnMailSent as Boolean

Private Sub Worksheet_Calculate()
If Range("J24").Value = "ORDER" And _
blnMailSent = False Then
Mail_with_outlook
blnMailSent = True
End If
End Sub

Obviously, this variable will only hold its value as long
as the workworrk is open. Next time it is openned another
mail would be sent.

Cheers,
Dave.
-----Original Message-----
Hi

Please help:

I have a worksheet with a formula the changes a cell(J24)
to either
"ORDER" or "OK" ...i have used vb
script with
modifications:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Range("J24"), Target) Is
Nothing Then
If Target.Value = "ORDER" Then
Mail_with_outlook
End If
End If
End Sub

all works fine if i type ORDER into the cell(J24)..the e-
mail is
generated...but when the formula generates the result
ORDER based on
the formula...no e-mail is generated.

I suspect this does not work beacuse of the Change
event....but i
don't know what else to use...as i am new to vb.

Thanks For your time
.



Hi

Thanks Dave..it works...but just one more question .how do i make this
work on a range of cells:
Dim blnMailSent as Boolean

Private Sub Worksheet_Calculate()
If Range("J24:J30").Value = "ORDER" And _
blnMailSent = False Then
Mail_with_outlook
blnMailSent = True
End If
End Sub

If i put the above("J24:J30") i get a error.

Thanks again

vee...

Try this...

Private Sub Worksheet_Calculate()
Dim rngR as Range

For Each rngR in Range("J24:J30")
If rngR.Value = "ORDER" And blnMailSent = False Then
Mail_with_outlook
blnMailSent = True
End If
Next rngR
End Sub

This structure loops through each cell in the range. In
each loop, rngR represents the cell.

Cheers,
Dave.

-----Original Message-----
"Dave Ramage" wrote in message
...
You could use the Worksheet_Calculate event- somethin g
like this:

Private Sub Worksheet_Calculate()
If Range("J24").Value = "ORDER" Then Mail_with_outlook
End Sub

The problem with this is that it will send the mail
every
time the worksheet calculates- for example, if you
enter a
formula in another cell.

To get round this, you could have a variable that
remembers if a mail has already been sent- like this:

Dim blnMailSent as Boolean

Private Sub Worksheet_Calculate()
If Range("J24").Value = "ORDER" And _
blnMailSent = False Then
Mail_with_outlook
blnMailSent = True
End If
End Sub

Obviously, this variable will only hold its value as
long
as the workworrk is open. Next time it is openned
another
mail would be sent.

Cheers,
Dave.
-----Original Message-----
Hi

Please help:

I have a worksheet with a formula the changes a cell
(J24)
to either
"ORDER" or "OK" ...i have used
vb
script with
modifications:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Range("J24"), Target) Is
Nothing Then
If Target.Value = "ORDER" Then
Mail_with_outlook
End If
End If
End Sub

all works fine if i type ORDER into the cell(J24)..the
e-
mail is
generated...but when the formula generates the result
ORDER based on
the formula...no e-mail is generated.

I suspect this does not work beacuse of the Change
event....but i
don't know what else to use...as i am new to vb.

Thanks For your time
.



Hi

Thanks Dave..it works...but just one more question .how
do i make this
work on a range of cells:
Dim blnMailSent as Boolean

Private Sub Worksheet_Calculate()
If Range("J24:J30").Value = "ORDER" And _
blnMailSent = False Then
Mail_with_outlook
blnMailSent = True
End If
End Sub

If i put the above("J24:J30") i get a error.

Thanks again
.