Run Macro Automatically When Field Value Changes

[pbd]

I have a Macro that saves the excel field data to a .csv whenever I run the Macro. (see below)

The Macro does exactly what I need it to, however, I would like the Macro to run automatically every time the value in a specific field changes.

** I found this Macro online, I do not write code or know anything about VBA. Please "dumb-down" the explanation as simple as possible.

 Public Sub CharacterSV()
        Const DELIMITER As String = ","
        Dim myRecord As Range
        Dim myField As Range
        Dim nFileNum As Long
        Dim sOut As String
        
        nFileNum = FreeFile
        Open "text.csv" For Output As #nFileNum
        For Each myRecord In Range("A1:A" & _
                    Range("A" & Rows.Count).End(xlUp).Row)
            With myRecord
                For Each myField In Range(.Cells, _
                        Cells(.Row, Columns.Count).End(xlToLeft))
                    sOut = sOut & DELIMITER & myField.Text
                Next myField
                Print #nFileNum, Mid(sOut, 2)
                sOut = Empty
            End With
        Next myRecord
        Close #nFileNum
    End Sub

 

[Smitty]

Welcome to the Board!

It can be done easily with either a change or calculate event, but we need some more information:

1) How is the value changing (manually or via formula)?
2) What's the specific range in question?

 

[pbd]

Thanks much!

The value changes via formula, and the range is any cell in F1:G20

Thanks again.

 

[Smitty]

See if this does what you want:

<font face=Calibri><SPAN style="color:#00007F">Public</SPAN> CurrentValue <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Double</SPAN><br><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Activate()<br>    CurrentValue = Application.WorksheetFunction.Sum(ActiveSheet.Range("F1:G20"))<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Calculate()<br>    <SPAN style="color:#00007F">If</SPAN> Application.WorksheetFunction.Sum(Range("F1:G20")) <> CurrentValue <SPAN style="color:#00007F">Then</SPAN> CharacterSV<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

It's essentially summing that range when the sheet is activated, and any change that causes the sum to be different will trigger the code.

HTH,

 

[pbd]

I put this into the sheet:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("F2:G10")) Is Nothing Then
        CharacterSV
    End If

End Sub

and put the original macro [CharacterSV] into a module and it works... sort of

The issue that remains is that the data in the field is linked from another workbook, and the macro does not update when the value updates, only when I manually change the cell value.

Any thoughts?

Will your code address that?

 

[Smitty]

Will your code address that?

It should. A Change event will only respond to a manual change on a sheet. To trigger a change from a formula you need to use a Calculate event.