CODEWITHSUNDEEP
excelvba
Wietse
Wietse

Reputation: 11

Show notification when cell value/outcome changes

I am trying to write a VBA code such that a Message box pops up whenever the value inside certain cells change.

The cells that I want to monitor change because they are linked to a query which is refreshed automatically. The VBA codes I found online only work when the cell value is changed manually, this will not work because the formula in the cells do not change, only the displayed value changes.

Can anyone help me with this?

Upvotes: 1

Views: 725

Answers (1)

VBasic2008
VBasic2008

Reputation: 54853

Application Calculate (Public Variables)

  • The following is written for a non-contiguous range.
  • A message box pop's up each time a value in the range changes via formula.
  • Copy the codes into the appropriate modules.
  • Adjust the values in the constants section.
  • This is an automated solution. To start you should either save, close, and reopen the workbook or run the popupMsgBoxInit procedure.

Standard Module e.g. Module1

Option Explicit

Public Const popupWsName As String = "Sheet1"
Public Const popupRgAddress As String = "A1,C3,E5"
Public popupRg As Range
Public popupCount As Long
Public popupArr As Variant

Sub popupMsgBoxInit()
    Set popupRg = ThisWorkbook.Worksheets(popupWsName).Range(popupRgAddress)
    popupRg.Interior.Color = 65535 'xlNone
    popupCount = popupRg.Cells.Count
    ReDim popupArr(1 To popupCount)
    Dim cel As Range
    Dim i As Long
    For Each cel In popupRg.Cells
        i = i + 1
        popupArr(i) = cel.Value
    Next cel
End Sub

Sub popupMsgBox()
    Dim chCount As Long
    Dim cel As Range
    Dim i As Long
    For Each cel In popupRg.Cells
        i = i + 1
        If cel.Value <> popupArr(i) Then
            chCount = chCount + 1
            popupArr(i) = cel.Value
        End If
    Next cel
    MsgBox "Number of Changes: " & chCount, vbInformation, "Success"
End Sub

ThisWorkbook Module

Option Explicit

Private Sub Workbook_Open()
    popupMsgBoxInit
End Sub

Sheet Module e.g. Sheet1

Option Explicit

Private Sub Worksheet_Calculate()
    popupMsgBox
End Sub

Upvotes: 0

Related Questions