Worksheet_change event executed on a wrong sheet?

Hi everyone,

Is it thinkable that a worksheet_change event is executed on another sheet? the case is that a particular messagebox sometimes appears on sheet B while the worksheet_change code, that fires this messagebox, is on the sheet A. Untill now i couldn't reproduce this event myself, but one of the users of my programme claims that it happens sometimes.

I think the circumstances under which this happens are as follows. The worksheet_change event will be executed if a particular cell is empty. Normaly this cell should contain a j or a n. While this particular cell is active (and not empty) a user can activate another sheet by pushing the sheet tab. On this sheet the messagebox appears that was fired by the worksheet_change event on the previous sheet. The strange thing is that the code shouldn't be executed at all, because the cell wasn't empty. Is it possible that the fact the cell was active (selected) before changing sheets causes this problem? What can i do to prevent this?

Best regards,
Stranno

Show us the code, otherwise it is just existential musings.

To answer your question specifically, no, there would need to be some other factor. If some code ran when you deactivated the sheet in question, or activated another sheet, both of which can have their own events, either specifically or at the workbook (class) level. So as xld put it, any futher would be musings.

Oke, Here is the code:

Code:

---

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim ant As String

 
Application.EnableEvents = False
On Error Resume Next
    If Not Application.Intersect(Target, ActiveSheet.Columns("L:L")) Is Nothing Then
          'Target.Value = "j" by Default
        If Target.Value <> "j" And Target.Value <> "n" Then
          Target = ""
          Target.Select
        End If
   
        Select Case Target.Value
       
            Case "n"
                  Target.Offset(0, -4) = -1 * Abs(Val(Target.Offset(0, -4).Value))
                  Target.Offset(0, -6) = -1 * Abs(Val(Target.Offset(0, -6).Value))
                  Target.Offset(0, -6).NumberFormat = "0;[Red]0"
               
            Case "j"
                  Target.Offset(0, -4) = Abs(Val(Target.Offset(0, -4).Value))
                  Target.Offset(0, -6) = Abs(Val(Target.Offset(0, -6).Value))
                  Target.Offset(0, -6).NumberFormat = "0;[Red]0"

            Case ""
            again:
ant = InputBox("j of n", "Kies j of n")
                  If Len(ant) = 0 Then GoTo again
                  If ant <> "j" And ant <> "n" Then GoTo again
                  Target = ant
                  If ant = "n" Then Target.Offset(0, -4) = -1 * Abs(Val(Target.Offset(0, -4).Value))
                  If ant = "j" Then Target.Offset(0, -4) = Abs(Val(Target.Offset(0, -4).Value))
                 
            Case Else
                  GoTo proceed
        End Select
    End If

proceed:
If Err.Number <> 0 Then
    Call ErrorDescription("pfd", "Worksheet_Change") 'calls the function ErrorDescription
    Err.Clear
End If
Application.EnableEvents = True
On Error GoTo 0
End Sub

---

The code is in Sheet("Pfd")
But i think there is nothing wrong with the code.

The worksheet in which the inputbox (sorry it was an inputbox, not a messagebox) shows up
contains no code.

En nu nog even die code tags Stranno (Na 89 berichten !!!)

At first glance, try removing ActiveSheet. from

Code:

---

If Not Application.Intersect(Target, ActiveSheet.Columns("L:L")) Is Nothing Then

---

to leave:

Code:

---

If Not Application.Intersect(Target, Columns("L:L")) Is Nothing Then

---

If it is Activesheet that might cause the problem, you need to use

Code:

---

If Not Application.Intersect(Target, Me.Columns("L:L")) Is Nothing Then

---

without a sheet qualifier it will refer to the Activesheet.

I thought that all unqualified ranges in a sheet's code module referred to that same sheet rather than the active sheet? I will have to test this later when I get in front of a pc..

Nope. All unqualified Range references will always reference the active sheet. And likewise all unqualified worksheet references will always reference the active workbook.

Edit: The key is to declare it always. If it's supposed to be the sheet the code is being called from, using "Me" as Bob suggests would help. But worksheet event code can be fired anywhere, regardless of what sheet is active or not, so specifying the worksheet should always be done.

I think some UDF might be running.

When the eventcode

Code:

---

Private Sub Worksheet_Change(ByVal Target As Range)

---

has been triggered, the worksheet is always the activesheet.

I don't think it would make any difference using:

Code:

---

If Not Application.Intersect(Target, Columns(12)) Is Nothing Then

---

or

Code:

---

If Not Application.Intersect(Target, activesheet.Columns(12)) Is Nothing Then

---

or

Code:

---

If Not Application.Intersect(Target, target.parent.Columns(12)) Is Nothing Then

---

