After each row is updated run a macro if cell changed

[waubain]

I am running a macro from Attachmate Reflections application that loops through and reads a row from Excel processes the information and then writes back to Excel. If the process was successful it writes back to Excel "CREATED" in the active row Column D and if unsuccessful writes "NOT CREATED".

Currenty when completed, I then click a cmdButton which prints a templated letter in Word if the process was successful(CREATED). This macro works, but now I would like to remove the command button and have it generate the letter as each row is successfully completed. I have read about the function Worksheet_Change(), and I can usually get macros to work if I see an example, but have not found anything quite like I am trying to do. I am having a hard time wrapping my brain around how to declare the function variable since the range has to be available to all functions and how to nest the If ElseIF statement into my current code.

I need to keep this code within Excel and cannot change the macro in Reflections.

Option Explicit

'change this to where your files are stored
Const FilePath As String = "E:\Work Projects\Conversion\Old\"

Dim wd As New Word.Application
Dim PersonCell As Range

Private Sub cmdLetters_Click()

Dim doc As Word.Document
Dim PersonRange As Range
Dim PrintedRx As Integer

wd.Visible = True
PrintedRx = 0
            
    Range("A2").Select
    Set PersonRange = Range(ActiveCell, ActiveCell.End(xlDown))

        For Each PersonCell In PersonRange  'for each person in list ...
        
            If PersonCell.Offset(0, 3) = "RX CREATED" And PersonCell.Offset(0, 4) = 0 Then
    
                Set doc = wd.Documents.Open(FilePath & "FormLetter.dotx") 'open a document in Word form letter
        
                'go to each bookmark and type in details
                CopyCell "FirstName", 1
                CopyCell "LastName", 2
                                    
                'doc.PrintOut 'doc.PrintOut prints and closes document without saving
                doc.PrintPreview 'doc.PrintPreview previews and then closes without saving
                doc.Close SaveChanges:=False
                Set doc = Nothing
                PersonCell.Offset(0, 4) = 1
                PrintedRx = 1
            End If
               
       Next PersonCell
       If PrintedRx = 0 Then
            MsgBox "No Rx's to print", 64
       End If
wd.Quit
Set wd = Nothing

End Sub


Sub CopyCell(BookMarkName As String, ColumnOffset As Integer)

    'copy each cell to relevant Word bookmark
        wd.Selection.GoTo What:=wdGoToBookmark, Name:=BookMarkName
        wd.Selection.TypeText PersonCell.Offset(0, ColumnOffset).Value

End Sub

Here is an example of use of Worksheet_Change() I found.

Private Sub Worksheet_Change(ByVal Target As Range)
 If Intersect(Target, Range("C5")) Is Nothing Then
  Exit Sub
 Else
  'The cell you are monitoring has changed!
  'Do whatever you need to do...
 End If
End Sub

Any suggestions would be appreciated. Thank you.

You don't know what you don't know...

 

[UnsolvedCoding]

This sounds like you may have a simple solution. Try to simply set a variable to collect the cell your after if it matches what you want run the macro, if not don't.

Example -

Dim Test as string

Test = Trim(Range("A5").value)

' Other code goes here

If Test <> Trim(Range("A5").value) then Macro_Name

 

[waubain]

UnsolvedCoding wrote-

This sounds like you may have a simple solution. Try to simply set a variable to collect the cell your after if it matches what you want run the macro, if not don't.

Example -

Dim Test as string

Test = Trim(Range("A5").value)

' Other code goes here

If Test <> Trim(Range("A5").value) then Macro_Name

Maybe I am missing something, but my cell of interest is not fixed. My current Excel sheet has 1100 rows. Each row is process one at a time and the column that gets written back to is (CurrentRow, "D"). So "CREATED" could be in anyrow between 2 and 1100....Waubain

You don't know what you don't know...