CODEWITHSUNDEEP
excelvba
soundship
soundship

Reputation: 33

Run Macro when cell result changes by formula

What I am needing: A macro to be triggered, let's call the macro "MacroRuns", whenever cell C3 returns a different value than it currently has, based on its FORMULA, NOT based on manually typing a different value.

I have spent all day reading through and attempting every "solution" on the first two pages of my google search on this topic. So far, nothing seems to work for me. Please help!!! I would very much appreciate it!

Example:

I have now tried this but it corrupts my file after it works a few times. 

Private Sub Worksheet_Calculate()
    If Range("E3") <> Range("C3").Value Then
        Range("E3") = Range("B3").Value
        MsgBox "Successful"
    End If
End Sub

Upvotes: 2

Views: 13972

Answers (3)

Ferdinando
Ferdinando

Reputation: 964

If i understood your question you can try this code:

1)Right-click the Sheet tab and then click View Code

  1. copy this code:

    Private Sub Worksheet_Change(ByVal Target As Range)

    Dim Value1 As Variant Static Value2 As Variant

    Value1 = Range("C3").value

    If Value1 <> Value2 Then MsgBox "Cell has changed." End If

    Value2 = Range("C3").value

    End Sub

i tried this one:

in cell C3 i have wrote =SUM(A1:B1) when i try to change value in this cells also C3 change and i get the msgBox

Hope this helps

EDIT the code to answer @ MD Ismail Hosen

if i understood your problem you can try this example code:

Private Sub Worksheet_Change(ByVal Target As Range)

'in this code i have used two range on the same row, but you can change as 
'you want. 
'In my case, the range that i check is Range("A1:C1") and the RANGE that i 'save old value is 
'RANGE("F1:H1") F1 is the sixth column.

Dim counter As Byte
Dim sizeRange As Byte

sizeRange = 3 ' my size range

For counter = 1 To sizeRange
    'on the left i check Range("A1:C1").On the right i check The Range("F1:H1")
    If Cells(1, counter) <> Cells(1, counter + 5) Then 'counter start from 1 
        MsgBox "Range Changed"
        Range("A1:C1").Copy Destination:=Range("F1:H1") ' use other code to copy the range
        Exit For
    End If
Next counter
End Sub

If you have a formula in your range ("A1:C1") you have to use this code to copy the new range value A1:C1 in F1:H1 else you get the error(loop the macro). 'TO use this code if you have formula in the cells.

 Range("A1:C1").Select
        Selection.Copy
        Range("F1").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

Hope this helps.

Upvotes: 0

user11749857
user11749857

Reputation: 1

Right. I have a nugget to add in here, something that completely frustrated me upon trying Ferdinando's code (which by itself is very neat, thank you, Ferdinando!!)

The main point is - if you are going to be using anything beyond just a messagebox (MsgBox "Cell has changed.") you need to add the following lines above AND below this line(otherwise the Excel will simply crash constantly due to endlessly trying to do the same). Don't ask me why this is, but I finally-finally solved my problem with this. So here are the lines:

If Value1 <> Value2 Then
(ADD THIS:)     Application.EnableEvents = False
                MsgBox "Cell has changed."
(I call a macro running a query from MySQL instead of MsgBox)
(AND ADD THIS:) Application.EnableEvents = True

Hope this helps anyone in the situation I was in!!

Upvotes: 0

VBasic2008
VBasic2008

Reputation: 54807

Module1, Sheet1 (Calculate), ThisWorkbook (Open)

Highlights

  • When the workbook opens, the value from C3 is read into the public variable TargetValue via TargetStart.
  • When the value in C3 is being calculated, TargetCalc is activated via the calculate event.If the current value in C3 is different than TargetValue, MacroRuns is triggered and TargetValue is updated with the value in C3.

The Code

Module1

Option Explicit

Public TargetValue As Variant
Private Const cTarget As String = "C3"

Sub TargetCalc(ws as Worksheet)
    If ws.Range(cTarget) <> TargetValue Then
        MacroRuns
        TargetValue = ws.Range(cTarget).Value
    End If
End Sub

Sub TargetStart()
    TargetValue = Sheet1.Range(cTarget).Value
End Sub

Sub MacroRuns()
    MsgBox "MacroRuns"
End Sub

ThisWorkbook

Option Explicit

Private Sub Workbook_Open()
    TargetStart
End Sub

Sheet1

Option Explicit

Private Sub Worksheet_Calculate()
    TargetCalc Me
End Sub

Upvotes: 5

Related Questions