Worksheet Event Deactivate

[Bengt]

I noticed a funny thing about the Deactivate-Event, and I was just curious whether someone else has noted the same, or if I am simply doing anything wrong.

I have this code:

Private Sub Worksheet_Deactivate()
Dim CurrentSheet As String
CurrentSheet = ActiveSheet.Name
MsgBox "You are deactivating worksheet " & CurrentSheet
End Sub

I have attached this code to Sheet1. When I leave Sheet1 to go to Sheet2, the code runs, but the value of CurrentSheet is not Sheet1 but Sheet2. It seems to me then, that this code is executed when Sheet2 is activated, rather than when Sheet1 is deactivated.

Another observation is its cousin, the Activate-Event.

I have this code:

Private Sub Worksheet_Activate()
CurrentSheet=ActiveSheet.Name
MsgBox "Welcome to " & CurrentSheet
End Sub

If Sheet2 is active and I click on Sheet1, The value of CurrentSheet Sheet1. This leads me to believe that the Activate-event and the DeActivate-event is really the same thing: Deactivate is triggered when the next sheet becomes active, as does Activate.

Am I wrong here.

Bengt

Running Excel2003 under W7.

 

[VoG]

Once you've changed sheets the active sheet is the sheet that you switched to. Try

Private Sub Worksheet_Deactivate()
Dim CurrentSheet As String
CurrentSheet = Me.Name
MsgBox "You are deactivating worksheet " & CurrentSheet
End Sub

 

[Bengt]

Thank you. That seems to work as intended. Got a little confused by the names of the events.

Bengt

 

[Bengt]

I have a folllow up to my previous question about deactivating a worksheet:

What I really wanted to do was to initiate an error checking procedure, to start automatically as soon as the user left a particular worksheet. My strategy was to put my error check-routine into the deactivate-event. However, when I put it there, I soon realized that the sheet that I left was no longer the active one. The active sheet was the sheet to which I wanted to go.

In order to be able to check my original worksheet, I had to activate it, and then run the error check-routine, but when that routine had run, and I tried to move to the worksheet to which I wanted to go to in the first place, the error-checked worksheet became deactivated again, and hence the deactivate-event was triggered which caused the error check-routine to start again, and so on, and so on...

Obviously, the attempted strategy doesn't work, and my question then is if there is a better way. Can you disable event handlers through VBA-code?

Bengt

 

[VoG]

Yes you can deactivate event handlers. However, please post your error checking code. It should not be necessary to select the deactivated sheet (unless maybe you need the user to change something on it).

 

[Bengt]

Hello Peter,
Thanks for your input. The error checking routine didn't do to much sophisticated stuff, it was more a question of comparing values in different cells and matching them against a criteria of some kind.

This is basically the code:

Sub Check()
'Dim DestinationSheet As String
'Dim StartSheet As String
Dim Note As String
Dim AtLeastOne As Boolean
Dim x As Integer
AtLeastOne = False
DestinationSheet = ActiveSheet.Name 'The sheet to which you want to go
StartSheet = SheetToCheck 'The sheet that you want to check
x = 5
Dim color As Integer
'Sheets(SheetToCheck).Activate
While Cells(x, 1) <> ""
'MsgBox Cells(x, 2)
Range(Cells(x, 1).Address & ":" & Cells(x, 6).Address).Select
If (Cells(x, 2) <> Cells(x, 6)) Or (Cells(x, 2) = "" And Cells(x, 6) <> "") Then
AtLeastOne = True
color = 45
Note = "Mismatch on line " & Str(x) & ": " & Cells(x, 2) & " --- " & Cells(x, 6)
frmFelkoll.lstShowMisMatch.AddItem Note
Else
'Cells(x, 1).Select
color = xlNone
End If
With Selection.Interior
.ColorIndex = color
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
x = x + 1
Wend
If AtLeastOne Then
frmFelkoll.Show
Else
MsgBox "No errors were found", vbInformation + vbOKOnly, "Felfritt"
End If
Application.CutCopyMode = False
End Sub

Bengt

 

[VoG]

So maybe like this

Private Sub Worksheet_Deactivate()
Dim CurrentSheet As Worksheet
Set CurrentSheet = ActiveSheet
Application.ScreenUpdating = False
Application.EnableEvents = False
Me.Select
Call Check
CurrentSheet.Select
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

 

[Bengt]

But maybe I don't get this properly, but once you trigger the event handler for the deactivate-event, the worksheet that you are deactivating is no longer the current worksheet, or am I wrong?

Bengt

 

[VoG]

Perhaps commented code will explain it

Private Sub Worksheet_Deactivate()
Dim CurrentSheet As Worksheet
Set CurrentSheet = ActiveSheet ' set a reference to the newly selected worksheet
Application.ScreenUpdating = False ' avoid screen flicker
Application.EnableEvents = False ' prevent events from firing
Me.Select ' select Me - the deactivated sheet
Call Check ' do your stuff
CurrentSheet.Select ' go back to the newly selected sheet
Application.ScreenUpdating = True ' refresh the screen
Application.EnableEvents = True ' turn events back on
End Sub

 

[Bengt]

Yes, now I see. I didn't notice the line with

Application.enableevents=False

That ought to do the trick.

Thanks a lot

Bengt