PDA

View Full Version : Worksheet_change event executed on a wrong sheet?



stranno
02-07-2014, 12:34 PM
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
02-07-2014, 01:03 PM
Show us the code, otherwise it is just existential musings.

Zack Barresse
02-07-2014, 01:31 PM
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
02-09-2014, 05:03 AM
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
02-09-2014, 05:49 AM
En nu nog even die code tags Stranno (Na 89 berichten !!!)

p45cal
02-09-2014, 06:17 AM
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
02-09-2014, 10:55 AM
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
02-09-2014, 02:19 PM
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
02-09-2014, 02:50 PM
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
02-09-2014, 02:51 PM
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
02-09-2014, 02:55 PM
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
02-09-2014, 02:57 PM
Zack answered my question above I guess.

stranno
02-09-2014, 03:31 PM
apparently not Snb. What else can cause this problem?

Paul_Hossler
02-09-2014, 04:02 PM
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,


Worksheet_Change(ByVal Target As Range)


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
02-09-2014, 04:04 PM
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...

Sub footest()
Sheet2.Activate
Sheet1.Range("A1").Value = 1
End Sub

And in Sheet1 code module have the following event code...
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

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
02-09-2014, 04:08 PM
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
02-09-2014, 05:06 PM
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..

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

stranno
02-10-2014, 02:00 AM
Other than the fact it doesn't work?

In addition to the other responses, esp. Zacks,


Worksheet_Change(ByVal Target As Range)


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
02-10-2014, 03:36 AM
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
02-10-2014, 03:38 AM
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

snb
02-10-2014, 03:44 AM
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
02-10-2014, 03:45 AM
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
02-10-2014, 03:58 AM
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
02-10-2014, 08:05 PM
@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

Paul_Hossler
02-10-2014, 09:40 PM
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 (http://office.microsoft.com/client/helppreview14.aspx?AssetId=HV080006915&lcid=1033&NS=EXCEL%2EDEV&Version=14&CTT=5&origin=HV080007254) can appear in class modules (http://office.microsoft.com/client/helppreview14.aspx?AssetId=HV080006915&lcid=1033&NS=EXCEL%2EDEV&Version=14&CTT=5&origin=HV080007254). This error has the following causes and solutions:


The Me keyword appeared in a standard module (http://office.microsoft.com/client/helppreview14.aspx?AssetId=HV080006915&lcid=1033&NS=EXCEL%2EDEV&Version=14&CTT=5&origin=HV080007254).
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
02-10-2014, 10:15 PM
@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
02-11-2014, 01:25 AM
Thanks Paul. Very helpful.

snb
02-11-2014, 01:55 AM
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
02-11-2014, 11:49 PM
@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/microsoft-excel-forum/208859-2d-works-but-1d-doe-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. :D

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

Mark

Zack Barresse
02-13-2014, 03:34 PM
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
02-14-2014, 04:36 AM
You can call me whatever you want, I'll answer to both. :)

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

SamT
02-14-2014, 04:15 PM
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