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
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

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.
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,297
Messages
5,510,461
Members
408,791
Latest member
bwirth

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top