CODEWITHSUNDEEP
vbaexcel
DLittlejohn412
DLittlejohn412

Reputation: 3

Macro does not automatically run when cell value changes

I would like to run a macro when a value in cell F1 is updated by the calculation =DAYS360(B2,B1)

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$F$1" Then
    MsgBox "You changed THE CELL!"
End If
End Sub

There are several topics on this, and none of the solutions for them are working for me.. I am baffled. If I manually change the value in cell F1, the macro runs. However, when I add a new value in the date column that causes the calculation in cell F1 to update, nothing happens.

Some of the other topics offer solutions using the intersect function, however, those are giving me the same results.

Upvotes: 0

Views: 8616

Answers (1)

user2140261
user2140261

Reputation: 8003

You could change your Change Event to look for the cells that F1 is looking at. 

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$1" Or Target.Address = "$B$2" Then
    MsgBox "You changed THE CELL!"
End If
End Sub

This makes perfect sense because these are actually the cells that you are changing, i caught this from your line when I add a new value in the date column that causes the calculation in cell F1 to update

Alternativly if your formula references will change you can use the below code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range
For Each r In Range("F1").Precedents
    If Target.Address = r.Address Then
        MsgBox "You changed THE CELL!"
        Exit For
    End If
Next r
End Sub

What the above code will do is whenever any cell in the worksheet is changed, it will goto cell F1, then look to see if the cell that was changed was one of the cells the F1 relies on for its calculation, if it wasn't it does nothing, if it was, than it will fire the msgbox.

UPDATE: Another option that will work across all the sheets to see if any cell changed is relied on by the formula inside the Cell F1 on Sheet1 when the cells are on another page can be found below. 

Public Sub Workbook_SheetChange(ByVal sh As Object, ByVal Target As Range)
On Error GoTo NextStep
Dim r As Range
For Each r In Sheets("SheetF1IsOn").Range("F1").Precedents
    If Target.Address = r.Address Then
        MsgBox "You changed THE CELL!"
        Exit For
        Exit Sub
    End If
Next r

NextStep:
If InStr(Replace(Sheets("SheetF1IsOn").Range("F1").Formula, "'", ""), sh.Name + "!" + Target.Address) _
   Or InStr(Replace(Sheets("SheetF1IsOn").Range("F1").Formula, "'", ""), sh.Name + "!" + Target.Address(False)) _
   Or InStr(Replace(Sheets("SheetF1IsOn").Range("F1").Formula, "'", ""), sh.Name + "!" + Target.Address(, False)) _
   Or InStr(Replace(Sheets("SheetF1IsOn").Range("F1").Formula, "'", ""), sh.Name + "!" + Target.Address(False, False)) > 0 Then
        MsgBox "You changed THE CELL!"
End If
End Sub

If it needs to work both on other sheets and the current sheet, then you will have to add another if test for target in the formula then see if it is preceded by the name of a sheet in the workbook and a !

UPDATE2: Both the above are slightly flawed (as Simoco has pointed out) the last thing I can come up with is to use a public variable, to set the value of your cell and every time the Work Sheet is calculated to test if the value changed. (if doing a whole column you may need to Dim a collection or array to test against)

So inside you worksheet module, replace your original code with the following: 

Option Explicit
Public F1ValueOld As Variant

Private Sub Worksheet_Calculate()
    If Range("F1").Value <> F1ValueOld Then
        MsgBox "You changed THE CELL!"
        F1ValueOld = Range("F1").Value
    End If
End Sub

This will work while the workbook is open, but you will get an error when you close and reopen the workbook, so inside the Workbook module add the following code also: 

Private Sub Workbook_Open()
    F1ValueOld = Sheets("SheetF1IsOn").Range("A1").Value
End Sub

This should no accomplish what you are attempting.

Upvotes: 1

Related Questions