Worksheet_Deactivate Only Firing Every Other Time

[TemiU]

I have a worksheet ("WS1") with a Worksheet_Deactivate procedure.

We have come across the following curious situation: When we navigate from WS1 to certain (but not all) other worksheets within our application, the Worksheet_Deactivate only fires every other time. This means that if we navigate from WS1 to WSA, then back to WS1 and then return to WSA, the Deactivate will only fire the second time we go from WS1 to WSA. Should we do the following, though:

1. WS1
2. WSA ("Bad List")
3. WS1
4. WSB ("Bad List")

Deactivate will not fire. It will only fire if we visit the same "Bad List" worksheet twice or navigate to a "Good List" one.

E.g.:

1. WS1
2. WSA ("Bad List")
3. WS1
4. WSA ("Bad List")

Now it fires

If we navigate to one of the worksheets which are on the "Good List", it will fire immediately, regardless of which worksheet we navigated to WS1 from.

E.G.:

1. WSA ("Bad List")
2. WS1
3. WSThisOneFires ("Good List")

Deactivate will fire.

I tried changing the name of a worksheet to see if that may be the cause, but it does not seem to be related. I can not find any other "common denominator" between the "Good List" worksheets or the "Bad List" ones.

One more point: If I put a message box in the deactivate procedure (e.g. MsgBox "Fired!"), then it fires every time. I do not want a message box there, though !

Any help would be appreciated!

Thanks,
TemiU

 

[Andrew Poulsom]

How do you know it's not firing? What's the VBA code? I don't see how adding a MsgBox would cause it to fire when it doesn't without it.

 

[TemiU]

I set a breakpoint on the Worksheet_Deactivate event to see when it fires. It is also obvious that it's not firing, because it should be changing a value on WS1 (the worksheet it's coming from) and that value is not changing.

I don't know why the MsgBox changes things, either, it is very odd. That's why I posted it here!

The VBA code is as follows:
Private Sub Worksheet_Deactivate()
'-- A bunch of comments explaining modifications


Application.ScreenUpdating = False
With Sheets("WS1")
.Unprotect Password:=PassWrd

.Range("A1").Value = "not current - click refresh to update"
.Protect Password:=PassWrd
End With
'the following is to prevent the values on WS1 showing on other worksheets, which was happening before the code was there
'This may have just been an Excel viewing issue, I'm not sure, since it's before I was working with this application:
With ActiveWorkbook
.Unprotect Password:=PassWrd
.Sheets("WS1").Visible = False
.Sheets("WS1").Visible = True
.Protect Password:=PassWrd
End With

Application.ScreenUpdating = True

End Sub


(In the Worksheet_Activate procedure of this sheet, if the value says "not current [etc.]", the sheet will be updated according to our specifications and Range("A1") will read "UPDATED". This works fine.)

 

[Andrew Poulsom]

Where have you assigned a value to the variable PassWrd? You can use Me in place of Sheets("WS1").

 

[TemiU]

PassWrd is a constant. This has been working consistently in all of our other code, so it should not be an issue.

Using "Me" yielded the same results but did not change anything.

 

[Andrew Poulsom]

I can't reproduce the behaviour you are seeing, sorry. Have you tried putting a breakpoint in your code to double check that it isn't firing?

 

[TemiU]

Yes. Thanks anyway!