-
Apr 15th, 2010, 09:45 AM
#1
Thread Starter
Lively Member
[vba excel] - check if cell has changed.
Good evening to everybody.
I'd like to check the value of a cell with the previous one stored and, if the value has changed, I need to reset two other cells.
This is my code
Code:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)
Static precedente
If Not Intersect(Range("foglio1!b2"), Target) Is Nothing Then
If Range("foglio1!b2").Value <> precedente Then
'MsgBox precedente
'MsgBox Range("foglio1!b2").Value
Range("foglio1!b5").Value = 0
Range("foglio1!b6").Value = 0
precedente = Range("foglio1!b2").Value
End If
End If
End Sub
It seems to work when the file is already opened. However if I close it and reopen it, even though I rewrite in the cell the same value, the other cells are set to 0. I add the msgboxes and I've understood that the problem is that the precedente variable has no value. How can I store it to make comparison as soon as I open the file? Thanks in advance.
-
Apr 15th, 2010, 03:53 PM
#2
Thread Starter
Lively Member
Re: [vba excel] - check if cell has changed.
Maybe I found a solution.
This is my new code
Code:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)
Application.EnableEvents = False
If Not Intersect(Range("foglio1!b2"), Target) Is Nothing Then
nuovo = Range("foglio1!b2").Value
Application.Undo
precedente = Range("foglio1!b2").Value
If nuovo <> precedente Then
Range("foglio1!b5").Value = 0
Range("foglio1!b6").Value = 0
Range("foglio1!b2").Value = nuovo
End If
End If
Application.EnableEvents = True
End Sub
What do you think about it?
Unluckily have another problem. After I changed value of b2 cell, this cell stays select. I'd like that if I press enter it were selected b3 cell, if I press right arrow were selected c2 cell and so on.
-
Apr 15th, 2010, 04:50 PM
#3
Re: [vba excel] - check if cell has changed.
as the static will have no value carried forward from the last time the file was open you need to save the value somewhere then
vb Code:
if precedente = "" then precedente = 'previous value
you could store in customdocumentproperties or elsewhere
alternatively you could use a global variable in stead of static the assign the stored value to it in the workbook open event
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Apr 15th, 2010, 04:58 PM
#4
Thread Starter
Lively Member
Re: [vba excel] - check if cell has changed.
Hi westconn1 and thanks for your reply. I don't know anything about customdocumentproperties. I'll search on google about it.
However the second code I posted seems to work well. Can you give some advice about cursor movements detecting. I thought that application.onkey method was what I was looking for but maybe I'm not able to use it in the right way.
-
Apr 16th, 2010, 03:03 AM
#5
Re: [vba excel] - check if cell has changed.
However the second code I posted seems to work well.
as your code changes a cell value within the sheetchange event it will again fire the sheetchange event and is fraught with problems, you would need to set a boolean variable, to prevent the change event from firing from changing the code, as it is likely to be recursive and run forever (or until it crashes)
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Apr 16th, 2010, 05:40 AM
#6
Re: [vba excel] - check if cell has changed.
Originally Posted by westconn1
as your code changes a cell value within the sheetchange event it will again fire the sheetchange event and is fraught with problems, you would need to set a boolean variable, to prevent the change event from firing from changing the code, as it is likely to be recursive and run forever (or until it crashes)
Application.EnableEvents = False
is a good use. After that, all events are disabled so the Change event will not fire itself again.
I found that without switching off events, the Change event will fire itself 207 times then stop (not sure why 207).
The code in post#2 seems to works as what required if only one cell B2 is modified.
But "Application.Undo" will cause a side effect if user modifies many cells at the same time such as copy-paste to multiple cells that includes B2 then all modified cells will be undone, not just B2.
The solution pete mentioned in post#3 is better:
alternatively you could use a global variable in stead of static the assign the stored value to it in the workbook open event
In "foglio1" module:
Code:
Option Explicit
Public precedent '-- the keyword Global does not works.
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.[B2]) Is Nothing Then
If Me.precedent <> Me.[B2].Value Then
Me.[B5] = 0
Me.[B6] = 0
Me.precedent = Me.[B2].Value
End If
End If
End Sub
In ThisWorkbook module:
Code:
Option Explicit
Private Sub Workbook_Open()
With Worksheets("foglio1")
.precedent = .[B2]
End With
End Sub
-
Apr 18th, 2010, 07:26 AM
#7
Thread Starter
Lively Member
Re: [vba excel] - check if cell has changed.
Thank you very much anhn. You're always very kind.
Now it's perfect. Have a nice sunday.
-
Jan 3rd, 2012, 07:22 AM
#8
New Member
Re: [vba excel] - check if cell has changed.
Hi,
Thank you for the brilliant notes above. I was trying something similar and worked like a charm for me.
Although, I want to do something like this:
<CODE>
Option Explicit
Public p2
Public p3
Public p4
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.[F2:F4]) Is Nothing Then
If Not Me.[F2] = ""
If Me.p2 <> Me.[F2].Value Then
Me.[G2] = Date
Me.p2 = Me.[F2]
End If
Else
Me.[G2] = ""
End If
If Not Me.[F3] = ""
If Me.p3 <> Me.[F3].Value Then
Me.[G3] = Now()
Me.p3 = Me.[F3]
End If
Else
Me.[G3] = ""
End If
If Not Me.[F4] = ""
If Me.p4 <> Me.[F4].Value Then
Me.[G4] = Date
Me.p4 = Me.[F4]
End If
Else
Me.[G4] = ""
End If
</Code>
I'm a complete noob to VB and thus I have no idea how looping works in a case like this. Any help would be greatly appreciated.
-
Jan 3rd, 2012, 07:24 AM
#9
New Member
Re: [vba excel] - check if cell has changed.
Hi,
Thank you for the brilliant notes above. I was trying something similar and worked like a charm for me.
Although, I want to do something like this:
Code:
Option Explicit
Public p2
Public p3
Public p4
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.[F2:F4]) Is Nothing Then
If Not Me.[F2] = ""
If Me.p2 <> Me.[F2].Value Then
Me.[G2] = Date
Me.p2 = Me.[F2]
End If
Else
Me.[G2] = ""
End If
If Not Me.[F3] = ""
If Me.p3 <> Me.[F3].Value Then
Me.[G3] = Now()
Me.p3 = Me.[F3]
End If
Else
Me.[G3] = ""
End If
If Not Me.[F4] = ""
If Me.p4 <> Me.[F4].Value Then
Me.[G4] = Date
Me.p4 = Me.[F4]
End If
Else
Me.[G4] = ""
End If
I'm a complete noob to VB and thus I have no idea how looping works in a case like this. Any help would be greatly appreciated.
-
Jan 3rd, 2012, 03:44 PM
#10
Re: [vba excel] - check if cell has changed.
I'm a complete noob to VB and thus I have no idea how looping works in a case like this. Any help would be greatly appreciated.
help to do what? you do not specify what result you wish to achieve, or what loop you want to do, this would appear to only fire when cell f2 to f4 are edited, what is the problem with the current code?
you should use indenting in your code so that you can easily identify what starts and finishes where
vb Code:
Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Me.[F2:F4]) Is Nothing Then If Not Me.[F2] = "" If Me.p2 <> Me.[F2].Value Then Me.[G2] = Date Me.p2 = Me.[F2] End If Else Me.[G2] = "" End If If Not Me.[F3] = "" If Me.p3 <> Me.[F3].Value Then Me.[G3] = Now() Me.p3 = Me.[F3] End If Else Me.[G3] = "" End If If Not Me.[F4] = "" If Me.p4 <> Me.[F4].Value Then Me.[G4] = Date Me.p4 = Me.[F4] End If Else Me.[G4] = "" End If End Sub
i can now see you are missing 3 "then" and one "end if"
the compiler should have given you messages about those anyway
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Jan 4th, 2012, 01:44 AM
#11
New Member
Re: [vba excel] - check if cell has changed.
Firstly, Many thanks for your prompt response.
Secondly, apologies..I wasnt clear enough.
About the code being indented, it was when I pasted it..weird it didnt come through.
As you can see I'm writing the same piece of code for F2:F4. I want to do this for the entire F range or at least 100 rows. As in, if any value in F range changes, I want to trigger the macro to insert date in the corresponding G cell. For eg. If my F5 says "New" when I open the file and I change it to say "Closed", G5 should have today's date. So on and so forth.
I could write a function to do this and then call it but the problem remains for the no. of global variables. Say, I wanna do it for 100 rows, do I have to create 100 global variables to capture the state of column F? At present, I'm creating 30 global variables and calling the method 30 different times with 30 different cells. So, it works fine for 30 rows.
The code is not actual code..just a representation.
Code:
Option Explicit
Public p2, p3, p4 ... p30
Function RepeatIt(ByRef F As Range, ByRef G As Range, ByRef Target As Range, ByVal p As String)
If Not F = "" Then
If p <> F.Value Then
G = Date
p = F
End If
Else
G = ""
End If
End Function
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.[F2:F30]) Is Nothing Then
Call RepeatIt(Me.[F2], Me.[G2], Target, Me.p2)
Call RepeatIt(Me.[F3], Me.[G3], Target, Me.p3)
....
Call RepeatIt(Me.[F30], Me.[G30], Target, Me.p30)
I'm sure there is an easier way to do this as its a pain to create so many variables and call the same function so many times.
Many Many Thanks.
-
Jan 5th, 2012, 05:20 AM
#12
Re: [vba excel] - check if cell has changed.
how about like?
vb Code:
if target.row = 6 then select case target case "Closed": target.offset(, 1) = date case "new": target.offset(, 1) = 123 ' other cases end select end if
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Jan 31st, 2012, 05:03 PM
#13
New Member
Re: [vba excel] - check if cell has changed.
I'm sorry guys, but I recently came across this thread, and I seem to be having a probelm with a simple code. I am trying to set up my sheet to reset cell values (k4,k5,k6,k9) when the target cell has been changed (d3). Can someone please help me, I would grately appreciate it.
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Intersect(Range("$D$3"), Target) Is Nothing Then
nuovo = Range("$D$3").Value
Application.Undo
precedente = Range("$D$3").Value
If nuovo <> precedente Then
Range("$K$4").Value = 0
Range("$K$5").Value = 0
Range("$K$6").Value = 0
Range("$K$9").Value = 0
Range("$D$3").Value = nuovo
End If
End If
Application.EnableEvents = True
End Sub
-
Feb 1st, 2012, 05:16 AM
#14
Re: [vba excel] - check if cell has changed.
what is the problem? the code looks ok and appears to work
are you getting errors? wrong results? nothing happening?
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Feb 1st, 2012, 06:36 AM
#15
New Member
Re: [vba excel] - check if cell has changed.
Nothing happens. It doesn't kick back an error, warning, change cell values or nothing. I am lost as to what could be the problem. Did it work when you plugged it in?
-
Feb 1st, 2012, 06:43 AM
#16
Re: [vba excel] - check if cell has changed.
Did it work when you plugged it in?
put some 0 where expected
are you sure the procedure was in the correct place, must be in the code pane for the worksheet containing the cells
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Feb 1st, 2012, 06:50 AM
#17
New Member
Re: [vba excel] - check if cell has changed.
Yes, the code is enter for sheet 3, which is where the cells are located. What is worst is that two days ago, I had the code working correctly, and didn't save it. I continued to make changes, and now, nothing happens. Is there a feature that I could have disabled?
-
Feb 1st, 2012, 08:16 AM
#18
New Member
Re: [vba excel] - check if cell has changed.
Anyway input on what I can change in the following formula:
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range)
Application.EnableEvents = False
If Not Intersect(Range("Sheet1!$B$1"), Target) Is Nothing Then
nuovo = Range("Sheet1!$B$1").Value
Application.Undo
precedente = Range("Sheet1!$B$1").Value
If nuovo <> precedente Then
Range("Sheet3!$K$4").Value = 0
Range("Sheet3!$K$5").Value = 0
Range("Sheet3!$K$6").Value = 0
Range("Sheet3!$K$9").Value = 0
Range("Sheet1!$B$1").Value = nuovo
End If
End If
Application.EnableEvents = True
End Sub
This formula is located the "ThisWorkBook" section. Sorry for the bother.
Last edited by TanktheFrank; Feb 1st, 2012 at 11:24 AM.
-
Feb 1st, 2012, 03:08 PM
#19
Re: [vba excel] - check if cell has changed.
i tested the code initially and it worked so i do not know what your problem is
you should not need to call undo as there is no change unless it is not equal to pevious
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|