Ok I will try "If Not Application.Intersect(Target, Me.Columns("L:L")) Is Nothing Then" instead of "If Not Application.Intersect(Target, ActiveSheet.Columns("L:L")) Is Nothing Then", since this code is in a worksheet code module, what's exactly the difference between ActiveSheet, Me or even no additive like p45 cal proposed?

Zack answered my question above I guess.

apparently not Snb. What else can cause this problem?

Quote:

---

Originally Posted by stranno

Oke, Here is the code:
But i think there is nothing wrong with the code.

---

Other than the fact it doesn't work?

In addition to the other responses, esp. Zacks,

[vba]
Worksheet_Change(ByVal Target As Range)
[/vba]

1. Target is the range that was changed, and might be many cells. If you want to be sure that you're only working on a cell use Target.Cells.Count. Something like Target.Value = "j" will make all the cells that changed = "j"

2. Target.Parent will return the WS object of Target range.

3. You have a On Error Resume Next that might be bypassing an error. If you remove it, do you get an indications of an error?

Paul

You can certainly fire code from another sheet and never be on that sheet (as the active sheet). As an example, in a standard module have something like this...

[vba]Sub footest()
Sheet2.Activate
Sheet1.Range("A1").Value = 1
End Sub[/vba]

And in Sheet1 code module have the following event code...
[vba]Private Sub Worksheet_Change(ByVal Target As Range)
'MsgBox "Change on '" & Me.Name & "' in " & Target.Address(0, 0)
ActiveSheet.Range("A1").Value = 2
End Sub[/vba]

Now run footest(). Sheet2 stays the active sheet, but Sheet1 code references the activesheet and will thus change Sheet2 cell A1 value. Make sense?

Quote:

---

Originally Posted by Zack Barresse

Nope. All unqualified Range references will always reference the active sheet. And likewise all unqualified worksheet references will always reference the active workbook.

Edit: The key is to declare it always. If it's supposed to be the sheet the code is being called from, using "Me" as Bob suggests would help. But worksheet event code can be fired anywhere, regardless of what sheet is active or not, so specifying the worksheet should always be done.

---

Zack, xld,
I'm not sure whether we're talking at cross-purposes, as I think I've tested this with the attached file.
There are two sheets, on sheet1 I've placed a button (so that we're certain that sheet1 is the active sheet when it's clicked) which when clicked runs the following code which changes a cell on Sheet2:

Code:

---

Private Sub CommandButton1_Click()
Sheets("Sheet2").Range("B2") = Sheets("Sheet2").Range("B2") & "q"
End Sub

---

This will trigger the event code in Sheet2's code module:

Code:

---

Private Sub Worksheet_Change(ByVal Target As Range)
Debug.Print Columns("L:L").Parent.Name
'or:MsgBox Columns("L:L").Parent.Name
End Sub

---

where I've used an unqualified Columns("L:L"), so you would expect that to refer to the active sheet Sheet1, here in Excel 2010 I get the result Sheet2.. or have I fouled up badly?

Hmm, well I stand very much corrected! Perhaps it's in lieu of being called from a class module it assumes the class is the default reference. When called from a standard module the behavior is as expected, a non-referenced sheet is always the activesheet. For example..

[vba]Sub foo()
Sheet1.Activate
Range("A1").Value = 1
Sheet2.Activate
Range("A1").Value = 2
End Sub[/vba]

Quote:

---

Originally Posted by Paul_Hossler

Other than the fact it doesn't work?

In addition to the other responses, esp. Zacks,

[vba]
Worksheet_Change(ByVal Target As Range)
[/vba]

1. Target is the range that was changed, and might be many cells. If you want to be sure that you're only working on a cell use Target.Cells.Count. Something like Target.Value = "j" will make all the cells that changed = "j"

2. Target.Parent will return the WS object of Target range.

3. You have a On Error Resume Next that might be bypassing an error. If you remove it, do you get an indications of an error?

Paul

---

