VBA to read cell value rather than the cell formula

[austicks84]

I would like a message box to pop up when a cell in a range changes. The cells in the range all have IF formulas in them. My problem is that the code i am using seems to only read the formula, which doesn't change, hence nothing happens. How can i get my code to read the cell value, which does change, rather than the formula which doesn't?

Private Sub Worksheet_Change(ByVal Target As Range)
If Not (Application.Intersect(Range("ED45:EO83"), Target) Is Nothing) Then
MsgBox "Cell " & Target.Address & " has changed.", vbInformation, "Kutools for Excel"
End If
End Sub

 

[Brombrough]

Hi,

Instead of cell use cell.value

Hope it helps

 

[pgc01]

Hi

How does the cell value change?
The event procedure is only fired when the cell is written.

Please elaborate.

 

[dmt32]

Hi
To detect formula changes, use the Worksheet_Calculate event

dave

 

[pgc01]

To detect formula changes, use the Worksheet_Calculate event

Hi Dave

The Worksheet_Calculate event will run when any calculation is performed. You'd then have to figure out what exactly happened.
The Worksheet_Change event will tell you which cell was modified which seems to be in this case more efficient and direct.

 

[dmt32]

The Worksheet_Calculate event will run when any calculation is performed. You'd then have to figure out what exactly happened.
The Worksheet_Change event will tell you which cell was modified which seems to be in this case more efficient and direct.

thanks but I am fully aware event does not have Target Parameter - there are workarounds using Calculate event - just waiting for OPs response

Dave

 

[austicks84]

Do you mean like in the below?

Private Sub Worksheet_Change(ByVal Target As Range)
If Not (Application.Intersect(Range("ED45:EO83"), Target) Is Nothing) Then
MsgBox "Cell.value " & Target.Address & " has changed.", vbInformation, "Kutools for Excel"
End If
End Sub

 

[austicks84]

This is what's in the target cell:

=VALUE(IF(COUNTIF($P$3:$P$42,R7), 1,2))

 

[austicks84]

Do i just change the below?:

Private Sub Worksheet_Calculate(ByVal Target As Range)
If Not (Application.Intersect(Range("ED45:EO83"), Target) Is Nothing) Then
MsgBox "Cell " & Target.Address & " has changed.", vbInformation, "Kutools for Excel"
End If
End Sub

 

[dmt32]

Do i just change the below?:

Private Sub Worksheet_Calculate(ByVal Target As Range)
If Not (Application.Intersect(Range("ED45:EO83"), Target) Is Nothing) Then
MsgBox "Cell " & Target.Address & " has changed.", vbInformation, "Kutools for Excel"
End If
End Sub

Worksheet_Calculate event has no parameters & you cannot (as far as I am aware) modify then in manner shown.

To detect which cell in your range has changed using this event, a workaround is to use the Range.ID property to store the cell value which you use to compare changes against

try following

Private Sub Worksheet_Calculate()
    Dim rng As Range, cell As Range
    Dim CellHasChanged As Boolean
    
    Set rng = Me.Range("ED45:EO83")
    For Each cell In rng.Cells
        With cell
           CellHasChanged = CBool(.ID <> .Value)
           If CellHasChanged Then
            .ID = .Value
            MsgBox "Cell " & cell.Address & " has changed.", vbInformation, "Cell Value Changed"
            Exit For
           End If
        End With
    Next cell
    Set rng = Nothing
End Sub

A slight limitation of this approach is that the values stored in ID property are lost when workbook closed but hopefully, will not be an issue for you.


Dave