Target cell - unexplainable results

Z

zSplash

My Worksheet_SelectionChange code includes:
....
If (Target.Column = 9 And Target.Offset(0, -4) <> "PR") Then
[line2] Target.Offset(0, -1) = StrConv(ActiveCell.Offset(0, -1),
vbProperCase)
ElseIf Target.Column = 9 And Target.Offset(0, -4) = "PR" Then
Target.Offset(0, -1) = StrConv(ActiveCell.Offset(0, -1),
vbUpperCase)
End If
....

Usually works okay, but sometimes (i.e., when Target.column=3), the code
goes to line2 (as if the target.column was 9), and runs line2. What do you
think is up?

TIA
 
J

Jake Marx

Hi zSplash,

Do you have an On Error Resume Next before your code in hopes of catching
the case where you're in columns A:D? The Offset method will raise an error
when you try to go 4 cells to the left if that will take you to the "left"
of column A. If you did use On Error Resume Next, then that would explain
the behavior - the If statement causes an error, so execution continues
inside the If block. Try an On Error Goto; better yet, check the value of
Cells(Target.Row, 5) instead of using Offset, as you're only checking the
value if the selection is in Column 9 anyway.

--
Regards,

Jake Marx
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]
 
Z

zSplash

Precisely right, Jake. Thanks so much.

st.

Jake Marx said:
Hi zSplash,

Do you have an On Error Resume Next before your code in hopes of catching
the case where you're in columns A:D? The Offset method will raise an error
when you try to go 4 cells to the left if that will take you to the "left"
of column A. If you did use On Error Resume Next, then that would explain
the behavior - the If statement causes an error, so execution continues
inside the If block. Try an On Error Goto; better yet, check the value of
Cells(Target.Row, 5) instead of using Offset, as you're only checking the
value if the selection is in Column 9 anyway.

--
Regards,

Jake Marx
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

My Worksheet_SelectionChange code includes:
...
If (Target.Column = 9 And Target.Offset(0, -4) <> "PR") Then
[line2] Target.Offset(0, -1) = StrConv(ActiveCell.Offset(0, -1),
vbProperCase)
ElseIf Target.Column = 9 And Target.Offset(0, -4) = "PR" Then
Target.Offset(0, -1) = StrConv(ActiveCell.Offset(0, -1),
vbUpperCase)
End If
...

Usually works okay, but sometimes (i.e., when Target.column=3), the
code goes to line2 (as if the target.column was 9), and runs line2.
What do you think is up?

TIA
Click to expand...
Click to expand...
 
Top