Run script on cell change

Question

Hello,

I have a script that I would like to have run when the value in column F changes (or is entered).  The script should look at the row's column A, and send an email from Lotus Notes based on that entry. 

I would like to get the script to run when I enter a value in column F - I know I need some sort of like, Worksheet_change(ByVal target as range) or something....

Thanks for any ideas or help!

PS: I know this forum isn't really Lotus Notes focused, but if someone knows how to script for it, I have another question (how do I clear the body field, so my Body text from this macro doesn't appear after my email signature?)

=========

Sub LotusNotsSendActiveWorkbook(ByVal Target As Range)

'Send an e-mail & attachment using Lotus Note(s)

'Original Code by Nate Oliver (NateO)

'Declare Variables for file and macro setup

Dim UserName As String, MailDbName As String, ccRecipient As String, attachment1 As String, Recipient As String

Dim Maildb As Object, MailDoc As Object, AttachME As Object, Session As Object

Dim EmbedObj1 As Object

Dim row As Long

Dim email As String

Dim BodyText As String

Dim Btext As String

Dim objNotesDocument As Object

row = ActiveCell.row

With Application

.ScreenUpdating = False

.DisplayAlerts = False

' Open and locate current LOTUS NOTES User

Set Session = CreateObject("Notes.NotesSession")

UserName = Session.UserName

MailDbName = _

Left$(UserName, 1) & Right$(UserName, (Len(UserName) - InStr(1, UserName, " "))) & ".nsf"

Set Maildb = Session.GETDATABASE("", MailDbName)

If Maildb.IsOpen = True Then

Else

Maildb.OPENMAIL

End If

'ActiveCell.Offset(0, ActiveCell.row).Select

Cells(ActiveCell.row, "A").Select

If ActiveCell = "COMPANY" Then

    Recipient = "@email.com," & " " & "@email.com"

ElseIf ActiveCell = "COMPANY1" Then Recipient = "@email.com,," & " " & "@email.com,," & " " & "******@email.com,"

ElseIf ActiveCell = "COMPANY2" Then Recipient = "@email.com,," & " " & "@email.com,"

ElseIf ActiveCell = "COMPANY3" Then Recipient = "@email.com,," & " " & "@email.com,"

ElseIf ActiveCell = "COMPANY4" Then Recipient = "******@email.com,,"

ElseIf ActiveCell = "COMPANY5" Then Recipient = "@email.com,," & " " & "@email.com,"

ElseIf ActiveCell = "COMPANY6" Then Recipient = "@email.com,," & " " & "@email.com,," & " " & "******@email.com,"

Else

email = InputBox("Email not found on file, please enter email address(es)", "HALT!")

    Recipient = email

End If

' Create New Mail and Address Title Handlers

Set MailDoc = Maildb.CreateDocument

MailDoc.Form = "Memo"

' Select range of e-mail addresses

'Recipient = Sheets("EmailSheet").Range("B2").Value

ccRecipient = "@email.com," & " " & "@email.com," & " " & "******@email.com"

' Or send to a single address

' Recipient = "******@isp.com"

MailDoc.sendTo = Recipient

MailDoc.CopyTo = ccRecipient

' Subject & Body stored in a**worksheet

MailDoc.Subject = Sheets("Sheet3").Range("B" & row).Value & " invoice set up"

'Btext = "Dear man" & vbNewLine & "hello"

'MailDoc.Body = Sheets("EmailSheet").Range("ENTER CELL OF BODY").Value

' These can be entered here manually instead

' MailDoc.Subject = "Check this out!"

' MailDoc.Body = BodyText

MailDoc.body = "Hello there" & vbNewLine & "how are you"

'Displays email message without sending; user needs to click Send

Set workspace = CreateObject("Notes.NotesUIWorkspace")

Call workspace.EDITDOCUMENT(True, MailDoc).GOTOFIELD("Body")

Set Maildb = Nothing

Set MailDoc = Nothing

Set AttachME = Nothing

Set Session = Nothing

Set EmbedObj1 = Nothing

.ScreenUpdating = True

.DisplayAlerts = True

errorhandler1:

Set Maildb = Nothing

Set MailDoc = Nothing

Set AttachME = Nothing

Set Session = Nothing

Set EmbedObj1 = Nothing

End With

End If

End Sub

Answer 1

Ah! That seems to launch the script, with one issue:

When I hit "Enter" to fill in the cell in column F, the active cell now becomes a row below the one with all my information.  How can I tell it to, when hitting Enter, go back up to the same row as the data and then run the script...

If that's confusing, hopefully this will help - this is how I have my row set up:

	A	B	C	D	E	F
1
2	MICROSOFT	Office	$50.51		supplies	1548
3
4

Basically, Column A is who is being paid (Microsoft), B C E for notes, D is the amount...now, in F I have "1548" which is the check number.

In F2, when I type "1548" and hit ENTER, my script runs, but the script is looking for info in A3, not A2 (which is the same row as the check number).  My script should go to A2, see "Microsoft", then create an email to them based on what my script says.

How can I get excel to look back up to the correct line?

I have tried adding:

Target.Offset(-1, 0).Select    

and

 Cells.offset(-1,0).Select

after

If Target.column = 6 Then

    Application.EnableEvents = False

    AVal = Target.EntireRow.Cells(1, "A").Value

but that didn't do anything.  Do you have any ideas?

Thank you so much, and if you would like some better clarification, please let me

Answer 2

You can use code in the worksheet's code module to detect the change in column F, get the corresponding value from column A, and then call your Lotus stuff. I don't know anything about Lotus Notes (I guess I did just learn that it is still alive -- news to me), so I can't address anything in your existing code.

In the code module for the appropriate worksheet, use

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim AVal As Variant

    On Error GoTo EndSub

    If Target.Column = 6 Then  ' Column F

        Application.EnableEvents = False

        ' get the value from column A

        AVal = Target.EntireRow.Cells(1, "A").Value

        ' call your lotus notes stuff here

    End If

EndSub:

    Application.EnableEvents = True

End Sub