Excel: Trigger Event when Cell Value Changes due to Calculation

[philqw78]

I have a spreadsheet (Excel 2003*)

Column C changes to "Y" when all the necessary data has been input in that row. I want a message box to appear when the value in column C changes to Y for the active row.

I am using this code but it fires every time any cell chnges.

Dim rngC As Range
Set rngC = Intersect(Range("C201:C64000"), Target)
If rngC.Value = "Y" Then
MsgBox "Data Complete"
End If
 

What can I do?

Thanks in advance

phil

*Sorry they only gave me 2003

[NeoPa]

Please include the procedure declaration line as without it, and without any explanation in your question, we have no idea how this code is triggered. It seems contradictory, but its hard to tell with most of the important information missing.

Phil:
*Sorry they only gave me 2003

What on Earth would you want another version for? 2003 is the most solid and reliable version released for a good long time. 2010 may attain that reputation eventually, but it'll have a way to go to reach the status 2003 has earned.

[philqw78]

Sorry, I have put the code in worksheet on calculate.

[Mihail]

Column "C" can't change to "Y".
I assume that a cell in column "C" is changed to "Y".
If so, this code will "see" if that cell contain "Y".
Put it in the sheet module.

Option Explicit
Dim TargetRow As Long
 
Private Sub Worksheet_Change(ByVal Target As Range)
    'If a change appear in other column than "C" nothing to do
    If Target.Column <> 3 Then
Exit Sub
    End If
    'If a change appear in other row than TargetRow nothing to do
    If Target.Row <> TargetRow Then
Exit Sub
    End If
    If Target.Value = "Y" Then
        'Do something
    End If
End Sub

[NeoPa]

Phil:
Sorry, I have put the code in worksheet on calculate.

That is not an explanation, and it still doesn't include the code with the information in it. I suspect the event you're using is inappropriate, but I don't plan to check out all the details for you when you don't even post what you have.

I suspect Mihail's code uses the event you want, but be careful of assuming only one cell is ever updated at a time. Target can often refer to a range, or even multiple ranges, of contiguous cells.

[philqw78]

The code is at work. I will post it Monday.

[NeoPa]

Ah. Makes sense that you didn't post the details then :-D

Never mind. Target isn't a parameter for Worksheet_Calculate(), as it happens, so your code won't make sense anyway. I suggest you follow Mihail's lead and use Worksheet_Change(ByVal Target As Range) instead.

Target.Address() will return the full list of ranges effected by the change. Alternatively, using something like the following allows you to process through all included cells :

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ranVar As Range
 
    For Each ranVar in Target
        With ranVar
            If .Column = 3 And .Value = "Y"  Then _
                MsgBox "Data complete for row " & .Row
        End With
    Next ranVar
End Sub

[philqw78]

Guys, these only work if I go into the cell and change the value manually. But the cell is a result of a calculation that changes to "Y" when the correct cells have been filled. (Cells needing completion and info needed depend upon type of record.)
So I need to monitor the cell and get a pop up message when the result of the calcualtion changes.
This is the original code I put in

Private Sub Worksheet_Calculate()
 
Dim rngC As Range
Set rngC = Intersect(Range("C201:C64000"), Target)
On Error Resume Next
 
 
If rngC.Value = "Y" Then
MsgBox "Data Complete"
End If
 
 
Hide: 'Filter button
If ActiveSheet.FilterMode = True Then
ActiveSheet.Shapes("Rectangle 183").Visible = True
Else
ActiveSheet.Shapes("Rectangle 183").Visible = False
End If
End Sub

The bit at the bottom hides/shows a button, but shouldn't make any difference, thought I'd best include it for completeness.

[philqw78]

Figured it out thanks guys. I just had to think a bit harder and you pushed me in the right direction. This is what I ended up doing.

I made a formula on the sheet that was the sum of column C, counting a "Y" as 1. If that value increased, soomething must have changed to "Y" so:

Option Explicit
 'Monitor C198 sum of completed records
Dim Monitored
 
Private Sub Worksheet_Activate()
'remember C198
Monitored = Range("c198").Value 
 
End Sub
 
Private Sub Worksheet_Change(ByVal Target As Range)
If Range("C198").Value = Monitored + 1 Then
MsgBox "Data Complete for serial " & Target.Row - 200
'reset   
Monitored = Range("C198").Value
End If
 
If Range("C198").Value <> Monitored + 1 Then
'reset
Monitored = Range("C198").Value
End If

[Mihail]

Good for you, even if I can't understand your logic.
Are you sure that monitorized cell, will be, from now forever, the cell C198 ?
Or, if you will add tomorrow only one row, you'll need to change the code ?

[philqw78]

@Mihail
Rows 1 to 197 are hidden, they have autocomplete lists above the data. Rows 197 to 200 are Titles. All data is row 201 onwards. The users have an action button to insert new rows at the end.
The workbook is VERY locked down. Each row starts with 1 cell that the users enter data into, depending what they enter other cells unlock in that row until they have entered all the necessary data.
As for understanding my logic I have problems with that as well :>)

Thanks for your help Mihail

[NeoPa]

Phil:
Guys, these only work if I go into the cell and change the value manually. But the cell is a result of a calculation that changes to "Y" when the correct cells have been filled. (Cells needing completion and info needed depend upon type of record.)

You're quite right of course. I noticed that when I first looked at the question, but when I revisited it I overlooked that point.

Your solution is workable, but as I'm sure you appreciate, somewhat clumsy. That is the nature of the situation though, rather than a dig at your coding abilities. If I were trying to do exactly that, I would come up with a similarly clumsy solution.

An alternative might be to take a step back at this point then, and look at your actual, overall, requirement. Is it necessary for column C to contain a formula at all if you are already needing to use code to handle the situation. Why not use the Worksheet_Change() event procedure to determine the status (Value in column C) itself, in which case it knows exactly when the status changes? Just a different way of looking at it.

[philqw78]

There are 42 different types of record and hence 42 different ways of getting "Y" for record complete. Getting the "Y" in column C takes calculations in 8 other columns ensuring that all the correct fields have been completed. I suppose I could have nested them in 2 or three cells, but that would have been really confusing if anything needed to change. Getting that done in VB is well beyond me. Clumsy but workable will have to do.

Thanks Neo

Also it should have been done in Access, but they want Excel.

[NeoPa]

It was just an idea.

It's quite right that you determine what suits you best once the ideas are on the table.