CODEWITHSUNDEEP
excelvba
Abhishek Kulkarni
Abhishek Kulkarni

Reputation: 13

Excel Message Box option. The cell contains formula, so the message box is not showing the result

I am trying to use the Excel Message Box option. In a cell say E10 I have used a formula, which refers from another sheet. By using this E10 cell, if the cell content changed I want the msg box to appear

This is the code I used:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim A As Range
Set A = Range("E12:E19")
If Intersect(Target, A) Is Nothing Then Exit Sub
If Target.Value = "No" Then
    MsgBox "No Work assigned :( !  " & Target, vbOKOnly, "Abhishek"
    Else
    MsgBox "New task Work on It!  " & Target, vbOKOnly, "Abhishek"
    
End If

End Sub

Upvotes: 1

Views: 267

Answers (1)

FaneDuru
FaneDuru

Reputation: 42236

Please, copy the next code (instead of existing Worksheet_Change event) in the sheet to be processed code module:

Option Explicit

Private prevValue As String 'variable to keep the precedent value
Private Sub Worksheet_Calculate()
    Dim Target As Range
    Set Target = Me.Range("E10")
    If Target.Value <> prevValue Then
        If Target.Value = "No" Then
            MsgBox "No Work assigned :( !  " & Target, vbOKOnly, "Abhishek"
        Else
            MsgBox "New task Work on It!  " & Target, vbOKOnly, "Abhishek"
        End If
        prevValue = Target.Value 'Place the new value in the prevValue variable
    End If
End Sub

Please, send some feedback after testing it.

Upvotes: 2

Related Questions