Run Macro Automatically When Field Value Changes

pbd

New Member
Joined
Jul 26, 2010
Messages
3
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.
Code:
 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
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
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

New Member
Joined
Jul 26, 2010
Messages
3
Thanks much!

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

Thanks again.
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
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

New Member
Joined
Jul 26, 2010
Messages
3
I put this into the sheet:

Code:
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

Legend
Joined
May 15, 2003
Messages
29,536
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,133,573
Messages
5,659,606
Members
418,514
Latest member
radonwilson

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top