VBA to create MsgBox based on Formula result

[CY56]

Hi All.

I have an Excel file where users can select from various dropdown lists (using data validation)

I have year and month drop downs in cells F12 (Month) and F13 (Year) and on a separate tab I have a list of Year and Month combinations for which we do not have rates.

I have entered a vlookup (G13) to check for the date combination and return "1" if it is found - I would like a Massage Box to pop up if the date is found in this list ie if the vlookup returns "1"

I have managed to get the code below to work but only when "1" is hard typed into the cell - it doesn't work when the formula result = 1.

 Private Sub Worksheet_Change(ByVal Target As Range)
Dim A As Range
Set A = Range("G13")
If Intersect(Target, A) Is Nothing Then Exit Sub
If Target.Value = 1 Then
MsgBox "Rates for this broadcast period are not yet available"
End If
End Sub

Does anyone have any idea how I can get it to work from the formula?

Much appreciated!

 

[NARAYANK991]

Hi ,

Your code is within a Worksheet_Change procedure ; this is what is known as an event procedure ; it is automatically triggered when its corresponding event occurs , and for this procedure , the event occurs when ever a cell is manually changed by user input , not because a formula has changed the cell contents.

Thus , if a cell has a formula , and the result of the formula changes , this event procedure is never triggered.

You can use a Worksheet_Calculate event procedure instead , but then the event procedure will be triggered when ever any change happens anywhere in the worksheet , whether through user input or because a formula has changed its output. And since a formula can depend on cells in other worksheets , you can understand that the Worksheet_Calculate event will happen very frequently compared to the Worksheet_Change event.

However , there is no simple alternative.

Narayan