CODEWITHSUNDEEP
vbamessagebox
Jordan Johnson
Jordan Johnson

Reputation: 11

Notification message upon cell change

So I want a message to appear when a cell changes that notifies somebody when the cell is changed. The cells that change are referenced from cells in another workbook. Here is my code so far, but it only works when I manually change the cells. It doesn't work when I change the cell being referenced.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCells As Range

    Set KeyCells = Range("B2:P43")

    If Not Application.Intersect(KeyCells, Range(Target.Address)) _
        Is Nothing Then

        Beep

        MsgBox "Cell " & Target.Address & "has changed."

    End If
End Sub

Upvotes: 1

Views: 108

Answers (1)

LimaNightHawk
LimaNightHawk

Reputation: 7083

Try fully qualifying the Ranges, like this:

Set KeyCells = Sheet1.Range("B2:P43")

Upvotes: 1

Related Questions