Macro correction needed

[K]

Hi all, I got macro below which checks values of "E" column with the
values of "A" column of Sheets("Parked CC"). And if value match then
a message box pops up which have two buttons "OK" and "Cancel". What
i am trying to do in below macro that when user click "OK" button it
should clear ActiveCell value but if user click "Cancel" button then
it should do nothing and move to next matched value cell. The problem
i am getting in below macro that when i am pressing one of the button
in message box it works ok and move to next cell but as soon as i
press other button it starts again from the top instead of moving to
next cell. I just want it to loop once even if i press different
button in the middle of the process. Hope i was able to explain my
question. Please can any frined can help me in this


Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
If Range("D64").Value <> "" And Range("U33").Value <> "" Then
lastcl = Sheets("Parked CC").Cells(Rows.Count, "A").End(xlUp).Row
lastcl2 = Me.Cells(Rows.Count, "D").End(xlUp).Row
For Each c In Range("E64:E" & lastcl2).Cells
If Application.CountIf(Sheets("Parked CC").Range("A2:A" & lastcl), c)
<> 0 Then
ANS = MsgBox("Cost Centre " & c.Value & " has been closed" &
vbNewLine & "Press OK to clear" & vbNewLine & "Press CANCEL to
ignore", vbOKCancel + vbInformation, "Cost Centre Check!")
c.Select
If ANS = vbOK Then
ActiveCell.ClearContents
Else
End If
End If
Next
Else
Exit Sub
End If
Range("U33").ClearContents
End Sub

 

[Mike H]

Hi,

I struggled to follow this code because I'm not sure where the code is i.e.
which sheet. However the reason it's re-starting is because you need to
disable events while the code is running because every time you change the
sheet the code re-calls itself and you start all over again from the
beginning. Try this change.

Also dimension the variables
dim lastcl as long
dim lastcl2 as long


Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
If Range("D64").Value <> "" And Range("U33").Value <> "" Then
Application.EnableEvents = False
lastcl = Sheets("Parked CC").Cells(Rows.Count, "A").End(xlUp).Row
lastcl2 = Me.Cells(Rows.Count, "D").End(xlUp).Row
For Each c In Range("E64:E" & lastcl2).Cells
If Application.CountIf(Sheets("Parked CC").Range("A2:A" &
lastcl), c) <> 0 Then
ANS = MsgBox("Cost Centre " & c.Value & " has been closed"
& vbNewLine & "Press OK to clear" & vbNewLine & "Press CANCEL to ignore",
vbOKCancel + vbInformation, "Cost Centre Check!")
c.Select
If ANS = vbOK Then
ActiveCell.ClearContents
Else
End If
End If

Next
Else
Application.EnableEvents = True
Exit Sub
End If
Range("U33").ClearContents
Application.EnableEvents = True
End Sub
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[K]

Two things on this one.
Thie is a Worksheet change event macro which itself can change the
sheet, and when it does it gets triggered again, which is why it's
starting over.
Either you limit the range it will run most of its code on by checking
which cells are changing right at the beginning and/or you can disable
events while the code is doing its stuff and re-enable afterwards.
Below I've done the latter and added a bit of an error handler to try to
make sure events aren't permanently disabled.
As far as the first suggestion, you need to decide what range of cells
changing that you want it to respond to, and does the macro change some
of those very cells or change cells elsewhere? (btw. -Target- is the
range of cells that triggered the event, so we can look at that to
decide whether to exit the macro or continue processing.)
Untested:

VBA Code:
--------------------

Private Sub Worksheet_Change(ByVal Target As Range)
  Application.EnableEvents = False
  On Error GoTo Leave
  Dim c As Range
  If Range("D64").Value <> "" And Range("U33").Value <> "" Then
  lastcl = Sheets("Parked CC").Cells(Rows.Count, "A").End(xlUp).Row
  lastcl2 = Me.Cells(Rows.Count, "D").End(xlUp).Row
  For Each c In Range("E64:E" & lastcl2).Cells
  If Application.CountIf(Sheets("Parked CC").Range("A2:A" & lastcl), c)<> 0 Then
  ANS = MsgBox("Cost Centre " & c.Value & " has been closed" & vbNewLine & "Press OK to clear" & vbNewLine & "Press CANCEL to ignore", vbOKCancel + vbInformation, "Cost Centre Check!")
  c.Select
  If ANS = vbOK Then ActiveCell.ClearContents
  End If
  Next
  Else
  Application.EnableEvents = True
  Exit Sub
  End If
  Range("U33").ClearContents
  Leave:
  Application.EnableEvents = True
  End Sub

--------------------

K;706098 Wrote:

Hi all,  I got macro below which checks values of "E" column with the







--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile:http://www.thecodecage.com/forumz/member.php?u=558
View this thread:http://www.thecodecage.com/forumz/showthread.php?t=197585

http://www.thecodecage.com/forumz- Hide quoted text -

- Show quoted text -

Thanks lot guys. It works now