Why is this Worksheet Change Event triggered?

[AJ Master]

I have a model with 3 worksheets, I'm executing code on Sheet 2 that
changes a cell value on Sheet 1, specifically cell "FF198". When I
update this cell the worksheet change event for sheet 1 is triggered
(which I expect) but then it halts on the last line of code and I
receive an error that states: "Run-time error 1004: Select method of
range class failed". Any ideas as to why this is happening??

If I am working on sheet 1 and making changes to values I never
receive this error, so what am I doing that causes the error? Any
thoughts would be appreciated!...Thx....AJ

I have set up the worksheet change event on Sheet 1 with the following
code:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim LocationRange As Range
Dim sNextCell As String

sNextCell = ActiveCell.Offset(1, 0).Address

Set LocationRange = Range("escalationtype1",
"escalationtype2") 'range O86 & range D136

If Not Intersect(Target, LocationRange) Is Nothing Then
FormatEscalationType
End If

Range(sNextCell).Select <=====this is the code the debugger
highlights

End Sub

 

[OssieMac]

It appears to be attempting to select a cell on a worksheet that is not the
active sheet. You can only select cells on the active worksheet.

 

[Dave Peterson]

I'd guess it had something to do with that subroutine that you didn't share.

Maybe you could use:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim LocationRange As Range
Dim NextCell As Range

Set NextCell = Target.Offset(1, 0)

Set LocationRange = Me.Range("escalationtype1", "escalationtype2")

If Not Intersect(Target, LocationRange) Is Nothing Then
FormatEscalationType
End If

Application.Goto NextCell ',scroll:=true

End Sub

 

[OssieMac]

I maybe should have explained my previous post a little better.
I don’t agree with Dave’s comment “something to do with that subroutine that
you didn't share†because I don’t believe that is the real problem. I hope
Dave does not mind my disagreeing. However, I don’t disagree with the code
Dave posted because it should overcome the problem.

I believe that the real reason for your problem needs explaining or you are
going to run into this problem again. It is the default sheet name with code
that is in the sheets module. The default sheet is the sheet that the code
belongs to and as Sheet2 is the ActiveSheet. Your code is attempting to
select a range on Sheet1 without first selecting Sheet1.

A good point of Dave’s is the GoTo. It selects the sheet as well as the
cell. However, the question is:
Do you want the code to Select the range on the ActiveSheet or on Sheet1.
If you want it to Select the active sheet then specify ActiveSheet as
follows:-

ActiveSheet.Range(sNextCell).Select

If you want it to select a range on Sheet1 then you can use either of the
following:-

'Sheet1 is default sheet and goto selects sheet and cell
Application.Goto Range(sNextCell)

Or
Sheets("Sheet1").Select
Range(sNextCell).Select

I hope it gives you a better understanding of what has occurred in your code.

 

[Dave Peterson]

OssieMac,

I agree with about the cause (trying to select a range on a sheet that isn't
active). But I don't see anything in the code that would change to a different
sheet.

So the problem (changing sheets) has to be in the FormatEscalationType
procedure.

Maybe it's just a case of defining what "the real problem" means.

I think you say that changing the activesheet is the real problem.

I was saying that the code inside FormatEscalationType is the real problem. I
just didn't offer any suggestion since the OP didn't share the code.

(Are we in violent agreement? A phrase we used at work when two people realized
that they were on the same side of an argument.)

 

[OssieMac]

Hi Dave,

Yes I have to agree that it was not actually stipulated that the worksheets
were changed but I understood from OP's quotes "I'm executing code on Sheet 2
that changes a cell value on Sheet 1" combined with this quite "If I am
working on sheet 1 and making changes to values I never receive this error"
to mean that Sheet2 was the active sheet.

I guess that I was reading between the lines and I think that my assumption
is probably correct.

I was really just trying to get accross to the OP the info about the default
sheet being the one to which the event code belongs and not the activesheet
and I feel that the question related as much to "Why is it so?" as to "How to
fix it?"

Are we in violent agreement? Based on your definition of what it means; it
now looks like that's a high probability and I hope we can always enjoy a
cordial relationship.

 

[Dave Peterson]

And one thing for the OP to consider is that most things done in excel don't
have to have that worksheet/range/object selected to be modified.

Instead of:

Worksheets("Sheet2").select
Worksheets("Sheet2").range("a2").value = "hi"

It can be done as:

Worksheets("Sheet2").range("A2").value = "hi"

And the problem may disappear.

 

[Tim Zych]

Just as a side-note, ActiveCell is likely not the same as the changed cell.

To see this in action, type a value in a cell and hit the Enter key while
using the test macro below. ActiveCell is the next cell below, while Target
is the actual cell which has been changed. Or in your case,
ActiveCell.Offset(1,0) is two cells from the changed value, not one as you
might surmise.

Private Sub Worksheet_Change(ByVal Target As Range)
Debug.Print ActiveCell.Address
Debug.Print Target.Address
End Sub