|
Please Help, Code Locks up
Can someone tell me what is wrong with this code? My attempt is what Cell C6
equals a specific object name; a range of other cells will equal the value of another cell. I can get it to work; this issue is that is only works once. When the program runs, it works as plan but locks up excel and I end up having to go through "Ctrl+Alt+Delete" in order to have access to my computer again. Here is my code: Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If Range("C6") = "Object Name" Then Range("H6").Value = Range(Q6) Range("L6").Value = Range(R6) Range("C8").Value = Range(S6) Range("H8").Value = Range(T6) Range("L8").Value = Range(U6) Range("C10").Value = Range(V6) Range("H10").Value = Range(W6) Else Range("H6").Value = "" Range("L6").Value = "" Range("C8").Value = "" Range("H8").Value = "" Range("L8").Value = "" Range("C10").Value = "" Range("H10").Value = "" End If End With End Sub |
Please Help, Code Locks up
I see a couple of things with your code.
1. Your With Target statement does nothing since you never reference the target so you can drop that line and the End With. 2. I like that you have referenced the Value on th eleft side of the equal statements but you dont bother to do it on the right. While this is not wrong (as Value is the default property of a range) it would be nice to see it on the right side also. 3. The biggest thing is that your change code causes a change, which will in turn fire the change event which will in turn cause the code to execute ad-infinitum. You have a recursive loop. You need to disable events before you make the change and then turn them back on again at the end. Give this code a try... Private Sub Worksheet_Change(ByVal Target As Excel.Range) On Error GoTo ErrorHandler Application.EnableEvents = False If Range("C6") = "Object Name" Then Range("H6").Value = Range(Q6).Value Range("L6").Value = Range(R6).Value Range("C8").Value = Range(S6).Value Range("H8").Value = Range(T6).Value Range("L8").Value = Range(U6).Value Range("C10").Value = Range(V6).Value Range("H10").Value = Range(W6).Value Else Range("H6").Value = "" Range("L6").Value = "" Range("C8").Value = "" Range("H8").Value = "" Range("L8").Value = "" Range("C10").Value = "" Range("H10").Value = "" End If ErrorHandler: Application.EnableEvents = True End Sub -- HTH... Jim Thomlinson "Ryan" wrote: Can someone tell me what is wrong with this code? My attempt is what Cell C6 equals a specific object name; a range of other cells will equal the value of another cell. I can get it to work; this issue is that is only works once. When the program runs, it works as plan but locks up excel and I end up having to go through "Ctrl+Alt+Delete" in order to have access to my computer again. Here is my code: Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If Range("C6") = "Object Name" Then Range("H6").Value = Range(Q6) Range("L6").Value = Range(R6) Range("C8").Value = Range(S6) Range("H8").Value = Range(T6) Range("L8").Value = Range(U6) Range("C10").Value = Range(V6) Range("H10").Value = Range(W6) Else Range("H6").Value = "" Range("L6").Value = "" Range("C8").Value = "" Range("H8").Value = "" Range("L8").Value = "" Range("C10").Value = "" Range("H10").Value = "" End If End With End Sub |
Please Help, Code Locks up
THank you for the help, that was really driving me nuts. There is still one
problem that I havent been able to get. There code Range("H6").Value = Range("Q6").Value doesnt work. It does work when I manually set the value. Range("H6").Valvue = "123456" It doesnt make since for it worked with my original formula. Please advice "Jim Thomlinson" wrote: I see a couple of things with your code. 1. Your With Target statement does nothing since you never reference the target so you can drop that line and the End With. 2. I like that you have referenced the Value on th eleft side of the equal statements but you dont bother to do it on the right. While this is not wrong (as Value is the default property of a range) it would be nice to see it on the right side also. 3. The biggest thing is that your change code causes a change, which will in turn fire the change event which will in turn cause the code to execute ad-infinitum. You have a recursive loop. You need to disable events before you make the change and then turn them back on again at the end. Give this code a try... Private Sub Worksheet_Change(ByVal Target As Excel.Range) On Error GoTo ErrorHandler Application.EnableEvents = False If Range("C6") = "Object Name" Then Range("H6").Value = Range(Q6).Value Range("L6").Value = Range(R6).Value Range("C8").Value = Range(S6).Value Range("H8").Value = Range(T6).Value Range("L8").Value = Range(U6).Value Range("C10").Value = Range(V6).Value Range("H10").Value = Range(W6).Value Else Range("H6").Value = "" Range("L6").Value = "" Range("C8").Value = "" Range("H8").Value = "" Range("L8").Value = "" Range("C10").Value = "" Range("H10").Value = "" End If ErrorHandler: Application.EnableEvents = True End Sub -- HTH... Jim Thomlinson "Ryan" wrote: Can someone tell me what is wrong with this code? My attempt is what Cell C6 equals a specific object name; a range of other cells will equal the value of another cell. I can get it to work; this issue is that is only works once. When the program runs, it works as plan but locks up excel and I end up having to go through "Ctrl+Alt+Delete" in order to have access to my computer again. Here is my code: Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If Range("C6") = "Object Name" Then Range("H6").Value = Range(Q6) Range("L6").Value = Range(R6) Range("C8").Value = Range(S6) Range("H8").Value = Range(T6) Range("L8").Value = Range(U6) Range("C10").Value = Range(V6) Range("H10").Value = Range(W6) Else Range("H6").Value = "" Range("L6").Value = "" Range("C8").Value = "" Range("H8").Value = "" Range("L8").Value = "" Range("C10").Value = "" Range("H10").Value = "" End If End With End Sub |
Please Help, Code Locks up
Nevermind...I figured it out.
I had to go a different way, I ended up using VLOOKUP but thank you again. "Jim Thomlinson" wrote: I see a couple of things with your code. 1. Your With Target statement does nothing since you never reference the target so you can drop that line and the End With. 2. I like that you have referenced the Value on th eleft side of the equal statements but you dont bother to do it on the right. While this is not wrong (as Value is the default property of a range) it would be nice to see it on the right side also. 3. The biggest thing is that your change code causes a change, which will in turn fire the change event which will in turn cause the code to execute ad-infinitum. You have a recursive loop. You need to disable events before you make the change and then turn them back on again at the end. Give this code a try... Private Sub Worksheet_Change(ByVal Target As Excel.Range) On Error GoTo ErrorHandler Application.EnableEvents = False If Range("C6") = "Object Name" Then Range("H6").Value = Range(Q6).Value Range("L6").Value = Range(R6).Value Range("C8").Value = Range(S6).Value Range("H8").Value = Range(T6).Value Range("L8").Value = Range(U6).Value Range("C10").Value = Range(V6).Value Range("H10").Value = Range(W6).Value Else Range("H6").Value = "" Range("L6").Value = "" Range("C8").Value = "" Range("H8").Value = "" Range("L8").Value = "" Range("C10").Value = "" Range("H10").Value = "" End If ErrorHandler: Application.EnableEvents = True End Sub -- HTH... Jim Thomlinson "Ryan" wrote: Can someone tell me what is wrong with this code? My attempt is what Cell C6 equals a specific object name; a range of other cells will equal the value of another cell. I can get it to work; this issue is that is only works once. When the program runs, it works as plan but locks up excel and I end up having to go through "Ctrl+Alt+Delete" in order to have access to my computer again. Here is my code: Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If Range("C6") = "Object Name" Then Range("H6").Value = Range(Q6) Range("L6").Value = Range(R6) Range("C8").Value = Range(S6) Range("H8").Value = Range(T6) Range("L8").Value = Range(U6) Range("C10").Value = Range(V6) Range("H10").Value = Range(W6) Else Range("H6").Value = "" Range("L6").Value = "" Range("C8").Value = "" Range("H8").Value = "" Range("L8").Value = "" Range("C10").Value = "" Range("H10").Value = "" End If End With End Sub |
| All times are GMT +1. The time now is 02:42 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com