Target cell - unexplainable results

  • Thread starter Thread starter zSplash
  • Start date Start date
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
 
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]
 
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...
 
Back
Top