Apparently it does work for 99.99%. But in some occasions (and i don't know when) it doesn't.

1. I am not sure if I know what you mean with using "Target.Cells.Count" What is the purpose of delimiting de range? I mean the target can be any cell in Column L. Or do you mean that I should add something like
"If Target.Cells.Count>0 then exit" sub?

2. Maybe a good idea. But what is exactly the difference between Target.Parent and "Target, Me.Collumns("L:L")" in this case?
3. Also a good idea, but again it's very hard to verify the effect because so far I could not reproduce this issue myself.

But Summarized, what is the best solution?

Use of: "Me.Parent, Columns("L:L")" or "Target, Me.Columns("L:L")" or is there yet another solution?


In the meantime I will check all the hints that came by in this thread.
Thanks everybody. Although I think the VBA experts on this forum seems to have different opinions on this issue.

Best regards,
Stranno

Quote:

---

Originally Posted by Zack Barresse

But worksheet event code can be fired anywhere, regardless of what sheet is active or not, so specifying the worksheet should always be done.

---

Yeah, but people don't do that, do they (you?), Zack?

Quote:

---

Originally Posted by Zack Barresse

Hmm, well I stand very much corrected! Perhaps it's in lieu of being called from a class module it assumes the class is the default reference. When called from a standard module the behavior is as expected, a non-referenced sheet is always the activesheet. For example..

---

Hi FireFighter (I cannot recall where the 'y' is, sorry.),

Your above comment made me think (painful, but survivable).

Rather than thinking in terms of the object module being the exception, might it be better to think (regardless of usage rate) of using the unqualified Range in a Standard Module as the oddity? My thinking (such as it may be), is that the Standard Module doesn't possess/own anything, whereas the sheet's object module of course does.

Mark

I don't think it's possible to offer a 'solution/remedy' if the 'ailment' isn't clear.
You'd better post the 'suffering' workbook.

Quote:

---

Originally Posted by stranno

1. I am not sure if I know what you mean with using "Target.Cells.Count" What is the purpose of delimiting de range? I mean the target can be any cell in Column L. Or do you mean that I should add something like
"If Target.Cells.Count>0 then exit" sub?

---

What that means is that the user could change many cells in a single action. If that happens and you don't trap it may not work as you want. You should test Target.Cells.Count for > 1 and if so, exit without doing anything, or else you could process every cell in Target within some kind of loop.

Quote:

---

Originally Posted by stranno

2. Maybe a good idea. But what is exactly the difference between Target.Parent and "Target, Me.Collumns("L:L")" in this case?

---

A lot. Target.Parent refers to the Parent object of Target, and as Target is a range, the Parent is the worksheet that that range is contained within. Target, Me.Coilumns("L:L") is referring to two objects, that same range object that triggered the event, and a specific range object in the worksheet, namely all of column L.

Quote:

---

Originally Posted by stranno

3. Also a good idea, but again it's very hard to verify the effect because so far I could not reproduce this issue myself.

But Summarized, what is the best solution?

Use of: "Me.Parent, Columns("L:L")" or "Target, Me.Columns("L:L")" or is there yet another solution?

---

The first one won't work, because you are testing whether a worksheet object intersects a range object, apples and pears.

So, summerising ( if that is indeed the word) Firefighter is somewhere in the world but unknown to GTO, XLD is tossing Apples & Pears, and snb needs some sort of medicare because his workbook is ailing. Gees. I can't wait for the next episode.

@Mark: Good point! Probably a better way to put what I was thinking. I don't know if that's actually the case and was just speculating. Would make sense though. And the "y" used to be "firefytr", which I changed to my real name after meeting quite a few people and was apparently confusing, so I figured transparency would be better lol. And I hope it wasn't too painful of thinking! :)

@Bob: I try to always specify the worksheet. I mean, I've always tried to use the 'Me' object. If nothing else it makes it easier to read (IMHO). And I certainly hope to toggle events off when needed! :)

@Aussiebear: LOL! Oh man, I love this board. :D

Reading between the lines on On Line 2010 Help ...

Quote:

---

Invalid use of Me keyword
This page is specific to the Visual Basic for Applications (VBA) Language Reference for Office 2010. The Me keyword can appear in class modules. This error has the following causes and solutions:

• The Me keyword appeared in a standard module.
  The Me keyword can't appear in a standard module because a standard module doesn't represent an object. If you copied the code in question from a class module, you have to replace the Me keyword with the specific object or form name to preserve the original reference.
  
• The Me keyword appeared on the left side of a Set assignment, for example:

---

the text in red makes it seem that Range("A1") (no specific parent) on a worksheet's code module resolves to that worksheet, where as in a standard module it resolves to the ActiveSheet.


I'm with Zack -- I always specify the full name, since I'm in favor of anything that helps prevent me from making errors

Paul

@stranno

The ideas and suggestions from all the others are very good coding practices.

Demonstration of some techniques I like to use that might be applicable based on what I could figure out from your code. This is NOT WAY a solution since I didn't grasp what you were trying to accomplish, but merely some thoughts.

1. No ActiveSheet
2. I assumed that only one cell would be changed at a time, and it's that cell .Offsets you wanted
3. This was not tested for logic error, but it does compile

Code:

---

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ant As String
   
    If Target.Cells.Count <> 1 Then Exit Sub
   
    If Application.Intersect(Target, Target.Parent.Columns(12)) Is Nothing Then Exit Sub
   
    Application.EnableEvents = False
   
    With Target
       
        ant = .Value
       
        Select Case ant
            Case "n"
                .Offset(0, -4) = -1 * Abs(Val(.Offset(0, -4).Value))
                .Offset(0, -6) = -1 * Abs(Val(.Offset(0, -6).Value))
                .Offset(0, -6).NumberFormat = "0;[Red]0"
               
            Case "j"
                .Offset(0, -4) = Abs(Val(.Offset(0, -4).Value))
                .Offset(0, -6) = Abs(Val(.Offset(0, -6).Value))
                .Offset(0, -6).NumberFormat = "0;[Red]0"
               
            Case ""
                ant = vbNullString
               
                Do
                    ant = InputBox("j of n", "Kies j of n")
                Loop Until (ant = "n" Or ant = "j")
           
                If ant = "n" Then
                    .Offset(0, -4) = -1 * Abs(Val(.Offset(0, -4).Value))
                Else
                    .Offset(0, -4) = Abs(Val(.Offset(0, -4).Value))
                End If
       
        Case Else
            If Err.Number <> 0 Then
                Call MsgBox(Err.Description, vbCritical + vbOKOnly, Me.Name & " -- " & "Worksheet_Change")
                Err.Clear
            End If
            On Error GoTo 0
       
        End Select
    End With
   
    Application.EnableEvents = True
End Sub

---

Paul

Thanks Paul. Very helpful.

Or:

Code:

---

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub
   
    If Application.Intersect(Target, Target.Parent.Columns(12)) Is Nothing Then Exit Sub
    If InStr("jn", LCase(Target)) = 0 Then
        Target.Select
        Exit Sub
    End If
   
    Application.EnableEvents = False

    With Target
        .Offset(, -4) = Abs(.Offset(, -4).Value) * (1 + 2 * (Target = "n"))
        .Offset(, -6) = Abs(.Offset(, -6).Value) * (1 + 2 * (Target = "n"))
        .Offset(, -6).NumberFormat = "0;[Red]0"
    End With

    Application.EnableEvents = True
End Sub

---

Quote:

---

Originally Posted by Zack Barresse

@Mark: ...I don't know if that's actually the case and was just speculating. Would make sense though...

---

Hi Zack,

I am probably misunderstanding your comment, in which case, please forgive. Just in case you were saying that you're not sure that the unqualified Range() in a worksheet's module, defaults to the module, it does. The help topic pretty much sucks at not mentioning this any place I found, but it is a "for sure and for certain" to the best of my knowledge. You might enjoy http://www.thecodecage.com/forumz/mi...e-not-why.html, particularly #24 where Rory "caught" me.

Quote:

---

Originally Posted by Zack Barresse

...And the "y" used to be "firefytr", which I changed to my real name after meeting quite a few people and was apparently confusing, so I figured transparency would be better lol.

---

Thank you so much :-) I hope you won't mind if I occasionally use that in respect to your chosen field. I imagine that Fire there is similar to here in that a lot of learning is required in EMS related as well as fire science. An honorable endeavor and my appreciation.

