Worksheet_change event executed on a wrong sheet?

[stranno]

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

[Bob Phillips]

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

[Zack Barresse]

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.

[stranno]

Oke, Here is the 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.

[snb]

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

[p45cal]

At first glance, try removing ActiveSheet. from

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

to leave:

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

[Bob Phillips]

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

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

without a sheet qualifier it will refer to the Activesheet.

[p45cal]

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..

[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.

[snb]

I think some UDF might be running.

When the eventcode

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:

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

or

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

or

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

[stranno]

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?

[stranno]

Zack answered my question above I guess.

[stranno]

apparently not Snb. What else can cause this problem?

[Paul_Hossler]

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

[Zack Barresse]

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?

[p45cal]

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:

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:

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?

[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..

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

[stranno]

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

[Bob Phillips]

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?

[GTO]

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