Worksheet change doesn't look at code

[Jeff Wright]

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

 

[JW]

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.

 

[JW]

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 Ogilvy]

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

 

[Leith Ross]

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

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

 

[JW]

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

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-

 

[Jeff Wright]

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