Quote:

---

Originally Posted by Zack Barresse

And I hope it wasn't too painful of thinking! :)

---

It's okay, my pea-sized brain is used to hurting/straining easily. Luckily though, when thinking something through is over, it has plenty of room to stretch and relax in the copious amount of empty space in my noggin.

Quote:

---

Originally Posted by Zack Barresse

@Aussiebear: LOL! Oh man, I love this board. :D

---

:thumb As Oorang would way, "WOOT!". I certainly agree!

Mark

Ah, I see, I was saying the opposite, that since a standard module would have unqualified/unspecified objects, it was probably the class which [forces] the inheritance.

These days all I'm doing is EMS actually. You can call me whatever you want, I'll answer to both. :)

Quote:

---

Originally Posted by Zack Barresse

You can call me whatever you want, I'll answer to both. :)

---

What makes you think in 2 dimensions....... :devil2:

Code:

---

Private Sub Worksheet_Change(ByVal Target As Range)
   
    Dim ant As String
    Dim X As Integer
    X = 1 'Default value
   
    If Target.Count > 1 Then Exit sub
    If Application.Intersect(Target, Columns("L")) Is Nothing Then Exit Sub
   
    Application.EnableEvents = False
    On Error GoTo proceed
   
    ant = Target.Value 'Target.Value = "j" by Default
again:
  If ant <> "j" And ant <> "n" Then
    ant = InputBox("j of n", "Kies j of n")
    GoTo again 'repeat until correct
  End If
 
  If ant = "n" Then X = -1

    Target.Offset(0, -4) = X * Abs(Val(Target.Offset(0, -4).Value))
    Target.Offset(0, -6) = X * Abs(Val(Target.Offset(0, -6).Value))
    Target.Offset(0, -6).NumberFormat = "0;[Red]0"
   
    Application.EnableEvents = True
    Exit Sub
   
proceed:
    Call ErrorDescription("pfd", "Worksheet_Change") 'calls the function ErrorDescription
    Err.Clear
    Application.EnableEvents = True
End Sub

---