VBA to read cell value rather than the cell formula

austicks84

New Member
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
 

pgc01

MrExcel MVP
Hi

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

Please elaborate.
 

pgc01

MrExcel MVP
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

Well-known Member
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
 
Last edited:

austicks84

New Member
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

New Member
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

Well-known Member
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

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

Some videos you may like

This Week's Hot Topics

Top