Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet change doesn't look at code
Good morning!
I have the following code in my program. When I change the contents of cell M16, I expect that this code will be activated. However, the program doesn't even look at it. Am I doing something wrong?? Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Worksheets("EDGING").Range("M16") = "BEVEL" Then Worksheets("EDGING").Range("A102") = "BevelRadius" ElseIf Worksheets("EDGING").Range("M16") = "PENCIL POLISH" Or _ Worksheets("EDGING").Range("M16") = "HIGH FLAT POLISH" Then Worksheets("EDGING").Range("A102") = "FlatPencilRadius" Else: Worksheets("EDGING").Range("A102") = "None" End If End Sub Thanks, Jeff |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet change doesn't look at code
On Oct 21, 11:08 am, "Jeff Wright" wrote:
Good morning! I have the following code in my program. When I change the contents of cell M16, I expect that this code will be activated. However, the program doesn't even look at it. Am I doing something wrong?? Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Worksheets("EDGING").Range("M16") = "BEVEL" Then Worksheets("EDGING").Range("A102") = "BevelRadius" ElseIf Worksheets("EDGING").Range("M16") = "PENCIL POLISH" Or _ Worksheets("EDGING").Range("M16") = "HIGH FLAT POLISH" Then Worksheets("EDGING").Range("A102") = "FlatPencilRadius" Else: Worksheets("EDGING").Range("A102") = "None" End If End Sub Thanks, Jeff Well, this is odd. My name is Jeff Wright too. You are using SelectionChange when you should be using Change. Private Sub Worksheet_Change(ByVal Target As Range) Also, make sure that the code is placed in the worksheet module. Right click on the sheet tab where you want this to happen and select View Code. Then place the code there. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet change doesn't look at code
On Oct 21, 11:18 am, JW wrote:
On Oct 21, 11:08 am, "Jeff Wright" wrote: Good morning! I have the following code in my program. When I change the contents of cell M16, I expect that this code will be activated. However, the program doesn't even look at it. Am I doing something wrong?? Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Worksheets("EDGING").Range("M16") = "BEVEL" Then Worksheets("EDGING").Range("A102") = "BevelRadius" ElseIf Worksheets("EDGING").Range("M16") = "PENCIL POLISH" Or _ Worksheets("EDGING").Range("M16") = "HIGH FLAT POLISH" Then Worksheets("EDGING").Range("A102") = "FlatPencilRadius" Else: Worksheets("EDGING").Range("A102") = "None" End If End Sub Thanks, Jeff Well, this is odd. My name is Jeff Wright too. You are using SelectionChange when you should be using Change. Private Sub Worksheet_Change(ByVal Target As Range) Also, make sure that the code is placed in the worksheet module. Right click on the sheet tab where you want this to happen and select View Code. Then place the code there. Here is the altered code to be placed in the module of the EDGING sheet. Private Sub Worksheet_Change(ByVal Target As Range) If Range("M16") = "BEVEL" Then Range("A102") = "BevelRadius" ElseIf Range("M16") = "PENCIL POLISH" Or _ Range("M16") = "HIGH FLAT POLISH" Then Range("A102") = "FlatPencilRadius" Else Range("A102") = "None" End If End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet change doesn't look at code
for me, whenever a change is made anywhere on the worksheet in question, this
event fires 213 times (it is good that microsoft has programmed in some sort of limit). I would suggest a modification such as this to cut down on the overhead. Private Sub Worksheet_Change(ByVal Target As Range) if Target.Address = "$M$16" then If Range("M16") = "BEVEL" Then Range("A102") = "BevelRadius" ElseIf Range("M16") = "PENCIL POLISH" Or _ Range("M16") = "HIGH FLAT POLISH" Then Range("A102") = "FlatPencilRadius" Else Range("A102") = "None" End If end if End Sub -- Regards, Tom Ogilvy "JW" wrote: On Oct 21, 11:18 am, JW wrote: On Oct 21, 11:08 am, "Jeff Wright" wrote: Good morning! I have the following code in my program. When I change the contents of cell M16, I expect that this code will be activated. However, the program doesn't even look at it. Am I doing something wrong?? Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Worksheets("EDGING").Range("M16") = "BEVEL" Then Worksheets("EDGING").Range("A102") = "BevelRadius" ElseIf Worksheets("EDGING").Range("M16") = "PENCIL POLISH" Or _ Worksheets("EDGING").Range("M16") = "HIGH FLAT POLISH" Then Worksheets("EDGING").Range("A102") = "FlatPencilRadius" Else: Worksheets("EDGING").Range("A102") = "None" End If End Sub Thanks, Jeff Well, this is odd. My name is Jeff Wright too. You are using SelectionChange when you should be using Change. Private Sub Worksheet_Change(ByVal Target As Range) Also, make sure that the code is placed in the worksheet module. Right click on the sheet tab where you want this to happen and select View Code. Then place the code there. Here is the altered code to be placed in the module of the EDGING sheet. Private Sub Worksheet_Change(ByVal Target As Range) If Range("M16") = "BEVEL" Then Range("A102") = "BevelRadius" ElseIf Range("M16") = "PENCIL POLISH" Or _ Range("M16") = "HIGH FLAT POLISH" Then Range("A102") = "FlatPencilRadius" Else Range("A102") = "None" End If End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet change doesn't look at code
On Oct 21, 9:53 am, Tom Ogilvy
wrote: for me, whenever a change is made anywhere on the worksheet in question, this event fires 213 times (it is good that microsoft has programmed in some sort of limit). I would suggest a modification such as this to cut down on the overhead. Private Sub Worksheet_Change(ByVal Target As Range) if Target.Address = "$M$16" then If Range("M16") = "BEVEL" Then Range("A102") = "BevelRadius" ElseIf Range("M16") = "PENCIL POLISH" Or _ Range("M16") = "HIGH FLAT POLISH" Then Range("A102") = "FlatPencilRadius" Else Range("A102") = "None" End If end if End Sub -- Regards, Tom Ogilvy "JW" wrote: On Oct 21, 11:18 am, JW wrote: On Oct 21, 11:08 am, "Jeff Wright" wrote: Good morning! I have the following code in my program. When I change the contents of cell M16, I expect that this code will be activated. However, the program doesn't even look at it. Am I doing something wrong?? Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Worksheets("EDGING").Range("M16") = "BEVEL" Then Worksheets("EDGING").Range("A102") = "BevelRadius" ElseIf Worksheets("EDGING").Range("M16") = "PENCIL POLISH" Or _ Worksheets("EDGING").Range("M16") = "HIGH FLAT POLISH" Then Worksheets("EDGING").Range("A102") = "FlatPencilRadius" Else: Worksheets("EDGING").Range("A102") = "None" End If End Sub Thanks, Jeff Well, this is odd. My name is Jeff Wright too. You are using SelectionChange when you should be using Change. Private Sub Worksheet_Change(ByVal Target As Range) Also, make sure that the code is placed in the worksheet module. Right click on the sheet tab where you want this to happen and select View Code. Then place the code there. Here is the altered code to be placed in the module of the EDGING sheet. Private Sub Worksheet_Change(ByVal Target As Range) If Range("M16") = "BEVEL" Then Range("A102") = "BevelRadius" ElseIf Range("M16") = "PENCIL POLISH" Or _ Range("M16") = "HIGH FLAT POLISH" Then Range("A102") = "FlatPencilRadius" Else Range("A102") = "None" End If End Sub In addition to to Tom's suggestion, another measure you can use to prevent cascade events is to set the Application.EnableEvents property at the start of the code to False. This will prevent the procedure from running again whenever you change a cells' contents in the code. Set the property back to True at any point you plan to Exit the Sub. Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If Range("M16") = "BEVEL" Then Range("A102") = "BevelRadius" ElseIf Range("M16") = "PENCIL POLISH" Or _ Range("M16") = "HIGH FLAT POLISH" Then Range("A102") = "FlatPencilRadius" Else Range("A102") = "None" End If Application.EnableEvents = True End Sub Sincerely, Leith Ross |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet change doesn't look at code
On Oct 21, 12:53 pm, Tom Ogilvy
wrote: for me, whenever a change is made anywhere on the worksheet in question, this event fires 213 times (it is good that microsoft has programmed in some sort of limit). I would suggest a modification such as this to cut down on the overhead. Private Sub Worksheet_Change(ByVal Target As Range) if Target.Address = "$M$16" then If Range("M16") = "BEVEL" Then Range("A102") = "BevelRadius" ElseIf Range("M16") = "PENCIL POLISH" Or _ Range("M16") = "HIGH FLAT POLISH" Then Range("A102") = "FlatPencilRadius" Else Range("A102") = "None" End If end if End Sub -- Regards, Tom Ogilvy "JW" wrote: On Oct 21, 11:18 am, JW wrote: On Oct 21, 11:08 am, "Jeff Wright" wrote: Good morning! I have the following code in my program. When I change the contents of cell M16, I expect that this code will be activated. However, the program doesn't even look at it. Am I doing something wrong?? Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Worksheets("EDGING").Range("M16") = "BEVEL" Then Worksheets("EDGING").Range("A102") = "BevelRadius" ElseIf Worksheets("EDGING").Range("M16") = "PENCIL POLISH" Or _ Worksheets("EDGING").Range("M16") = "HIGH FLAT POLISH" Then Worksheets("EDGING").Range("A102") = "FlatPencilRadius" Else: Worksheets("EDGING").Range("A102") = "None" End If End Sub Thanks, Jeff Well, this is odd. My name is Jeff Wright too. You are using SelectionChange when you should be using Change. Private Sub Worksheet_Change(ByVal Target As Range) Also, make sure that the code is placed in the worksheet module. Right click on the sheet tab where you want this to happen and select View Code. Then place the code there. Here is the altered code to be placed in the module of the EDGING sheet. Private Sub Worksheet_Change(ByVal Target As Range) If Range("M16") = "BEVEL" Then Range("A102") = "BevelRadius" ElseIf Range("M16") = "PENCIL POLISH" Or _ Range("M16") = "HIGH FLAT POLISH" Then Range("A102") = "FlatPencilRadius" Else Range("A102") = "None" End If End Sub Tom, thanks for the heads up and the testing on the code. Your modification will work great, as long as it is M16 that is receiving the change. If M16 is the result of a formula such as an If statement, then the value in M16 could feasibly change without it being the target range. I reckon Leith's suggestions would be best if that is the case. Regards -Jeff- |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet change doesn't look at code
Thanks to all of you for your comments!! Truly appreciated. I've never used
worksheet code of this type before. I found that my original code does work, but not as fast as I thought. I'll try the using Application.EnableEvents property to avoid cascading events. Thanks again to all of you for your input! Jeff Tucson, Arizona "JW" wrote in message ps.com... On Oct 21, 12:53 pm, Tom Ogilvy wrote: for me, whenever a change is made anywhere on the worksheet in question, this event fires 213 times (it is good that microsoft has programmed in some sort of limit). I would suggest a modification such as this to cut down on the overhead. Private Sub Worksheet_Change(ByVal Target As Range) if Target.Address = "$M$16" then If Range("M16") = "BEVEL" Then Range("A102") = "BevelRadius" ElseIf Range("M16") = "PENCIL POLISH" Or _ Range("M16") = "HIGH FLAT POLISH" Then Range("A102") = "FlatPencilRadius" Else Range("A102") = "None" End If end if End Sub -- Regards, Tom Ogilvy "JW" wrote: On Oct 21, 11:18 am, JW wrote: On Oct 21, 11:08 am, "Jeff Wright" wrote: Good morning! I have the following code in my program. When I change the contents of cell M16, I expect that this code will be activated. However, the program doesn't even look at it. Am I doing something wrong?? Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Worksheets("EDGING").Range("M16") = "BEVEL" Then Worksheets("EDGING").Range("A102") = "BevelRadius" ElseIf Worksheets("EDGING").Range("M16") = "PENCIL POLISH" Or _ Worksheets("EDGING").Range("M16") = "HIGH FLAT POLISH" Then Worksheets("EDGING").Range("A102") = "FlatPencilRadius" Else: Worksheets("EDGING").Range("A102") = "None" End If End Sub Thanks, Jeff Well, this is odd. My name is Jeff Wright too. You are using SelectionChange when you should be using Change. Private Sub Worksheet_Change(ByVal Target As Range) Also, make sure that the code is placed in the worksheet module. Right click on the sheet tab where you want this to happen and select View Code. Then place the code there. Here is the altered code to be placed in the module of the EDGING sheet. Private Sub Worksheet_Change(ByVal Target As Range) If Range("M16") = "BEVEL" Then Range("A102") = "BevelRadius" ElseIf Range("M16") = "PENCIL POLISH" Or _ Range("M16") = "HIGH FLAT POLISH" Then Range("A102") = "FlatPencilRadius" Else Range("A102") = "None" End If End Sub Tom, thanks for the heads up and the testing on the code. Your modification will work great, as long as it is M16 that is receiving the change. If M16 is the result of a formula such as an If statement, then the value in M16 could feasibly change without it being the target range. I reckon Leith's suggestions would be best if that is the case. Regards -Jeff- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Change the code in another worksheet | Excel Programming | |||
change the name of a worksheet/update VBA code | Excel Discussion (Misc queries) | |||
Change Code from a Worksheet | Excel Programming | |||
Code Conflicts With Worksheet Change Code | Excel Programming | |||
Worksheet change code | Excel Programming |