By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,483 Members | 1,464 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,483 IT Pros & Developers. It's quick & easy.

Excel: Trigger Event when Cell Value Changes due to Calculation

P: 31
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.
Expand|Select|Wrap|Line Numbers
  1. Dim rngC As Range
  2. Set rngC = Intersect(Range("C201:C64000"), Target)
  3. If rngC.Value = "Y" Then
  4. MsgBox "Data Complete"
  5. End If
  6.  
What can I do?

Thanks in advance

phil

*Sorry they only gave me 2003
Mar 30 '12 #1
Share this Question
Share on Google+
13 Replies


NeoPa
Expert Mod 15k+
P: 31,186
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.
Mar 30 '12 #2

P: 31
Sorry, I have put the code in worksheet on calculate.
Mar 31 '12 #3

100+
P: 759
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.

Expand|Select|Wrap|Line Numbers
  1. Option Explicit
  2. Dim TargetRow As Long
  3.  
  4. Private Sub Worksheet_Change(ByVal Target As Range)
  5.     'If a change appear in other column than "C" nothing to do
  6.     If Target.Column <> 3 Then
  7. Exit Sub
  8.     End If
  9.     'If a change appear in other row than TargetRow nothing to do
  10.     If Target.Row <> TargetRow Then
  11. Exit Sub
  12.     End If
  13.     If Target.Value = "Y" Then
  14.         'Do something
  15.     End If
  16. End Sub
Mar 31 '12 #4

NeoPa
Expert Mod 15k+
P: 31,186
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.
Apr 1 '12 #5

P: 31
The code is at work. I will post it Monday.
Apr 1 '12 #6

NeoPa
Expert Mod 15k+
P: 31,186
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 :
Expand|Select|Wrap|Line Numbers
  1. Private Sub Worksheet_Change(ByVal Target As Range)
  2.     Dim ranVar As Range
  3.  
  4.     For Each ranVar in Target
  5.         With ranVar
  6.             If .Column = 3 And .Value = "Y"  Then _
  7.                 MsgBox "Data complete for row " & .Row
  8.         End With
  9.     Next ranVar
  10. End Sub
Apr 1 '12 #7

P: 31
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
Expand|Select|Wrap|Line Numbers
  1. Private Sub Worksheet_Calculate()
  2.  
  3. Dim rngC As Range
  4. Set rngC = Intersect(Range("C201:C64000"), Target)
  5. On Error Resume Next
  6.  
  7.  
  8. If rngC.Value = "Y" Then
  9. MsgBox "Data Complete"
  10. End If
  11.  
  12.  
  13. Hide: 'Filter button
  14. If ActiveSheet.FilterMode = True Then
  15. ActiveSheet.Shapes("Rectangle 183").Visible = True
  16. Else
  17. ActiveSheet.Shapes("Rectangle 183").Visible = False
  18. End If
  19. 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.
Apr 2 '12 #8

P: 31
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:
Expand|Select|Wrap|Line Numbers
  1. Option Explicit
  2.  'Monitor C198 sum of completed records
  3. Dim Monitored
  4.  
  5. Private Sub Worksheet_Activate()
  6. 'remember C198
  7. Monitored = Range("c198").Value 
  8.  
  9. End Sub
  10.  
  11. Private Sub Worksheet_Change(ByVal Target As Range)
  12. If Range("C198").Value = Monitored + 1 Then
  13. MsgBox "Data Complete for serial " & Target.Row - 200
  14. 'reset   
  15. Monitored = Range("C198").Value
  16. End If
  17.  
  18. If Range("C198").Value <> Monitored + 1 Then
  19. 'reset
  20. Monitored = Range("C198").Value
  21. End If
Apr 2 '12 #9

100+
P: 759
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 ?
Apr 2 '12 #10

P: 31
@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
Apr 2 '12 #11

NeoPa
Expert Mod 15k+
P: 31,186
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.
Apr 2 '12 #12

P: 31
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.
Apr 2 '12 #13

NeoPa
Expert Mod 15k+
P: 31,186
It was just an idea.

It's quite right that you determine what suits you best once the ideas are on the table.
Apr 2 '12 #14

Post your reply

Sign in to post your reply or Sign up for a free account.