Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro correction needed
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro correction needed
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" wrote: 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 . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro correction needed
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 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 -- p45cal *p45cal* ------------------------------------------------------------------------ p45cal's Profile: http://www.thecodecage.com/forumz/member.php?u=558 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=197585 http://www.thecodecage.com/forumz |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro correction needed
On Apr 22, 12:04*pm, p45cal wrote:
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 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 -- p45cal *p45cal* ------------------------------------------------------------------------ p45cal's Profile:http://www.thecodecage.com/forumz/member.php?u=558 View this thread:http://www.thecodecage.com/forumz/sh...d.php?t=197585 http://www.thecodecage.com/forumz- Hide quoted text - - Show quoted text - Thanks lot guys. It works now |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Correction Needed in Macro | Excel Programming | |||
CORRECTION NEEDED | Excel Programming | |||
CORRECTION NEEDED IN MACRO | Excel Programming | |||
CORRECTION NEEDED | Excel Programming | |||
correction needed | Excel Programming |