Worksheet_change event executed on a wrong sheet?

[snb]

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.

[Bob Phillips]

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.

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.

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.

[Aussiebear]

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.

[Zack Barresse]

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

[Paul_Hossler]

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

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

[Paul_Hossler]

@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

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

[stranno]

Thanks Paul. Very helpful.

[snb]

Or:

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

[GTO]

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

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

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.

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

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

Mark

[Zack Barresse]

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.

[Aussiebear]

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

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

[SamT]

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